Foreign key is disturbing can't handle

[UK-1991]

I have created a vendors page where I want to show how many vendors exist in the database. I have succcssfully done but main problem is that how do i show that which vendor contains how many products

There are 2 tables I have created 1 is products 2nd is vendors

<table style="border-collapse: collapse">
    <tr>
    <th>Vendor Name</th>
    <th>Vendor Address</th>
    <th>Vendor Email</th>
    <th>Product Quantity</th>
    </tr>

    <?php
    $query     = "SELECT * FROM vendors";
    $get_query = mysqli_query($connection, $query);

    while ($vendors_row =  mysqli_fetch_assoc($get_query)) {
    echo "<tr>";
    echo "<td>" . $vendors_row["v_name"]    . "</td>";
    echo "<td>" . $vendors_row["v_address"] . "</td>";
    echo "<td>" . $vendors_row["v_email"]   . "</td>";

    $query1       = "SELECT * FROM products";
    $get_query1   = mysqli_query($connection, $query1);
    $products_row = mysqli_num_rows($get_query1);

    echo "<td>"  . $products_row  . "</td>";
    echo "</tr>";
    }
    ?>
    </table>

    <?php
    mysqli_free_result($get_query);
    ?>

This is the structure of my products table

  `prod_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `product_price` int(20) NOT NULL,
  `prod_details` text NOT NULL,
  `vendor_v_name` varchar(45) NOT NULL,
  PRIMARY KEY (`prod_id`)

and This one is for vendors table

  `v_id` int(11) NOT NULL AUTO_INCREMENT,
  `v_name` varchar(45) NOT NULL,
  `v_address` varchar(125) NOT NULL,
  `v_email` varchar(255) NOT NULL,
  `prod_qty` int(11) NOT NULL,

I beleive here we will be putting concept of foreign key but how is this done we in products table i tried to use the forign key but not sure if it is done crrectly

[reygcalantaol]

Hello UK-1991,

I believe to properly structure your table you may change vendor_v_name to v_id as reference to vendor table, so:

 `prod_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) NOT NULL,
  `product_price` int(20) NOT NULL,
  `prod_details` text NOT NULL,
  `v_id` int NOT NULL,
  PRIMARY KEY (`prod_id`)

and your query to get number of products will be:

SELECT
  V.*,
  COUNT(P.`prod_id`) AS product_count
FROM products p
INNER JOIN P 
  ON (V.V_id=P.V_id)
GROUP BY V.V_id
ORDER BY product_count DESC;

Display the result:

    <tr>
    <th>Vendor Name</th>
    <th>Vendor Address</th>
    <th>Vendor Email</th>
    <th>Product Quantity</th>
    </tr>

    while ($vendors_row =  mysqli_fetch_assoc($get_query)) {
    echo "<tr>";
    echo "<td>" . $vendors_row["v_name"]    . "</td>";
    echo "<td>" . $vendors_row["v_address"] . "</td>";
    echo "<td>" . $vendors_row["v_email"]   . "</td>";
    echo "<td>" . $vendors_row["product_count"]   . "</td>";  
    }

[DaveAmour]

reyborn - will that SQL display vendors with Zero products?

[UK-1991]

@DaveAmour Sorry but can't got you.

There are total 6 products stored 4 belongs to Samsung and 2 belongs to LG

[UK-1991]

@reyborn didn't worked