MSSQL Query While Looping through Previous Result Set 1

[menkes]

I am having an issue with doing a query while looping through a result set. I do use a database abstraction layer written years ago by Frank Kromman...but not sure if that is causing the problem.

Here is an example of what is going on (I am typing this on the fly):

$sql1 = "Select name, age, gender from tblPERSON";
$rs1 = $db->Query($sql1);
while($row1 = $db->FetchRow($rs1))
{
  $list = '';
  $sql2 = "Select book from tblBOOKS Where age_grp = $row1[age]";
  $rs2 = $db->Query($sql2);
  while($row2 = $db->FetchRow($rs2))
  {
    $list .= "$row2[book], ";
  }

  echo "$row1[name], a $row1[age] year old $row1[gender] would like these books: $list";
}

If I run the above, what I will get is the first field from $rs1 only. If I modified $sql2 to be "SELECT book, author from ..." then I would get the first 2 fields from $rs1. And of course, if I modify $sql2 to be "SELECT book, author, isbn FROM ..." I get the first 3 fields...

Obvious pattern, and probably a newbie issue...but I do not know what to do. In my real query I need 10 fields from $rs1 and only one from $rs2 (there are only 4 fields in the table referenced in $rs2).

Thanks in advance for any assistance with this.

 

[jpadie]

just change the queries to add in the columns that you want.

 

[menkes]

Thanks for responding...

However, it does not address the issue. Once I run the second query, I can only access the number of columns in the first query that I selected in the second.

Sooo...if I have 3 columns in the rs1, and 1 in the rs2, I get:

rs1[col1], rs1[col2], rs1[col3], rs2[col1]
rs1[col1], rs2[col1]
rs1[col1], rs2[col1]
.... and so on

It's like once I make the second query, the first query will only access up to the index value of the second.

 

[jpadie]

i have found one of Frank's abstraction layers and you would be right in thinking that it might be the cause of at least some concerns. the reason is because it makes use of the internal variable called column that is reset each time a query is called. thus the number of cols is changed by the second query. this control is not needed.

so you have three choices:

1. use a better abstraction layer
2. fix the abstraction layer
3. change your code

for expediency i'd go with the 3rd option...

$sql1 = "Select name, age, gender from tblPERSON";
$rs1 = $db->Query($sql1);
$rowF = $row1 = $row2 = array();
while($rowF[] = $db->FetchRow($rs1))
{ //do nothing }
  foreach ($rowF as $row1){
    $list = '';
    $sql2 = "Select book from tblBOOKS Where age_grp = $row1[age]";
    $rs2 = $db->Query($sql2);
    while($row2 = $db->FetchRow($rs2)) {
     $list .= "$row2[book], ";
    } //close while loop on internal query
  echo "$row1[name], a $row1[age] year old $row1[gender] would like these books: $list";
} //close foreach loop

you could probably also recraft your query to do everything you needed in one go (with a sub-select). if you're interested in that method, check out the relevant forum.

i have also posted a replacement fetchRow which you might want to use instead of the above fix. you need to put the code into the mssql class cMSSQL

function FetchRow($rs,$type=2) { 

	$permittedTypes = array(1=>'ASSOC', 2=>'NUMERIC', 3=>'BOTH', 4=>'OBJECT');
	//error check the incoming type
	$type = (in_array($type, array_keys($permittedTypes))) ? $type :2;
	//spin the variable to a more user friendly nature
	$type = $permittedTypes[$type];
            if (is_resource($rs)) { 
            		switch ($type){
				case "ASSOC":
					$rowData = @mssql_fetch_array($rs, MSSQL_ASSOC);
					break;
				case  "NUMERIC":
					$rowData = @mssql_fetch_array($rs, MSSQL_NUM);
					break;
				case "BOTH":
					$rowData = @mssql_fetch_array($rs, MSSQL_BOTH);
					break;
				case "OBJECT":
					$rowData = @mssql_fetch_object($rs);
					break;
			}
		} //close if
	return $rowData; 
}

 

[menkes]

Excellent response. While all the options seem viable, I chose to modify the abstraction layer directly. Not only does it work, but appears to run a bit faster.

Thanks!