2. PHPRunner General questions
  3. [SOLVED] Should I have to reset a recordset within a loop
This topic is locked
[SOLVED]

Should I have to reset a recordset within a loop

 		5/29/2024 1:28:40 PM
PHPRunner General questions
P
 PK author

Hello All,
If I do this:

$sql= "select id from table";
while ($data = db_fetch_array($rs)) {
$id = $data["id"];
$sql1="select addres from table2 where ID = ".$id;
$rs1 = db_query($sql1, $conn);
$data1 = db_fetch_array($rs1);
$address=$data1["address"];
//then I do something with the $address
//here, do I need to reset $rs1 before the loop goes to the next record or will it auto overwrite the $rs1 variable when it gets to the next record in the loop?
}

If I need to reset the recordset, how do I achieve that?

Thank you
Percy
C
 		Chris Whitehead 5/30/2024

Percy, You can use "break" to get out of the loop, but when you say "reset" what exactly do you mean?

If you're just concered with the query within the loop, The result variable $rs1 will be reset becuase it's in the while loop and will be a new result based on the $sql1 query.

Are you running 2 DB connections or is that code from somewhere else as it doesn't have the PHPRunner DB API functions in it? Just curious really.
C
 		cristi 5/30/2024

Never use query inside loop....believe me...your sql server will thank you.Use JOIN or MySQL IN and do your thing outside or even without the loop.
P.S. The code is incomplete - you forgot to declare "$rs"....
P
 		PK author 5/30/2024

Thank you Chris and cristi.
I took your advise and used a JOIN to put table1 and table2 together into one results set, instead of getting the records from table1 and looping through them while querying table2 to find related records.
But Chris, to answer your question: table1 contains categories with a field that holds the max value allowed for the category. Table2 contains the actual data with assigned categories from table1. So what I was trying to do was to loop through the categories in table1 and use the ID to query table2 to see id the sum of a certain field meets the "max value" rule of the category. This way, I can tell the user exacly which lines in table2 are breaking the rules of its assigned category.
But now I JOIN the 2 tables and select only lines in table2 where the assigned category rule is voilated, then I show th user which categories to check and correct. Works better and much faster.
And it is go to know if I use $rs for something and then later use it again for somthing else, it will overwrite the first set of records. In other programming languages like vb.net, you have to do something like $rs.Reset or $rs.Clear or else, the new data will be added (appended) to the old data

Thanks again for your help.