Nested while only returning 1 result

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
webphotogeek
Forum Newbie
Posts: 18
Joined: Sun Jul 04, 2010 12:11 pm

Nested while only returning 1 result

Post 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>';
}}}
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Nested while only returning 1 result

Post 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.
webphotogeek
Forum Newbie
Posts: 18
Joined: Sun Jul 04, 2010 12:11 pm

Re: Nested while only returning 1 result

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Nested while only returning 1 result

Post 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.
webphotogeek
Forum Newbie
Posts: 18
Joined: Sun Jul 04, 2010 12:11 pm

Re: Nested while only returning 1 result

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: Nested while only returning 1 result

Post by superdezign »

webphotogeek wrote:Where should I place mysql_data_seek() to make it work properly?
Before attempting to traverse the same results again.
Post Reply