MySQL & PHP: Associate columns into another table

[Nickmadd]

Hey guys seem to have tricky question here, I am just wondering how I could do the following:

 

I have an SQL table that contains these columns:

 

 

As you can see there is a 'Make' column and a 'Model' column and each row displays what make it is.

 

I now want to associate the 'Makes' with the 'Models' in another table just like this example:

 

BMW        AUDI        VOLVO        FORD        TOYOTA

X5            All Road    XC90           Focus        Supra

3 Series   A1             C30              Mustang    Yaris

5 Series                                         Galaxy

 

So for example if the first table has a row that contains the make 'AUDI' within the 'Make' column I then want it to take the value of the 'Model' and list it into my other table under the 'AUDI' column, any ideas how I can do this with PHP?

 

Thanks!

 

 

[mac_gyver]

i think you are asking how to extract the (unique) data from your existing database table and insert it into a make table, a model table, and a third table that relates the makes and models? please confirm/clarify?

[Nickmadd]

i think you are asking how to extract the (unique) data from your existing database table and insert it into a make table, a model table, and a third table that relates the makes and models? please confirm/clarify?

 

Yes I do need to relate the Make column with the Model column, I am unsure how this can be done. Why would I need three tables though?

[Barand]

I now want to associate the 'Makes' with the 'Models' in another table just like this example:

 

BMW        AUDI        VOLVO        FORD        TOYOTA

X5            All Road    XC90           Focus        Supra

3 Series   A1             C30              Mustang    Yaris

5 Series                                         Galaxy

 

Thanks!

 

I assume that the new table is an HTML table that you want to display because you certainly shouldn't have a SQL table in that format

<?php
include('db_inc.php');
$db = new mysqli(HOST,USERNAME,PASSWORD,'test');

$sql = "SELECT 
        make 
        , model
        FROM car
        ORDER BY make, model";
$res = $db->query($sql);
$data = array();
while (list($make,$model) = $res->fetch_row()) {
    $data[$make][] = $model;
}

?>
<table border='1' cellpadding='3'>
<tr>
    <th> 
    <?= join('</th><th>', array_keys($data)) ?>
    </th>
</tr>

<tr valign='top'>
    <?php
        foreach ($data as $models) {
            echo '<td>' . join('<br>', $models) . '</td>';
        }
    ?>
</tr>
</table>

Results would look like