multiple mysql_fetch_ internal pointers?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

multiple mysql_fetch_ internal pointers?

Post 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)){
      ....
}
}
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Coded properly it's fine, but there may be optimizations in coding it differently.
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

Post by pedrotuga »

like what?

isnt it recomended to use "while"?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

pedrotuga wrote:isnt it recomended to use "while"?
If a sql-JOIN can do the trick, probably not.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
pedrotuga
Forum Contributor
Posts: 249
Joined: Tue Dec 13, 2005 11:08 pm

Post 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
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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`
Post Reply