Simple MySQL query results incorrecy

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Simple MySQL query results incorrecy

Post 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>');                  

							}
?>
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

Post 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.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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'];
User avatar
Maugrim_The_Reaper
DevNet Master
Posts: 2704
Joined: Tue Nov 02, 2004 5:43 am
Location: Ireland

Post 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?
cdickson
Forum Contributor
Posts: 120
Joined: Mon Apr 05, 2004 1:30 pm
Location: Michigan, USA

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