CODEWITHSUNDEEP
phpmysql
LamSam
LamSam

Reputation: 45

php mysql select count from 2 tables

I have 2 tables in Mysql

Table1: item(itemNO, type,name)
Table2: remark (itemNO, content)

I use while for viewing the data of item

$sql="SELECT * FROM item WHERE NO ='$NO' ORDER BY rand()  LIMIT 5";
$result = mysqli_query($connect,$sql);
while($row = mysqli_fetch_array($result)){
$row['itemNO'];
$row['Type'];
$row['name'];

}

I want to show count of the remark(s) for the item which share same itemNO in two tables.
I tried to create new query in while for counting the remark for the item but fail.

What I should do for showing the count of remark in while?

Upvotes: 0

Views: 828

Answers (4)

JYoThI
JYoThI

Reputation: 12085

No need multiple query . simple use JOIN and GROUP BY CLAUSE 

 select i.* ,r.*,count(r.itemNO) as remark_count 
 from item as i 
 left join remark as r 
 on (i.itemNO=r.itemNO) 
 WHERE i.itemNO='$No'
 group by i.itemNO

Upvotes: 0

Ivo P
Ivo P

Reputation: 1712

this could be done in 1 query:

SELECT item.itemNO, item.type, item.name,
       COUNT(*) AS totalRemarks
FROM item
LEFT JOIN remark ON remark.itemNO = item.itemNO
GROUP BY item.itemNO, item.type, item.name
ORDER BY RAND() 
LIMIT 5

Left join, as you probably want to see items with no remarks as well

I notice a flaw: in case of no remarks, it will still mention "1"

this change could solve it:

SELECT item.itemNO, item.type, item.name,
       COUNT(remark.itemNO) AS totalRemarks
FROM item
LEFT JOIN remark ON remark.itemNO = item.itemNO
GROUP BY item.itemNO, item.type, item.name
ORDER BY RAND() 
LIMIT 5

Upvotes: 1

DEarTh
DEarTh

Reputation: 1005

You should use INNER JOIN

$sql="SELECT count(*) FROM item INNER JOIN remark ON item.itemNO = remark.itemNo WHERE item.NO ='$NO'";

Upvotes: 0

Sonu Bamniya
Sonu Bamniya

Reputation: 1115

Use join for this. try this query

$sql="SELECT count(itemNO) as totalItem FROM item i inner join remark r on r.itemNO = i.itemNO";

Upvotes: 0

Related Questions