One of those days, strange MySQL behavior on mysql_fetch_array

[falcon1620]

Ok so I have a simple database table set up, and I am trying to get the results into an HTML table, however I have a question. One loop I write in a for loop will dump the data I want to the table, the other returns no results. For example I have the code...

 
       
//SQL Lookup
//SQL Lookup
$sqlAppt = mysql_query("SELECT * FROM Appointment WHERE TTUNumber = \"$TTUNumber\" && ShippingNumber = \"$ShippingNumber\" LIMIT 1") or die('Could not execute SQL statement ' . mysql_error());


$numbRows = mysql_num_rows($sqlAppt);
echo "<p>Number of rows found: $numbRows </p>\n";

 

So When I create the first whlile loop, i do not get any results from my database.

 

while($r=mysql_fetch_array($sqlAppt)){	
            
   $ApptDate=$r['Date'];
   $ApptTime=$r['Time'];
   $ApptNumber=$r['ApptNumber'];
   $ApptDock=$r['Dock'];
                
   echo '<h1>I got here</h1>'."\n";
   echo "<table>\n";
   echo "<tr><td>Date</td><td>$ApptDate</td></tr>\n";
   echo "<tr><td>Time</td><td>$ApptTime</td></tr>\n";
   echo "<tr><td>Appt Number</td><td>$ApptNumber</td></tr>\n";
   echo "<tr><td>Dock</td><td>$ApptDock</td></tr>\n";
   echo "</table>\n";
                
} 

 

The Results is this:

Number of rows found: 1

I got here

Date

Time

Appt Number

Dock

 

However, when I create a different loop structure...

for($i = 0; $i < $numbRows; $i++) {
                
   $r=mysql_fetch_array($sqlAppt);

   $ApptDate=$r['Date'];
   $ApptTime=$r['Time'];
   $ApptNumber=$r['ApptNumber'];
   $ApptDock=$r['Dock'];
                
   echo '<h1>I got here</h1>'."\n";
   echo "<table>\n";
   echo "<tr><td>i:</td><td>$i</td></tr>\n";
   echo "<tr><td>Date</td><td>$ApptDate</td></tr>\n";
   echo "<tr><td>Time</td><td>$ApptTime</td></tr>\n";
   echo "<tr><td>Appt Number</td><td>$ApptNumber</td></tr>\n";
   echo "<tr><td>Dock</td><td>$ApptDock</td></tr>\n";
   echo "</table>\n";
                
}

 

Naturally I get a different result! 

 

Number of rows found: 1

I got here

i: 0

Date 1982-12-26

Time 08:00:00

Appt Number 123

Dock 34

 

I can't figure out why for the life of me that my while loop would not return anything on here>?  Does anyone have any idea's?

 

Thanks

[Psycho]

I don't see any problems. The fact that the loop is runinng (i.e. the labes are displaying) you know that the loop is running that one time. Try doing a print_r() onthe $r variable.

 

Also, to help yourself out - don't construct the query inside the mysql_query() function. It makes it much harder to debug mysql problems. Create the query as a string variable. Then if there are unexpected results you can echo the query to the page.

$query = "SELECT *
          FROM Appointment
          WHERE TTUNumber = '$TTUNumber'
            AND ShippingNumber = '$ShippingNumber'
          LIMIT 1";
$sqlAppt = mysql_query($query) or die('Query: $query<br />Error: ' . mysql_error());

[falcon1620]

Thanks for the tip.  Yea I actually that is very helpful separating the Query from the MySQL function just because I could get the output of the SQL query and run it against my SQL server to debug it. I don't know why I like to squeeze those together.  So I went ahead and added a print_r ($r) statement, and the same deal, during executing of the for loop, the $r returns the array with information in the array, and the while loop returns nothing. So I went ahead and re-tried the function and found a stray ; ending my while loop prematurely that I think came about while I was forwarding my Eclipse IDE over SSH during a "moment", so the line looked something like this

 

 

while($r=mysql_fetch_array($sqlAppt))                                                    (off the screen)                                                                          ;

 

 

HILARIOUS!  2 days later....

 

Coding on a 15" dell LCD... LOL Some one must have been messing with me on that one.