current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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>";
}

}
?>
share|improve this question
    
That's the normal way to fetch data, nothing wrong with it –  Pekka 웃 Nov 23 '13 at 15:19
    
Sorry, I misunderstood the question. Depending on what you want to do with the data, this is the way to do it - like Pekka said. –  kingkero Nov 23 '13 at 15:28
    
@Pekka웃, dont we have a another good way to handle this ? –  user3025146 Nov 23 '13 at 15:29
    
@kingkero thanks for your reply, but doesn,t it slow down the performance ? –  user3025146 Nov 23 '13 at 15:32
    
Ah, the code looks different now. –  Pekka 웃 Nov 23 '13 at 15:32

4 Answers 4

up vote 1 down vote accepted

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>";
  }
}
share|improve this answer
    
working perfectly I feel this must be the best way to do it. –  user3025146 Nov 23 '13 at 17:33
up vote 0 down vote

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

share|improve this answer
up vote 0 down vote

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>";

    }
    ?>
share|improve this answer
    
this method is also working well, simple and direct way to get results –  user3025146 Nov 24 '13 at 8:00
up vote 0 down vote

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
share|improve this answer
    
i tried this as well, this is also working well. simple and direct way to do the same. –  user3025146 Nov 23 '13 at 19:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.