Nested Select statement

[pop-eye]

I have two tables that I need to search for information related to a transaction. Not sure if this is the most efficient way of doing it but, I am doing something like:

$sqlstr="select * from abc for var1='John'";
$sqlres=mssql_query($sqlstr);
$foundrecs=mssql_num_rows($sqlres);
$ABCresultststable = mssql_fetch_array($sqlres, MSSQL_BOTH);
while ($ABCrestultstable = mssql_fetch_array($sqlres, MSSQL_BOTH)) {
$owner=$ABCresultstable[1];
echo "
<input type='text' value='$ABCresultstable[2]'>
.......
other HTML stuff
.......";
$sqlstr="select * from xyz for var1='$owner'";
$sqlres=mssql_query($sqlstr);
$foundrecs=mssql_num_rows($sqlres);
$XYZresultststable = mssql_fetch_array($sqlres, MSSQL_BOTH);
echo "
<input type='text' value='$XYZresultstable[3]'>
.......
.......";
};

 

The problem seems to be when I do the "select" for XYZ, I lose the "While" pointer. It exits the "while" statement even there are more records to display from ABC.

 

Any suggestion will be appreciated. Thanks.

[DavidAM]

$sqlstr="select * from abc for var1='John'";
$sqlres=mssql_query($sqlstr);
$foundrecs=mssql_num_rows($sqlres);
$ABCresultststable = mssql_fetch_array($sqlres, MSSQL_BOTH);
while ($ABCrestultstable = mssql_fetch_array($sqlres, MSSQL_BOTH)) {
$owner=$ABCresultstable[1];
echo "
<input type='text' value='$ABCresultstable[2]'>
.......
other HTML stuff
.......";
$sqlstr="select * from xyz for var1='$owner'";
// THIS NEXT LINE WIPES OUT THE RESOURCE FROM THE OUTER LOOP
// ** USE A DIFFERENT VARIABLE HERE - AND WHEREVER YOU REFERENCE IT
$sqlres=mssql_query($sqlstr);
$foundrecs=mssql_num_rows($sqlres);
$XYZresultststable = mssql_fetch_array($sqlres, MSSQL_BOTH);
echo "
<input type='text' value='$XYZresultstable[3]'>
.......
.......";
};

 

You are trashing your Outler Loop query resource (see comment in code) with the second query.

 

Also, just before your loop, you call fetch_array() but you never use the data. So your loop is not processing the first record in the result.

[pop-eye]

Thanks for the replay. But, I have since resolved this problem by using the JOIN capability of SQL to join the two tables first and create all the information that I need from both tables in one record. It works great and a lot less painful than what I was trying to do. Thanks anyway.