Home / Questions / 497947
Accepted
Valentina
Asked: 2022-04-18 19:36:44 +0800 CST 2022-04-18 19:36:44 +0800 CST

How to count total products in table union?

  • 772

I have the following query, in the products table (tbl_products) I am linked by the store id (id_store), what I need is to count how many published products the store has in the table (tbl_products), I tried the following but it does not count .

$stmt = $con->prepare("SELECT st.id_store,
                            st.photo,
                            st.store,
                            st.address,
                            st.phone,
                            st.email,
                            st.in_offer,
                            st.url,
                            COUNT(p.id_product) //p.id_store
                        FROM tbl_store st
                        LEFT JOIN tbl_products p ON st.id_store = p.id_store
                        WHERE st.id_user=?");
$stmt->bind_param("i",$id_user);
$stmt->execute();
$stmt->bind_result(
    $st_id_store,
    $st_photo,
    $st_store,
    $st_address,
    $st_phone,
    $st_email,
    $st_in_offer,
    $st_url
);
$stmt->fetch();
php

1 Answers

  1. Best Answer

    Well continuing what I told you in the comments, I will explain it to you in this answer a little better.

    To get the count of all the products that a store has, you can make a small subquery from the tbl_store table to the tbl_products table

    As follows:

    $stmt = $con->prepare("SELECT photo, store, address, phone, email, in_offer, url, 
   (SELECT COUNT(*) FROM `tbl_products` WHERE `tbl_store`.`id_store` = `tbl_products`.`id_store`) AS total
   FROM `tbl_store` WHERE `id_user` = ? LIMIT 1");

/* 
 * Acá el resto de tu codigo, una vez hecha la consulta
 * ...
 */

    What I have done here is to bring me a custom data from a subquery (Query that is inside ()the parentheses) , bringing the count of its rows in the related table tbl_products

    • 1