unistake Posted November 8, 2010 Share Posted November 8, 2010 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; ?> Quote Link to comment Share on other sites More sharing options...
radar Posted November 9, 2010 Share Posted November 9, 2010 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. Quote Link to comment Share on other sites More sharing options...
OldWest Posted November 9, 2010 Share Posted November 9, 2010 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. Quote Link to comment Share on other sites More sharing options...
OldWest Posted November 9, 2010 Share Posted November 9, 2010 And if you just need a count on the results, why not just use phps count() function? Quote Link to comment Share on other sites More sharing options...
radar Posted November 9, 2010 Share Posted November 9, 2010 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.