Re: [Phplib-users] HELP! php-mysql weirdness

[Stephen W. <wo...@me...>]

Bizzare, but true! 

Here is what I think the problem is. 

The column BIRT_PLAC may be NULL so the result set has a row with the
value of NULL unless you use the WHERE clause. The problem is the that
the 

while($row = mysql_fetch_array($result))

terminates the the while when it returns the NULL row result and the
ORDER BY always place the NULL row result as the first row in the result
set. Even more insidious would be an unsorted result that returns some
row of data, then a NULL, then more rows of data. So you would get some
rows, thnk things are great and not realize that you never processed the
rest of the rows. Which is exactly what happens in my testing of this
problem.

This is VERY BAD! I use this type of construct ALL through my code.

Any thoughts or comments?

  -Stephen Woodbridge

Stephen Woodbridge wrote:
> 
> Hi all,
> 
> Sorry this is a little off topic, but I'm can't figure this one out and
> someone here might have run into this problem.
> 
> I have a SQL query to mysql from php3, the query has an optional WHERE
> clause. I get results back if the where clause is included but none if
> it is left out.
> 
> select distinct BIRT_PLAC as PLACE from INDI where BIRT_PLAC like "%%"
> order by PLACE
> num=719
> 
> select distinct BIRT_PLAC as PLACE from INDI order by PLACE
> num=0
> 
> BOTH queries work FINE if I cut and paste them into mysql directly.
> Anyone have any ideas?
>   -Steve
> 
> function Places($str) {
>   global $conn;
> 
>   $aPlaces = array();
> 
>   $sql = "select distinct BIRT_PLAC as PLACE from INDI ";
>   if ($str != "")
>     $sql .= "where BIRT_PLAC like \"%$str%\" ";
>   $sql .= "order by PLACE ";
> 
>   print "<br>$sql<br>\n";
>   $result = @mysql_query($sql, $conn)
>     or die("Can't execute query: $sql ! $php_errormsg");
> 
>   $num = 0;
>   while ($row = mysql_fetch_array($result)) {
>     $aPlaces[] = $row["PLACE"];
>     $num++;
>   }
>   print "num=$num<br>\n";
> }
>