CODEWITHSUNDEEP
phpmysqli
durian
durian

Reputation: 520

while loop inside while loop MySQL

I have two tables in MySQL, one stores both have a main id key, but different details inside, for example:

table 1: | id | name | type |

table 2: | id | secondary id | more dateails |

The simplified code I'm using:

$status = $mysqli->query("SELECT * FROM `table1`");

while($rows = $status->fetch_array(MYSQLI_BOTH)) 
{
    $id = $rows['id'];
    $conts_q = $mysqli->query("SELECT * FROM `table2` WHERE id='$id'");
    $conts_numr = $conts_q->num_rows;

    if($conts_numr==0)
    {
        //Display empty
    }
    else
    {
        while($row2 = $conts_q->fetch_array(MYSQLI_BOTH))
        {
            //Get details and display
        }
    }
}

If the second table has no contents, it shows it fine, but if it does, it shows all of the contents for each id instead of only the ones relevant for it, I cant seem to find what I'm doing wrong. If the code I posted is not sufficient I will post the full original code here.

Upvotes: 0

Views: 101

Answers (1)

dethtron5000
dethtron5000

Reputation: 10841

As was stated in the comments, the best way to get this data is to use a JOIN.

SELECT * FROM `table1` t1
INNER JOIN `table2` t2 ON t1.id = t2.id

You may need to massage the returned columns and such as well.

Upvotes: 2

Related Questions