linking 2 mysql databases.

[unistake]

hi all,

 

I am trying to link 2 mysql tables and display some information from each of them.

 

I have a list of all the possible items for sale in table1 and I am trying to count the number of rows in the other table2 where the items exist.

 

E.g. 'table1'

manufacturer      model

      man1                  item1

      man1                  item2

      man1                  item3

 

'table2'

    id                model

        1                    item3

        2                    item3

        3                    item2

 

And the result would show:

item1(0)

item2(1)

item3(2)

It would list all the items from table1 and show next to it how many rows are related to that item from table2.

 

I have inserted a quote where I have tried many times to enter something similar to that show in the note below - but I can not get it to work - it just shows the total number of models in table1 for a given manufacturer.

 

The php I have made so far is:

 

<?php
    case 'manufacturer':
        $query = "
            SELECT * 
            FROM table1";
        $query .= "
            WHERE manufacturer = '".$data."' ";
        $query .= "
            ORDER BY model ";

        $result = mysqli_query($cxn,$query);
        $returnData[''] = "Select a Model...";
        while($row = mysqli_fetch_assoc($result)){
// I THINK I NEED TO INSERT SOMETHING LIKE $query2 = "SELECT * FROM table2 WHERE model = table1.model";
    $k=$row['model'];
    $k2=$row2['model'];
            $counter[$k]+=1;
            $returnData[$k]=$k;
        }
        
        foreach($counter as $k => $row)
        {
            $returnData[$k] .= " ($row)";
        }

        break;
?>

 

 

[radar]

try:

 

$query = "SELECT a.manufacturer, a.model, b.id, b.model as name

FROM table1 as a

LEFT JOIN table2 as b ON b.model = a.model

WHERE a.manufacturer = '".$data."'

GROUP BY b.model, a.model ORDER BY a.model";

 

havent tested it, but should work.

[OldWest]

You can write a simple query like (not tested but should get you in the right direction):

 

$query = "SELECT * FROM table1, table2 WHERE table1.model = table2.model";

 

Or instead of an INNER JOIN style as above, you might want to consider a RIGHT or LEFT JOIN depending on the exact results you are looking for.

[OldWest]

And if you just need a count on the results, why not just use phps count() function?

[radar]

for count you could add SQL_CALC_FOUND_ROWS right after select and then do:

 

$_query = mysql_query("SELECT FOUND_ROWS() as total");

$_row = mysql_fetch_array($_query, MYSQL_ASSOC);

$cnt = $_row['total'];

and you'd have your count.