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 ?
Need help with outer join command
Collapse
X
-
Hi. Welcome to TSDN!
Consider this example...
Say we have a database for a simple blog system:
[code=mysql]
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)
);
[/code]
Now if I wanted to find all Blogs written by a single user, along with the user info, I could do this:
[code=mysql]
SELECT
Blog.Title, Blog.Body,
User.UserID, User.Name
FROM User
INNER JOIN Blog
ON User.UserID = Blog.Owner
[/code] -
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.netComment
-
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.Originally posted by AtliThen 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
[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.hostnam e, server.domain, server.manufact urer, server.model, server.chassis, server.slot, server.os, server.osver, server.notes
FROM network
LEFT OUTER JOIN server
ON network.serveri d = server.serverid
WHERE ipaddr = '.mysql_escape_ string($_GET["ipadd"]);
$result = mysql_query($qu ery) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table>\n" ;
while ($line = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_v alue</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_resu lt($result);
// Closing connection
mysql_close($li nk);
else:
?>
<!-- comments -->
<form method="post" action="test.ph p">
<input type ="text" name ="ipadd"/>
<input name="button" value="Search" type="SUBMIT"/>
</form>
<?php endif;?>[/PHP]Comment
-
PHP does not use : like python and other such languages do...
Your if statements should be structured like this:
[code=php]
if($something > 1) {
echo "It's bigger than 1";
}
else if($something == 1) {
echo "It is 1";
}
else {
echo "It's less than 1";
}
[/code]
PHP shows error messages when you make syntax errors like these. You just need to turn them on.Comment
-
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_dropdow n"];
echo mysql_escape_st ring($_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.hostnam e, server.domain, server.manufact urer, server.model, server.chassis, server.slot, server.os, server.osver, server.notes
FROM network
LEFT OUTER JOIN server
ON network.serveri d = server.serverid
WHERE ipaddr = '" . mysql_escape_st ring($_POST["ipadd"]) . "'";
print "<p>$query<p>\n ";
$result = mysql_query($qu ery) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table>\n" ;
while ($line = mysql_fetch_arr ay($result, MYSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_v alue</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Free resultset
mysql_free_resu lt($result);
// Closing connection
mysql_close($li nk);
else:
?>
<!-- comments -->
<form method="post" action="test.ph p">
<input type ="text" name ="ipadd"/>
<p><select size="1" name="my_dropdo wn">
<option value="one">cpu </option>
<option value="two">dis k</option>
<option value="three">e nvironment</option>
<option value="four">me mory</option>
<option value="five">ne twork</option>
<option value="six">pro duct</option>
<option value="seven">s erver</option>
</select></p>
<input name="button" value="Search" type="SUBMIT" name="btm_submi t" onclick="valida te()"/>
</form>
<?php endif;?>Comment
Comment