Ioannis Pantelidis Ioannis Pantelidis -4 years ago 70
PHP Question

From two tables from my database i would like to echo a column with click counts

In my database i have created two tables , the one is "categories" and the other "click_count".
The two tables have the following information : categories( cat_id, cat_name , cat_description ) and click_count(id, cat_id, cat_count). I have already written a php code which echo a table with information about categories and i have already written a php code whick calculates the click counts, so i want a php script which i can echo on the same table the information about click_count and specify the "cat_count" which contains the number about "clicks" . The following code is obviously wrong but you can get the logic.

<?php
$sql4 = "SELECT categories.cat_id,categories.cat_name,click_count.cat_id,click_count.cat_count WHERE categories.cat_id=click_count.cat_id";
$result4 = mysqli_query($conn, $sql4);
$row4 = mysqli_fetch_array($result4);
while($row4 = mysqli_fetch_assoc($result4)) {
echo '<td>'.$row4['cat_count']; }?>

Answer Source

The SELECT statement should contain a JOIN:

SELECT categories.cat_id, categories.cat_name, click_count.cat_id, click_count.cat_count 
FROM categories
LEFT JOIN click_count 
ON categories.cat_id = click_count.cat_id;  

...and you can also add a WHERE clause at the end if you need it to select not all, but only the ones that fit a certain condition.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download