Need help with outer join command

[edefikh]

I have a database called inventory, In it I have tables called network and environment. In the network table there is a row called ipaddr. I would like to be able to write a query that can find ip address, lets say 10.0.0.1 in the table called network, row called ipaddr, join the information with environment table and display the search result. I want to be able to search the inventory database by ip address and be able to see the complete result, based on data in both tables. What would be the statement ?

[Atli]

Hi. Welcome to TSDN!

Consider this example...

Say we have a database for a simple blog system:

CREATE TABLE User (
  UserID Int Unsigned Auto_Increment Primary Key,
  Name VarChar(150) Not Null
);
 
CREATE TABLE Blogg (
  BloggID Int Unsigned Auto_Increment Primary Key,
  Title VarChar(255),
  Body Text,
  Owner BigInt References User(UserID)
);
 

Now if I wanted to find all Blogs written by a single user, along with the user info, I could do this:

SELECT 
  Blog.Title, Blog.Body,
  User.UserID, User.Name
FROM User
INNER JOIN Blog
  ON User.UserID = Blog.Owner
 

[edefikh]

Thank you for that reply. Now I would like to add a search box to my wiki page and have users query the data by ipaddr string. Somehow I need to pass the string from the webpage to mysql and display the results back. Any ideas

[Atli]

What kind of API are you using on your page?
PHP?... ASP?... Something else?

[edefikh]

What kind of API are you using on your page?
PHP?... ASP?... Something else?

I am using PHP
999999999999999999999999999999999999999999999

[Atli]

Then you simply need to create a HTML form which sends the data to you PHP script, have your PHP script execute a query on your database and display the results.

If you are having difficulties passing the data from the form, check out this article I wrote on the subject.

If you need to know how to query the database, check out the MySQL Functions, or better yet, the Improved MySQL Extension at php.net

[edefikh]

Then you simply need to create a HTML form which sends the data to you PHP script, have your PHP script execute a query on your database and display the results.

If you are having difficulties passing the data from the form, check out this article I wrote on the subject.

If you need to know how to query the database, check out the MySQL Functions, or better yet, the Improved MySQL Extension at php.net

here is the script I created, but when I execute it, nothing comes back in a webpage, it still displays the search button, I am thinking something is not correct in the way I try to display results.
[PHP]<?php

if($_GET["field1"]):

// Connecting, selecting database
$link = mysql_connect('10.10.17.247', 'edefikh', 'changeme')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('InventoryDev') or die('Could not select database');

// Performing SQL query
$query = 'SELECT network.hostname, server.domain, server.manufacturer, server.model, server.chassis, server.slot, server.os, server.osver, server.notes
FROM network
LEFT OUTER JOIN server
ON network.serverid = server.serverid
WHERE ipaddr = '.mysql_escape_string($_GET["ipadd"]);

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);

else:
?>

<!-- comments -->
<form method="post" action="test.php">
<input type ="text" name ="ipadd"/>
<input name="button" value="Search" type="SUBMIT"/>
</form>

<?php endif;?>[/PHP]

[mwasif]

Use CODE tags when posting souce code.

[Atli]

PHP does not use : like python and other such languages do...

Your if statements should be structured like this:

if($something > 1) {
  echo "It's bigger than 1";
}
else if($something == 1) {
  echo "It is 1";
}
else {
  echo "It's less than 1";
}
 

PHP shows error messages when you make syntax errors like these. You just need to turn them on.

[edefikh]

I need to be able to query a set of 7 tables, pull the right data from it and then display the result in a table. Here is what I have so far, I am not sure how to display the results nicely:


<?php
if($_POST["ipadd"]):
$_POST["my_dropdown"];
echo mysql_escape_string($_POST['text']);
echo $_POST['my_dropdown'];

// Connecting, selecting database
$link = mysql_connect('10.10.17.247', 'inventory', 'inventory')
or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('InventoryDev') or die('Could not select database');


// Performing SQL query
$query = "SELECT network.hostname, server.domain, server.manufacturer, server.model, server.chassis, server.slot, server.os, server.osver, server.notes
FROM network
LEFT OUTER JOIN server
ON network.serverid = server.serverid
WHERE ipaddr = '" . mysql_escape_string($_POST["ipadd"]) . "'";

print "<p>$query<p>\n";

$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);

else:
?>

<!-- comments -->
<form method="post" action="test.php">
<input type ="text" name ="ipadd"/>
<p><select size="1" name="my_dropdown">
<option value="one">cpu</option>
<option value="two">disk</option>
<option value="three">environment</option>
<option value="four">memory</option>
<option value="five">network</option>
<option value="six">product</option>
<option value="seven">server</option>
</select></p>
<input name="button" value="Search" type="SUBMIT" name="btm_submit" onclick="validate()"/>
</form>



<?php endif;?>