Help with this query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Help with this query

Post 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.
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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.
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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.
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post 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?
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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);
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

because that is an invalid SQL statement.

mysql_query() returns FALSE upon error. echo mysql_error() to see exact error message.
mickd
Forum Contributor
Posts: 397
Joined: Tue Jun 21, 2005 9:05 am
Location: Australia

Post 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.
User avatar
mhouldridge
Forum Contributor
Posts: 267
Joined: Wed Jan 26, 2005 5:13 am

Post 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?
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post 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.

:)
Post Reply