CODEWITHSUNDEEP
phpmysqlarrayswhile-loopfetch
user3025146
user3025146

Reputation: 19

Get result of mysql_query inside while of mysql_fetch_array

I am using a code something like below to get data from the second table by matching the id of first table. Code is working well, but I know it slow down the performance, I am a new bee. Please help me to do the same by an easy and correct way. 

<?php
$result1 = mysql_query("SELECT * FROM table1 ") or die(mysql_error());
while($row1 = mysql_fetch_array( $result1 ))
{
$tab1_id = $row1['tab1_id'];
echo $row['tab1_col1'] . "-";

$result2 = mysql_query("SELECT * FROM table2 WHERE tab2_col1='$tab1_id' ") or die(mysql_error());
while( $row2 = mysql_fetch_array( $result2 ))
{
echo $row2['tab2_col2'] . "-";
echo $row2['tab2_col3'] . "</br>";
}

}
?>

Upvotes: 1

Views: 1811

Answers (4)

Adrian Rutkowski
Adrian Rutkowski

Reputation: 1

Like Sushant said, it would be better to use one JOIN or simpler something like that:

 SELECT * FROM table1, table2 WHERE `table1`.`id` = `table2`.`id

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

You can join the two tables and process the result in a single loop. You will need some extra logic to check if the id of table1 changes, because you'll only want to output this value when there's a different id:

<?php
// Join the tables and make sure to order by the id of table1.
$result1 = mysql_query("
  SELECT
    *
  FROM 
    table1 t1
    LEFT JOIN table2 t2 ON t2.col1 = t1.id
  ORDER BY
    t1.id") or die(mysql_error());

// A variable to remember the previous id on each iteration.
$previous_tab1_id = null;

while($row = mysql_fetch_array( $result1 ))
{
  $tab1_id = $row['tab1_id'];
  // Only output the 'header' if there is a different id for table1.
  if ($tab1_id !== $previous_tab1_id)
  {
    $previous_tab1_id = $tab1_id;
    echo $row['tab1_col1'] . "-";
  }

  // Only output details if there are details. There will still be a record
  // for table1 if there are no details in table2, because of the LEFT JOIN
  // If you don't want that, you can use INNER JOIN instead, and you won't need
  // the 'if' below.
  if ($row['tab2_col1'] !== null) {
    echo $row['tab2_col2'] . "-";
    echo $row['tab2_col3'] . "</br>";
  }
}

Upvotes: 1

Guilherme Soares
Guilherme Soares

Reputation: 736

You can use this. One relation with two tables:

 <?php
    $result1 = mysql_query("SELECT tab2_col2, tab2_col3 FROM table1, table2 where tab2_col1 = tab1_id ") or die(mysql_error());

    while($row1 = mysql_fetch_array( $result1 ),)
    {

        echo $row2['tab2_col2'] . "-";
        echo $row2['tab2_col3'] . "</br>";

    }
    ?>

Upvotes: 0

sushant-hiray
sushant-hiray

Reputation: 1898

Instead of having 2 while loops, what you can do is join the 2 tables and then iterate over the result.

If you're not sure what join is look here: https://dev.mysql.com/doc/refman/5.1/de/join.html

Also here is a fairly simple query written using join: Join Query Example

Upvotes: 0

Related Questions