[Resolved] code breaking while statement

[chriswheat]

Hello,

I have the following code:

include ("../../phpcode/db_connection.php");
$timestamp = strtotime("+0 hours 0 minutes 30 days");

$rundate = strftime( "%m%d",$timestamp);
$timestamp2 = strtotime("+0 hours 0 minutes 0 days");

$curdate = strftime( "%m%d",$timestamp2);
$grandtotal = "0";
$sql = mysql_query("SELECT * FROM _gamedates");
if(mysql_num_rows($sql) > 0) { //sart if sql
while($row=mysql_fetch_array($sql)) { //start whilestatement
$month = $row['month'];
$day = $row['day'];
$combined = "$month$day";
if($combined < $rundate & $combined > $curdate){

print "'.$row['']."<br><br>";

} //end while dates
} //end if dates
} // end if sql

the above returns a total of 10 results

yet if I put the follwing code inplace of the
print "".$row['']."<br><br";

$sql = mysql_query("SELECT * FROM _tkts WHERE game_id='".$row['game_id']."' and status='open'");
if(mysql_num_rows($sql) > 0) { //sart if open
print "<table width=\"100%\" border=\"0\" cellpadding=\"5\" cellspacing=\"0\"><tr bgcolor=\"#CCCCCC\"><td>vs. ".$row['agianst']." -- ".$row['month']."/".$row['day']."/".$row['year']."</td></tr>";
$opentkts = "0";
while($row=mysql_fetch_array($sql)) { //start while open
$opentkts = $opentkts + 1;
$grandtotal = $grandtotal +1;
} //end while open
print "<tr><td>TOTAL OPEN TKTS: $opentkts</td></tr>";
print "<tr bgcolor=\"#CCCCCC\"><td>GRAND TOTAL: $grandtotal</td></tr></table>";
} //end if open

It only returns one result

Why is the above breaking the initial while statement?

Many Thanks
Chris

[laserlight]

Examine your code line by line.

It is obvious that the line where you make use of $row comes before you set the value of $row using mysql_fetch_array().
So then, you are simply looping through the result set, creating an array each time, then doing nothing with it.

[chriswheat]

It was my understanding that you don't use mysql_fetch_array()
when using a while statement. I don't understand why it prints out 10 different game_id's in the first case but on the second bit of code it only does one. Can you go into further detail?

Many thanks
Chris

[laserlight]

It was my understanding that you don't use mysql_fetch_array()
when using a while statement.

Then why did you use it?

 while($row=mysql_fetch_array($sql)) { //start while open
$opentkts = $opentkts + 1;
$grandtotal = $grandtotal +1;
} //end while open

You must remember, the $row variable isnt automagically populated.
$row = mysql_fetch_array($query);
Populates $row with an array that contains a result set from $query.
The while loop is used to loop through the result set.
On each iteration, $row is set to the current row of the result set.
You can then use $row.

Before we continue, set your error reporting level to E_ALL
I suspect you should have received a notice about an undefined variable being used.

[chriswheat]

Ok I added:

error_reporting(E_ALL);

to the top of my code. It does not display any errors.
I must be dense on this one. if print"".$row['']."<br><br>";

returns
2
3
4
5
6
7
8
9
10
11

Yet when it replaced the print with the other code it returns:

---

vs. ANA -- 04/06/2004

TOTAL OPEN TKTS: 3

---

GRAND TOTAL: 3

I can't grasp why it only returns the above once for game_id 2
but it does not do it for 3,4,5,6,7,8,9,10,11

Many thanks
Chris

[laserlight]

Consider this corrected code:

$sql = mysql_query("SELECT * FROM `_tkts` WHERE `game_id`='{$row['game_id']}' AND `status`='open'")
	OR die(mysql_error()); //for debugging
//check that query returns at least 1 row
if (mysql_num_rows($sql) > 0) {
	echo "<table width=\"100%\" border=\"0\" cellpadding=\"5\" cellspacing=\"0\">";
	$opentkts = 0;
	//loop through result set
	while($row = mysql_fetch_array($sql)) {
		//print result for current row
		echo "<tr bgcolor=\"#CCCCCC\"><td>vs. {$row['agianst']} -- {$row['month']}/{$row['day']}/{$row['year']}</td></tr>";
		$opentkts++;
		$grandtotal++;
	}
	echo "<tr><td>TOTAL OPEN TKTS: {$opentkts}</td></tr>";
	echo "<tr bgcolor=\"#CCCCCC\"><td>GRAND TOTAL: {$grandtotal}</td></tr></table>";
}

The mistake is obvious.
You are only printing out that output once.
Your loop only increments the $opentkts and $grandtotal variables, aside from populating $row

What you want to do is this:

while end of $sql result set not reached
	place current row of $sql result set into $row
	increment $sql result pointer
	output elements from $row in given format
	increment $opentkts
	increment $grandtotal
endwhile

What you are doing is this:

output elements from $row in given format
while end of $sql result set not reached
	place current row of $sql result set into $row
	increment $sql result pointer
	increment $opentkts
	increment $grandtotal
endwhile

This pseudocode:

while end of $sql result set not reached
	place current row of $sql result set into $row
	increment $sql result pointer

is implemented as:

while ($row = mysql_fetch_array($sql))

[chriswheat]

Thanks for the help, however your code returns all sorts of errors, I did figure it out though. The problem was not with my logic, it was that the second sql query was overrided the first sql query. here was the fix:

$game = $row['game_id'];
$sql2 = mysql_query("SELECT * FROM _tkts WHERE game_id='$game' and status='open'");
if(mysql_num_rows($sql2) > 0) { //sart if open
print "<table width=\"100%\" border=\"0\" cellpadding=\"5\" cellspacing=\"0\"><tr bgcolor=\"#CCCCCC\"><td>vs. ".$row['agianst']." -- ".$row['month']."/".$row['day']."/".$row['year']."</td></tr>";
$opentkts = "0";
$grandtotal = "0";
while($row=mysql_fetch_array($sql2)) { //start while open
$opentkts = $opentkts + 1;
$grandtotal = $grandtotal +1;
} //end while open
print "<tr><td>TOTAL OPEN TKTS: $opentkts</td></tr>";
print "<tr bgcolor=\"#CCCCCC\"><td>GRAND TOTAL: $grandtotal</td></tr></table><br><Br>";
} //end if open

simply put by assiging the first sql result for $row[game_id] to $game the second sql result is no longer overided the original $row[game_id]

It now runs through each game and gives a report for the 10 results of the original sql.

Thanks
Chris

[laserlight]

oh, I see.
I didnt understand your original post.
If I did, I would have pointed that out immediately.

When asking your question, place your code in PHP formatting bbcode tags so that it can more easily be viewed, retaining its formatting.

In this case, the misunderstanding could have been avoided if you hadnt bothered with writing
print "'.$row['']."<br><br>";
but instead immediately gave the whole code snippet, and then pointed out the part where you suspected had the problem.

[chriswheat]

Thanks again for the help.

Chris