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>';
}}}
Nested while only returning 1 result
Moderator: General Moderators
-
webphotogeek
- Forum Newbie
- Posts: 18
- Joined: Sun Jul 04, 2010 12:11 pm
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: Nested while only returning 1 result
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
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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: Nested while only returning 1 result
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
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
Thanks
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: Nested while only returning 1 result
Before attempting to traverse the same results again.webphotogeek wrote:Where should I place mysql_data_seek() to make it work properly?