Fetching rows from mysql several times embeded with php

Question

i m dealing with 2 tables.I just want to know whether there is something code that helps me fetching rows for multiple times without writing same query for that many times: Example:

while($row1=mysqli_fetch_array($result1)) {
   while($row2=mysqli_fetch_array($result2)){ 
       //checking for some condition 
   } 
}

In above code unlike array we cant reset a variable outside the inner loop as follows

while($row1=mysqli_fetch_array($result1)) { 
   $number=0;//so that v can start from first row 
   while($row2=mysqli_fetch_array($result2)){
     //checking for some condition 
   } 
}

I m totally aware that rows and array are different,So i m asking if there is FOR loop we can use on rows?? like:

while($row1=mysqli_fetch_array($result1)) { 
  for(...){
    //so dat it will xecute no.of whileloops*no.of for loops.
  }
}

if not clear ask for more. Your suggestions are much obliged.

Edit: table: Year 1999 2000 2002 2004 2000 $result=mysqli_query($con,"select distinct Year from table_name"); dataset=mysqli_fetch_array($result)..

Looks like you are trying to do the equivalent of an SQL JOIN in PHP, which is not a bad thing per se (provided the number of rows stay within reasonable bounds). However, to do that efficiently, I think you should get all results of the two queries in two PHP arrays and then work with them. If you don't, you will do a lot of duplicated queries to the database, which would be quite unefficient. — kuroi neko
– kuroi neko, Commented Jan 9, 2014 at 13:52
let alone array,i hav tried wid array.it o/ps something like Array[0]([0]=>1999... — user3172177
– user3172177, Commented Jan 9, 2014 at 14:00
@user3172177 try adding back ticks around your Year, not sure if its a reserved word in mysql. select distinct `Year` from table_name — user849001
– user849001, Commented Jan 9, 2014 at 14:38

kuroi neko · Accepted Answer · 2014-01-09 14:00:21Z

1

If I got it right, you want to match all the results of the first query against all the results of the second query.

In that case, you should first gather all the results of both queries in two PHP arrays (of arrays) and then work with these variables, like so:

$list1=array();
while ($row=mysqli_fetch_array($result1)) $list1[] = $row;
$list2=array();
while ($row=mysqli_fetch_array($result2)) $list2[] = $row;

foreach ($list1 as $row1)
foreach ($list2 as $row2)
{
    match_against ($row1, $row2);
}

Note that it looks very much like an SQL JOIN done in PHP.
Why not, as long as the number of results stay within reasonable bounds.

But if it's not the case (i.e. $list1 or $list2 could contain hundreds of elements), be aware that your code will very likely be a lot less efficient than what a DB engine can do.

That's why you might want to consider replacing this code with an SQL query.

answered Jan 9, 2014 at 14:00

kuroi neko

8,6781 gold badge23 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3172177 Over a year ago

arrays r weired in php,i tried wid ur sol..bt i hav to not only store d result bt also hav to display dem,if u do wid array it produces somthing:Array ( [0] => ) Array ( [0] => [1] => ) Array ( [0] => [1] => [2] => ) Array ( [0] => [1] => [2] => [3] => )...any oder way?

kuroi neko Over a year ago

I think you should get familiar with PHP arrays before trying to handle database queries. I used mysqli_fetch_array instead of $list1 = mysqli_fetch_all($result1) to try and make the mechanics more apparent to you, but I'm afraid getting comfortable with arrays is mandatory if you want to do some PHP programming.

Mikkel Over a year ago

Your "a lot less efficient" statement really depends on use case. If the nested loops can be replaced with a better WHERE/JOIN that results in significantly less data being returned, then you're right. However, if the OP actually needs 1000s of records * 1000s of records, PHP will use less memory. Also, it's better to err on the side of slightly slower PHP code than more complex DB queries, since web servers scale much more easily than database servers.

voodoo417 · Accepted Answer · 2014-01-09 13:53:22Z

0

Try this (if i understood ):

while( list($row1,$row2) = array(mysqli_fetch_array($result1),mysqli_fetch_array($result2)) ){
   // check condition 
}

answered Jan 9, 2014 at 13:53

voodoo417

12.2k3 gold badges39 silver badges43 bronze badges

1 Comment

user3172177 Over a year ago

no array,it produces weired result..not only hav to store bt lso hav to display dem,,wich is quite cumbersome.

score 0 · Accepted Answer · 2014-01-09 14:00:25Z

0

$dataSet1 = mysqli_fetch_all($result1);
$dataSet2 = mysqli_fetch_all($result2);
foreach($dataSet1 AS $row1){
  foreach($dataSet2 AS $row2){

  }
}

Something like this might work?

edited Jan 9, 2014 at 14:00

answered Jan 9, 2014 at 13:51

user849001

1 Comment

user849001 Over a year ago

@user3172177 What exactly isn't working, do you get an error?

Collectives™ on Stack Overflow

Fetching rows from mysql several times embeded with php

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related