Which is faster, if either...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

turbo2ltr
Forum Commoner
Posts: 29
Joined: Sun Jul 18, 2004 4:08 pm

Which is faster, if either...

Post by turbo2ltr »

I need to iterate through the results of 1 query multiple (possibly hundreds) of times.

Which is faster:
Just keep calling mysql_fetch_array to get the rows, then calling mysql_data_seek every time I want to reset the array pointer and iterate again,

OR

Copy all the rows from mysql_fetch_array into a local array and just use the local array to do the iteration?

The data in the array/result doesn't change.

Thanks,
Mike
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

I've never heard of having to use mysql_data_seek to reset the array returned by mysql_fetch_array. Am I mis-interpreting your statement?
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

If you're asking what I think you're asking, I suggest this:

Code: Select all

for($i=0;$i<mysql_num_rows($result);$i++){ 
$row[] = mysql_fetch_assoc($result);
//where result is you mysql_query
Cycle through the results like this:

Code: Select all

for($i=0,$i=sizeof($row);$i<$i;$i++){ 
echo $row[i]['field'] //blah blah blah
Good luck!
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

evilmonkey wrote:

Code: Select all

($i=0,$i=sizeof($row);$i<$i;$i++)
When will $i ever be less than itself?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Could you not just use a simple while() loop and break it whenever the last record is reached. I think that is what I would do but perhaps I am thinking wrongly.
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

evilmonkey wrote:If you're asking what I think you're asking, I suggest this:

Code: Select all

for($i=0;$i<mysql_num_rows($result);$i++){ 
$row[] = mysql_fetch_assoc($result);
//where result is you mysql_query
Cycle through the results like this:

Code: Select all

for($i=0,$i=sizeof($row);$i<$i;$i++){ 
echo $row[i]['field'] //blah blah blah
Good luck!
well, that coudl also be done like :

Code: Select all

while($row = mysql_fetch_assoc($result))
{
   echo $row['myfield'];
   // or just $field1[] = $row['field1'];  $field2[] = $row['field2']; etc..
   // of course declaring the variables you want to use as arrays before hand
}
and of course, cycle through them if you need

Code: Select all

for($i=0; $i<count($field1); $i++)
{
   echo 'Do Something with '.$field1[$i].' or '.$field2[$i];
}
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

That is exactly what I was thinking infolock. i posted just above you :)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

great minds think alike :P
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

nigma wrote:
evilmonkey wrote:

Code: Select all

($i=0,$i=sizeof($row);$i<$i;$i++)
When will $i ever be less than itself?
My stupididty, that shouldn't be there. :oops:
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

Why would you use mysql_fetch_assoc() when you can use mysql_fetch_array()?

Say you have the following declarations:

Code: Select all

$query1 = $query2 = mysql_query("select username from table");
$row1_query1 = mysql_fetch_array($query1);
$row2_query2 = mysql_fetch_array($query2);
As far as I know you can, in both cases, get the value of the username field by doing the following:

Code: Select all

echo $row1_query1['username'];
echo $row2_query2['username'];
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

infolock, my solution produces a two dimensional array. This is if you're dealing with more tn one row and need to have it stored as opposed to outputted and forgotten about.
turbo2ltr
Forum Commoner
Posts: 29
Joined: Sun Jul 18, 2004 4:08 pm

Post by turbo2ltr »

Ok, guess I didn't explain it well enough..

which is faster:

Code: Select all

$result = mysql_query("SELECT * FROM rating_fields ");

$x=0;
while($field_s = mysql_fetch_array($result ,MYSQL_ASSOC))
&#123;
    $fieldarray&#1111;$x] = $field_s&#1111;rating_tablename];
    $x++;
&#125;

for($y=0;$y< someval;$y++)
&#123;
   foreach($fieldarray as $field)
   &#123; 
      // do something with $field
   &#125;
&#125;
OR

Code: Select all

$result = mysql_query("SELECT * FROM rating_fields ");

for($y=0;$y< someval;$y++)
&#123;
    while($field = mysql_fetch_array($result ,MYSQL_ASSOC))
    &#123;
         // do something with $field
     &#125;
     mysql_data_seek($result, 0);
&#125;
edit: fixed code errors.....
Last edited by turbo2ltr on Tue Jul 20, 2004 10:12 pm, edited 3 times in total.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

nigma wrote:Why would you use mysql_fetch_assoc() when you can use mysql_fetch_array()?
Programming preference, I never use mysql_fetch_array(), always mysql_fetch_assoc(), no idea why...
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

lol, same thing with me, when I first learned how to do this I learned to do it with mysql_fetch_array() and I guess it stuck :)
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

PHP Manual wrote: mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array. This is the way mysql_fetch_array() originally worked. If you need the numeric indices as well as the associative, use mysql_fetch_array().
=P
Post Reply