Page 1 of 1
multiple mysql_fetch_ internal pointers?
Posted: Wed Aug 16, 2006 9:21 pm
by pedrotuga
is there any problem in using more than one pointer simultaniously?
like
Code: Select all
while($rows1=mysql_fetcharray($result1)){
$sql="select * from table";
$result2=mysql_query($sql);
while($rows2=mysql_fetcharray($result2)){
....
}
}
Posted: Wed Aug 16, 2006 9:39 pm
by feyd
Coded properly it's fine, but there may be optimizations in coding it differently.
Posted: Thu Aug 17, 2006 12:35 pm
by pedrotuga
like what?
isnt it recomended to use "while"?
Posted: Thu Aug 17, 2006 12:40 pm
by volka
pedrotuga wrote:isnt it recomended to use "while"?
If a
sql-JOIN can do the trick, probably not.
Posted: Thu Aug 17, 2006 1:01 pm
by feyd
There's JOINs as ~volka noted, which I would pursue first. If a JOIN isn't possible or prohibitive, which is rare, there's the cached data method where you store all the resultant data that would be in the inner loop into an array so you aren't fetching from the database all the time. The last, and quite often worst, is the nested loop.
To keep the inner loop from short circuiting during the second iteration of the outer loop, you must use
mysql_data_seek().
I spoke of them in order of general preference from most preferred, to least.
Posted: Thu Aug 17, 2006 5:17 pm
by pedrotuga
mmm... i see...
in my case i am fetching at the most 3 rows in the inner loop and an average of 2 in the outer one, i think using dataseek would only increase the complexity and the amout of execptions to catch.
In a situation with larger that handling i guess dataseek would be more eficient
Posted: Thu Aug 17, 2006 5:21 pm
by s.dot
I do it how feyd mentioned. It was hard for me to grasp the concept at first.. so I can show a short example.
Code: Select all
$storage = array();
$result1 = mysql_query($sql);
while($array1 = mysql_fetch_assoc($result1))
{
$storage[] = $array1['whatever_you_need'];
}
$result2 = mysql_query($sql);
while($array2 = mysql_fetch_assoc($result2))
{
//the storage array can now be referenced in here
//instead of doing another loop
}
Posted: Thu Aug 17, 2006 8:26 pm
by Ollie Saunders
Good advice from scottayy there. Avoid nesting wherever possible. I've drowned in it before.
Also you can do a lot of your join like stuff without an actual join using a WHERE clause
Code: Select all
SELECT * FROM `A`,`B` WHERE `aId` = `bForeignKeyOfA`