[SOLVED] while loop not working .. maybe mySQL

[severndigital]

I'm not sure which category to post this is. but here is the senario.

 

I have a mysql_query running. usually I do a simple while loop and echo some results.

 

this script doesn't seem to be working, but I am getting zero error reports from PHP or MySQL

 

The MySQL query works and I have tested in in other applications. namely Navicat 8 MySQL query builder.

I am connected to the Database.

I have error reporting turned on in PHP

all variables called are being defined correctly.

 

here is the code

$sql = "SELECT
timesheet.userid, timesheet.datein,timesheet.timein,timesheet.timeout,
workers.firstname,workers.lastname
FROM
workers ,timesheet
WHERE
workers.id =  timesheet.userid AND
timesheet.datein BETWEEN '$start' AND '$end'";


$pull = mysql_query($sql)or die(mysql_error());

echo 'before while';
while($r = mysql_fetch_array($pull)){
	$fname = $r['firstname'];
	echo 'Hello';
 	echo '<BR>The first name is: ' . $fname . '<BR>';
}
echo 'after while';

 

generally i put the two echos in there as check points. if the while loop isn't working, I won't see the 'after while' since the script will hang at the loop, but this script is not hanging it reports both echos before and after the loop.

 

However, it does not echo ANY of the information within the while loop. Nor, does it set the $fname variable set within the while loop.

 

It's probably something I'm doing, but if anyone can offer help, i would really be thankful.

 

If it is something I did, please go easy, this if my first crack at more complex mysql data pulls.

 

thanks,

chris

 

 

[darkfreaks]

mysql_fetch_assoc ???

[dbo]

Are you sure you're getting results at all? I typically do it like this:

 

$query = "SELECT id, username, password FROM table"; //or whatever....
$result = mysql_query($query);

$rows = mysql_num_rows($result);
for( $i = 0; $i < $rows; ++$i )
{
   $row = mysql_fetch_assoc($result);
   //do some stuff
}

 

If you do it this way you can troubleshoot a little easier IMO, that and I just like for loops better. But anyways. Echo out the number of rows to make sure its not 0... if it's zero your query is running and not failing, it's just not returning any results.

[kenrbnsn]

If you're debugging this, put in the a debug echo before the "while" loop:

<?php
$sql = "SELECT timesheet.userid, timesheet.datein,timesheet.timein,timesheet.timeout, workers.firstname,workers.lastname
            FROM workers ,timesheet WHERE workers.id =  timesheet.userid AND timesheet.datein BETWEEN '$start' AND '$end'";
$pull = mysql_query($sql)or die(mysql_error());
echo 'before while , number of records selected:' . mysql_num_rows($pull) . '<br>'; // modified this line
while($r = mysql_fetch_array($pull)){
      $fname = $r['firstname'];
      echo 'Hello';
      echo '<BR>The first name is: ' . $fname . '<BR>';
}
echo 'after while';
?>

 

Ken

[Ninjakreborn]

Also test out the query itself (echo the $sql variable) and verify that the sql is being created properly.

[severndigital]

i seemed to get it working. I used most of the debugging suggestions to discover PHP didn't like the way I had formatted the SQL statement.

 

I am building the more complex statements with Navivat 8 and when I do a copy and paste it works. If I mess with the format at all  (i.e. put tabs in or spaces to make it look cleaner) It doesn't want to work right.

 

Thank you.