How to return results when some fields are ok to be NULL

Nico

I have this query:

$sql = mysql_query("SELECT * FROM $table WHERE serialnumber= " . $serialnumber . " ORDER BY serialnumber");

now its ok for SOME of the rows to be null, but not all. When asking this query, if a serial number is not in the database it continues with the operation 'cause the query will always return true.

if i use

if(!mysql_num_rows($sql))

then it filters out almost all the rows returned because there is inevitably a couple blank rows in the table which is fine.

I tried playing with the mysql_num_rows but to be honest, I have no clue how to use it correcty other than the previous statement. > and < don't work (at least the way I tried it).

How can I get this to die if the serial number doesn't exist?

edit: additionally I'm outputting everything via

		echo "<p><p><table>";			
		while ($row = mysql_fetch_row($sql)) {(html stuff)}

if that helps in any way

Thanx mucho

Nico

bastien

as the dataset returned should all records where the serialnumber matches. if there is no serial number or it is different then those records should not be part of the dataset...

if you separate out the SQL and echo it back what does it look like?

Nico

no matter if any data is returned from the "echo $row" commands or not, it always echos out "Resource id #2".

A dataset is always returned, whether it has anything in it or not is the problem.

Nico

bastien

Nico

entire code minus the connection info is:

$db = mysql_select_db("dcpserials", $connection)
					or die("Unable to connect to database" . mysql_errno());

$sql = mysql_query("SELECT * FROM $table WHERE serialnumber= " . $serialnumber . " ORDER BY serialnumber");			
	echo "<p><p><table>";			
	while ($row = mysql_fetch_row($sql)) {

?>
</div>
<tr bgcolor="#666699"> 
  <td><div align="center">&nbsp;<b>Serial Number:</b></div></td>
  <td><div align="center">&nbsp; <b><? printf("%s", $row[0]); ?> </b></div></td>
</tr>
<tr> 
  <td><div align="center">&nbsp;<b>Unit ID:</b></div></td>
  <td><div align="center">&nbsp;<b><? echo $row[1];?></b></div></td>
</tr>
<tr bgcolor="#666699"> 
  <td><div align="center">&nbsp; <b>Owner's Name:</b></div></td>
  <td><div align="center">&nbsp;<b><? echo $row[2]," ",$row[3];?></b></div></td>
</tr>
<tr> 
  <td><div align="center">&nbsp; <b>Address:</b></div></td>
  <td><div align="center"><div align="left">&nbsp; <b><? echo $row[4];?></b></div></td>
</tr>
<tr bgcolor="#666699"> 
  <td>&nbsp;</td>
  <td><div align="center">&nbsp; <b><? echo $row[5];?></b></div></td>
</tr>
<tr> 
  <td>&nbsp;</td>
  <td><div align="center">&nbsp; <b><? echo $row[6],", ",$row[7]," ",$row[8];?></b></div></td>
</tr>
<tr bgcolor="#666699"> 
  <td><div align="center">&nbsp; <b>Phone Number:</b></div></td>
  <td><div align="center">&nbsp; <b><? echo $row[9];?></b></div></td>
</tr>
<tr> 
  <td><div align="center">&nbsp;<b>Alternate Phone Number:</b></div></td>
  <td><div align="center">&nbsp; <b><? echo $row[10];?></b></div></td>
</tr>
<tr bgcolor="#666699"> 
  <td><div align="center">&nbsp;<b>Purchase Date:</b></div></td>
  <td><div align="center">&nbsp; <b><? echo $row[11];?></b></div></td>
</tr>
<tr> 
  <td><div align="center">&nbsp;<b>Warranty End Date:</b></div></td>
  <td><div align="center"><div align="center">&nbsp;<b><? echo $row[12];}?></b></div></td>
</tr>
<tr> 
  <td>&nbsp;</td>
  <td>&nbsp;</td>
</tr>
<tr> 
  <td>&nbsp;</td>
  <td>&nbsp;</td>
</tr>
<div align="center"></table> 
  <?
      mysql_close($connection) or die("CANNOT CLOSE THE DATABASE CONNECTION!  CRAP!");
    } else {;}
  ?>

If you know of a better way to write this to get the same results please let me know-- i'm ALWAYS open for ideas on making stuff faster, more error correction, etc etc. I'm fairly new to this so any help is greatly appreciated. Thanks for your time in reviewing all this!

Nico

bastien

so its gotta be the sql

try

$sql= ("SELECT * FROM $table WHERE serialnumber= $serialnumber ORDER BY serialnumber");

echo $sql; <===what does this say?

$result= mysql_query($sql);

echo "<p><p><table>";

while ($row = mysql_fetch_array($result)) {
        $sn=$row["serialnumber"];
        $unitID = $row["unitID"];
        $OnwerName=$row["OwnerName"];
        //etc...
        //assigning the results to readable variables makes it 
       //easier to see what you are doing

//rest of code
?>

<tr bgcolor="#666699">
<td><div align="center"> <b>Serial Number:</b></div></td>
<td><div align="center">  <b><?=$sn ?> </b></div></td>
</tr>

Nico

I use fetch_row for speed. This table has the potential to get really large and a fetch array for that many rows has the potential to be slow. I will however assign variables to the rows-- good idea.

echo $sql = "Resource id #2"

Nico

bastien

should say something like

select * from table where serialnumber = 123456789

(or what ever number is input)

killerbishop

You could could just do a check like:

$result = mysql_query($sql);
if (is_resource($result)) {

   while($product = mysql_fetch_assoc($result)) {

    echo $product['serialnumber'];

   }

}

exit;

By the way, mysql_fetch_assoc() is not that much slower than mysql_fetch_row(). The potential bottleneck here is in the size of data set, not the PHP code. Of course, if you are worried about that, you could use mysql_unbuffered_query(), which doesn't wait for the entire data set to be returned, but then mysql_num_rows() won't work. Also, consider doing "paged-listings" using a LIMIT clause - show like 100 entries per page at most.