Page 1 of 1

Nested while only returning 1 result

Posted: Wed Aug 04, 2010 8:41 pm
by webphotogeek
I'm trying to pull data from a SQL table that match a certain criteria and if true, I want it to pull associated info from another table. I am using while loops and I got it to work, but it only pulls out the 1st result. I know I am missing something. Any help would be appreciated. It's been a while since I worked with PHP/MySQL. Here's the code. Thanks.

/* Get General Information */
$Res = "SELECT * FROM general"; $Results = mysql_query($Res) or die ('Could Not Access general Table: ');

/* Get Purchasing Information */
$Res_pur = "SELECT * FROM Purchasing WHERE Date BETWEEN '$beg_date' AND '$end_date'";
$Results_pur = mysql_query($Res_pur)or die('Could Not Access Purchasing Table: ' . mysql_error());

while ($Results_pur_Arr = mysql_fetch_array($Results_pur)) {
while ($Results_Arr = mysql_fetch_array($Results)) {
if ($Results_pur_Arr['ID'] == $Results_Arr['ID']) {
echo '<tr><td>'. $Results_pur_Arr['Date'].'</td><td>'. $Results_Arr['ID'].'</td><td>'.$Results_Arr['LName'].'</td><td>'.$Results_Arr['FName'].'</td>
<td>'.$Results_Arr['Address'].'</td><td>'.$Results_Arr['City'].'</tr>';
}}}

Re: Nested while only returning 1 result

Posted: Wed Aug 04, 2010 8:55 pm
by superdezign
Isn't "date" a reserved word? Whenever using a reserved word as a column title, you are required to surround it in "`" marks, as far as I know.

Re: Nested while only returning 1 result

Posted: Thu Aug 05, 2010 9:54 am
by webphotogeek
Date is the column (field) name and I've found it to work the way it is set up now. Most be something else. I think it has something to do with where the row pointer is for the inner loop.

Re: Nested while only returning 1 result

Posted: Thu Aug 05, 2010 5:30 pm
by superdezign
I didn't really ready your cod the first time because you neglected to use the [syntax] tags... I took another glimpse, and your inner loop will only traverse the elements for the first outer loop. That's the way the MySQL results work. If you want to traverse the same results again, either save them in an array and traverse that instead or use mysql_data_seek() to return to the first result at the end of every loop.

Re: Nested while only returning 1 result

Posted: Thu Aug 05, 2010 7:47 pm
by webphotogeek
Thanks for rereading the code. I was researching mysql_data_seek() and it does look like the right way to go, only problem is I tried it in different ways, but still couldn't get it to work right. Where should I place mysql_data_seek() to make it work properly?

Thanks

Re: Nested while only returning 1 result

Posted: Thu Aug 05, 2010 9:25 pm
by superdezign
webphotogeek wrote:Where should I place mysql_data_seek() to make it work properly?
Before attempting to traverse the same results again.