Page 1 of 1

Simple MySQL query results incorrecy

Posted: Mon Nov 29, 2004 10:26 am
by cdickson
I'm running a simple query that I've done a million times, but the results are wrong and I can't figure out why.

For some reason, even though cat1 or cat2 = $searchcat, a few of the records won't show up. It seems random, but there may be something wrong in the code following the query that puts the records into columns.

Any help will be greatly appreciated! :D

Code: Select all

<?php
query = "SELECT * FROM portfolio WHERE cat1='$searchcat' OR cat2='$searchcat' ORDER BY name";
$r = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($r);
			
echo "<p><a href="portfolio.php">Search More Categories</a></p>";
					  
	$tmp_cnt = 0;   // temporary variable
	$per_row = 2;
					  
	for ($j = 0; $j < mysql_num_rows($r); $j++) {
						
	if ($tmp_cnt % $per_row == 0)
	echo ('<tr>');
							$rowarray = mysql_fetch_array($r);
	$field_num = mysql_num_fields($r);
							echo ("<td>");
							echo ("<table valign="top" align="center" border="0" align="center" cellpadding="1" cellspacing="2">");
								if (!empty($rowarray[0])) {
								echo("<tr><td valign="top" align="center" width="170"><a href="http://{$rowarray[3]}" target="_blank"><img src="images/{$rowarray[5]}" border="0"></a></td></tr>");
								echo ("<tr><td valign="top" align="center"class="captiontext">") . $rowarray[0] . ("</td></tr>");
								}
								else {
								echo("<tr> <td> </td></tr>");
								}
							echo ("</table>");
							echo ("</td>");        

	$tmp_cnt = $tmp_cnt + 1;     
	if ($tmp_cnt % $per_row == 0)    
	echo ('</tr>');                  

							}
?>

Posted: Mon Nov 29, 2004 10:32 am
by kettle_drum
Where is the variable $searchcat coming from? Are the results in the same case? is there white_space in the results in the database so it doesnt actully fully equal the value?

Posted: Mon Nov 29, 2004 10:39 am
by cdickson
Thanks for trying to help, kettle.

The var $searchcat comes from a dropdown field within a field:

Code: Select all

<form name="searchcategory" method="post" action="results.php">
<select name="searchcat" class="sidebartext">
When results.php comes up, that is when I find some of the records are missing.

Yes, I made sure there was no white space around the text that is within the cat1 or cat2 fields, going so far as to 1) retype and 2) copy and paste the info from a record that does appear correctly, with no success.

Posted: Mon Nov 29, 2004 11:04 am
by kettle_drum
Have you recently upgraded your version of PHP from one with global variables turned on? I.e. what does the following show:

Code: Select all

echo $searchcat." - ".$_POST['searchcat'];

Posted: Mon Nov 29, 2004 11:06 am
by Maugrim_The_Reaper
Is register_globals ON? If it's off, you should be using $_POST[searchcat].

Any reason why you seem to have two mysql_fetch's? You seem to call it once at the beginning with fetch_assoc but then do nothing with it. Are you therefore skipping at least one row each time?

Posted: Mon Nov 29, 2004 11:24 am
by cdickson
kettle & Maugrim -

On our server, globals are always on.

BUT... as soon as I commented out the line

Code: Select all

$row = mysql_fetch_assoc($r);
,
everything seems to be working fine!

Thanks so much, both of you! :D