Need help with a MYSQL row count in php

[tekhead2004]

Hey everybody.  Tryint to figure out why my row count only returns 1 instead of the actual value that it is supposed to be.  Please help

 

$mySQL = "SELECT Pallet, puwo_number as 'Pickup Workorder', t_tag as 'T-Tag', Make, Model, serial_number as 'Serial Number' , comments as 'Comments', (Select description from dropdowns where tablename = 'pickups' and columnname = 'type' and value =pickups.type) as Type , Location, shipdate as 'Ship Date', CONCAT('<a href=http://www.fedex.com/Tracking?&cntry_code=us&clienttype=ivother&tracknumbers=' , Tracking_number,' target=new>',Tracking_number,'</a>') as 'Tracking Number', logged_by as 'Logged By', Edited_by as 'Edited By' from pickups, locations where pickups.locationid = locations.locationid";
        if (trim($_POST["criteria"]) != ""  && trim($_POST["choice"]) != "") {
            $mySQL .= " and (" . $_POST['choice'] ." = ";
            $mySQL .= " '".str_replace("\n" ,"' or ". $_POST['choice'] ."= '", trim(str_replace(", ","' or ".$_POST['choice'] ."= '", trim($_POST["criteria"]))))."')";
        }
	echo "MYSQL query made by variables: $mySQL <br><br>";
        $result = mysql_query ($mySQL) ;
        if (!$result)
        {
        	  die('Error: ' . mysql_error());
	}
	$count =  mysql_num_rows($result);

        $Output .= "Rows Returned (this using the dynamic:".$count."<br>";
[code]

[allworknoplay]

Well it's really difficult to read your query because it contains a subquery, maybe a better SQL guru can explain why, but since I don't know the structure of your DB at all, it makes it very difficult to know why it only returns 1 row.

[Maq]

Your code is hard to follow, due to the poor formatting.

 

Are you sure there should be more than 1 row returning?  Have you tried running it in something like PHPMyAdmin to ensure there is more than 1 row?  Or put it in a while loop and display one of the rows to see how many there are?  mysql_num_rows doesn't lie...

 

There is no way we can really tell why or how many rows it should be returning.  Maybe someone else has a better idea.

[tekhead2004]

If this helps at all...I echoed the subquery once it was all put together...copied that query right into my sql database and it ran just fine.  returned the exact results that I was looking for. 

 

the just is.. that the query runs in php, and just returns data concerning the last variable added in the criteria... but if you copy that query and run it in mysql, it returns all results.