Page 1 of 1
Help with this query
Posted: Wed Sep 28, 2005 11:33 am
by mhouldridge
Hi,
I have the following code which outputs results from my database into alternate colours;
Code: Select all
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}
// Define the number of results per page
$max_results = 25;
// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
// Perform MySQL query on only the current page number's results
$sql = mysql_query("SELECT * FROM dedicated order by asset LIMIT $from, $max_results");
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM dedicated"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
$numofrows = $total_results;
echo "<TABLE BORDER=\"0\" table width=\"100%\" cellspacing=\"1\" cellpadding=\"5\" bgcolor=\"#cccccc\" class=\"standard\">\n";
echo "<TR bgcolor=\"#ffffff\"><TD font colour=\"#999999\" width=\"15%\"><b>Asset</b></TD><TD width=\"30%\"><b>Title</b></TD><TD width=\"25%\"><b>IP address</b></TD><TD width=\"15%\"><b>Reconciled</b></TD><TD width=\"5%\"></TD><TD width=\"5%\"></TD></TR>\n";
for($i = 0; $i < $max_results; $i++) {
$row = mysql_fetch_array($sql); //get a row from our result set
if($i % 2) {
echo "<TR bgcolor=\"ffffff\">\n";
}
else {
echo "<TR bgcolor=\"ffffff\">\n";
}
echo "<TD><a href='viewasset.php?varl=".$row['asset']."' class=\"blue3\">".$row['asset']."</a></font></TD><TD>".$row['title']."</TD><TD>".$row['IP']."</TD><TD>".$row['recon']."</TD><TD><a href='sysdocupdate3.php?varl=".$row['asset']."'><img src=\"edit.gif\" alt=\"Delete\" border=\"0\" /></a></TD><TD><a href='deleteget.php?varl=".$row['asset']."'><img src=\"b_drop.gif\" alt=\"Delete\" border=\"0\" /></a></TD>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
// Build Page Number Hyperlinks
echo "<center>Select a Page<br />"
Ok - In the above code you can see that I pull in a value for IP as, "$row['ip']." - I have now altered my database so that I have a table called ip which can stored multiple ip addresses for each asset.
I need to output the ip addresses in this part, therefore I need to query the other table using the asset number from the $row result. My fields within the ip table are;
asset // the relating asset number
id // the unique key for each ip
ip // the ip address - this needs outputting to the above.
Please help. I have tried to implement a query, but cannot get it to work.
Posted: Wed Sep 28, 2005 11:47 am
by Skara
Your code looks horrible, no offense. Your braces are in weird places and the quotes are odd, mainly. Instead of doing "the \"horse\" ran," use 'the "horse" ran' or "the 'horse' ran."
Now then, if you want to do it in one query...
Code: Select all
SELECT * FROM dedicated LEFT JOIN ip ON ip.asset=dedicated.asset ORDER BY dedicated.asset LIMIT $from, $max_results
I think that'll work.
Posted: Thu Sep 29, 2005 4:13 am
by mhouldridge
Hi,
Not sure if that is what I am looking for, but I will give it a try anyway....
Here is what I have now, maybe this will look clearer;
Code: Select all
// QUERY ONE
$max_results = 25; // Define the number of results per page
$from = (($page * $max_results) - $max_results); // Figure out the limit for the query based on the current page number
$sql = mysql_query("SELECT * FROM dedicated order by asset LIMIT $from, $max_results"); // Perform MySQL query on only the current page number's results
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM dedicated"),0); // Figure out the total number of results in DB:
$total_pages = ceil($total_results / $max_results); // Figure out the total number of pages. Always round up using ceil()
$numofrows = $total_results;
echo "<TABLE BORDER=\"0\" table width=\"100%\" cellspacing=\"1\" cellpadding=\"5\" bgcolor=\"#cccccc\" class=\"standard\">\n";
echo "<TR bgcolor=\"#ffffff\"><TD font colour=\"#999999\" width=\"15%\"><b>Asset</b></TD><TD width=\"30%\"><b>Title</b></TD><TD width=\"25%\"><b>IP address</b></TD><TD width=\"15%\"><b>Reconciled</b></TD><TD width=\"5%\"></TD><TD width=\"5%\"></TD></TR>\n";
for($i = 0; $i < $max_results; $i++) {
$row = mysql_fetch_array($sql); //get a row from our result set
// QUERY TWO
$querytwo = mysql_query("SELECT * FROM ip where asset =".$row['asset']."");
$ip = echo $querytwo;
echo "<TR bgcolor=\"ffffff\">\n";
echo "<TD><a href='viewasset.php?varl=".$row['asset']."' class=\"blue3\">".$row['asset']."</a></font></TD><TD>".$row['title']."</TD><TD>".$ip."</TD><TD>".$row['recon']."</TD><TD><a href='sysdocupdate3.php?varl=".$row['asset']."'><img src=\"edit.gif\" alt=\"Delete\" border=\"0\" /></a></TD><TD><a href='deleteget.php?varl=".$row['asset']."'><img src=\"b_drop.gif\" alt=\"Delete\" border=\"0\" /></a></TD>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
The above gives me a parse error, which I knew would happen. I need to use the specifc row asset number in my second query. As you can see within the table row I have used $ip to jump to QUERY TWO.
Please help, I am a newb to php.
Posted: Thu Sep 29, 2005 4:20 am
by mickd
this isnt related to your question, but i thought ill just point this out...
http://www.w3schools.com/xhtml/default.asp
tags should be written in lower case, not a must but when xhtml takes over html itll save you time from converting later. also a good habbit to get into writing xhtml.
what was the parse error that came out, what line was it on?
Posted: Thu Sep 29, 2005 4:51 am
by mhouldridge
Here is what I have now;
Code: Select all
$max_results = 25; // Define the number of results per page
$from = (($page * $max_results) - $max_results); // Figure out the limit for the query based on the current page number
$sql = mysql_query("SELECT * FROM dedicated order by asset LIMIT $from, $max_results"); // Perform MySQL query on only the current page number's results
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM dedicated"),0); // Figure out the total number of results in DB:
$total_pages = ceil($total_results / $max_results); // Figure out the total number of pages. Always round up using ceil()
$numofrows = $total_results;
echo "<TABLE BORDER=\"0\" table width=\"100%\" cellspacing=\"1\" cellpadding=\"5\" bgcolor=\"#cccccc\" class=\"standard\">\n";
echo "<TR bgcolor=\"#ffffff\"><TD font colour=\"#999999\" width=\"15%\"><b>Asset</b></TD><TD width=\"30%\"><b>Title</b></TD><TD width=\"25%\"><b>IP address</b></TD><TD width=\"15%\"><b>Reconciled</b></TD><TD width=\"5%\"></TD><TD width=\"5%\"></TD></TR>\n";
for($i = 0; $i < $max_results; $i++) {
$row = mysql_fetch_array($sql); //get a row from our result set
// QUERY TWO
$row2 = $row['asset'];
$querytwo = mysql_query("SELECT * FROM ip where asset $row2");
$ip = mysql_fetch_array($querytwo);
echo "<TR bgcolor=\"ffffff\">\n";
echo "<TD><a href='viewasset.php?varl=".$row['asset']."' class=\"blue3\">".$row['asset']."</a></font></TD><TD>".$row['title']."</TD><TD>".$ip['asset']."</TD><TD>".$row['recon']."</TD><TD><a href='sysdocupdate3.php?varl=".$row['asset']."'><img src=\"edit.gif\" alt=\"Delete\" border=\"0\" /></a></TD><TD><a href='deleteget.php?varl=".$row['asset']."'><img src=\"b_drop.gif\" alt=\"Delete\" border=\"0\" /></a></TD>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
Above the output table I get a list of,
"Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in F:\Auditwebsite\indexasset3.php on line 358" - which is the following
Code: Select all
$ip = mysql_fetch_array($querytwo);
Posted: Thu Sep 29, 2005 5:05 am
by Jenk
because that is an invalid SQL statement.
mysql_query() returns FALSE upon error. echo mysql_error() to see exact error message.
Posted: Thu Sep 29, 2005 5:08 am
by mickd
Code: Select all
$querytwo = mysql_query("SELECT * FROM ip where asset $row2");
what does WHERE asset $row2 do in that query? never seen it before.
isnt the format for WHERE normally something like WHERE fieldname='$fieldvalue'? unless asset is an actual command.
Posted: Thu Sep 29, 2005 5:24 am
by mhouldridge
Here is the new code, giving me Array for the only field that has a value;
Code: Select all
// QUERY ONE
$max_results = 25; // Define the number of results per page
$from = (($page * $max_results) - $max_results); // Figure out the limit for the query based on the current page number
$sql = mysql_query("SELECT * FROM dedicated order by asset LIMIT $from, $max_results"); // Perform MySQL query on only the current page number's results
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM dedicated"),0); // Figure out the total number of results in DB:
$total_pages = ceil($total_results / $max_results); // Figure out the total number of pages. Always round up using ceil()
$numofrows = $total_results;
echo "<TABLE BORDER=\"0\" table width=\"100%\" cellspacing=\"1\" cellpadding=\"5\" bgcolor=\"#cccccc\" class=\"standard\">\n";
echo "<TR bgcolor=\"#ffffff\"><TD font colour=\"#999999\" width=\"15%\"><b>Asset</b></TD><TD width=\"30%\"><b>Title</b></TD><TD width=\"25%\"><b>IP address</b></TD><TD width=\"15%\"><b>Reconciled</b></TD><TD width=\"5%\"></TD><TD width=\"5%\"></TD></TR>\n";
for($i = 0; $i < $max_results; $i++) {
$row = mysql_fetch_array($sql); //get a row from our result set
// QUERY TWO
$row2 = $row['asset']; //set $row2 value
$querytwo = mysql_query("SELECT * FROM ip where asset = $row2"); // Query database with $row2 value
$ip = mysql_fetch_array($querytwo); // get result from database and assign as $ip
$showip = $ip;
echo "<TR bgcolor=\"ffffff\">\n";
echo "<TD><a href='viewasset.php?varl=".$row['asset']."' class=\"blue3\">".$row['asset']."</a></font></TD><TD>".$row['title']."</TD><TD>".$showip."</TD><TD>".$row['recon']."</TD><TD><a href='sysdocupdate3.php?varl=".$row['asset']."'><img src=\"edit.gif\" alt=\"Delete\" border=\"0\" /></a></TD><TD><a href='deleteget.php?varl=".$row['asset']."'><img src=\"b_drop.gif\" alt=\"Delete\" border=\"0\" /></a></TD>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
Any ideas?
Posted: Thu Sep 29, 2005 5:32 am
by Jenk
you are trying to echo an array, thus you will only see "Array"
You need to specify which indice of the array you wish to see, if it is all of them, you'll need to loop through it with a foreach loop, or manually echo every indice
Also, I'd consider cutting down on the number of variables you use, such as your reassignment of $showip = $ip is unecessary when you could just use $ip.
