PHP show number of rows in a table with conditions

Question

So I have a mysql table called posted_ads I want to show a statistic on the website which is the number of ads posted.

There are also 2 types of ads type1 and type2 these are in a column in the same mysql table called ad_type

So the question is what would be the shortest possible php code to show the numbers e.g:

Number of type 1 ads = XXX (e.g: 500) Number of type 2 ads = XXX

XXX being the number of ads (rows)

Code that I do have working is...

<?php $result = mysql_query("select count(1) FROM posted_ads"); $row = mysql_fetch_array($result); $total = $row[0]; echo "Number of ads " . $total; ?>

However, I want to show 2 different stats, one that shows the number of type1 ads and the number of type 2 ads. There is a column in that table called 'ad_type' and the value is either 'type 1' or 'type 2'

---- I SOLVED IT---- Thanks for all your help. But I have now figured it out. I am using 2 bits of code, one for each ad type as follows:

<?php $result = mysql_query("select count(1) FROM posted_ads WHERE ad_type = 'type1';");
$row = mysql_fetch_array($result);
$total = $row[0];
echo "Number of type 1 ads" . $total; ?>


<?php $result = mysql_query("select count(1) FROM posted_ads WHERE ad_type = 'type2';");
$row = mysql_fetch_array($result);
$total = $row[0];
echo "Number of type 2 ads" . $total; ?>

You are missing the point. The number should be the number of ads. The numbers I posted 1000 and 500 where just for example — , Apr 22 '14 at 03:45
Show us your table structure like what to relate in these two tables — kimbarcelona, Apr 22 '14 at 03:48
Code that I do have working is... However, I want to show 2 different stats, one that shows the number of type1 ads and the number of type 2 ads. There is a column in that table called 'ad_type' and the value is either 'type 1' or 'type 2' — , Apr 22 '14 at 03:51

score 2 · Answer 1 · edited Apr 22 '14 at 03:49

2

This might help:

SELECT COUNT(*) FROM posted_ads GROUP BY ad_type

edited Apr 22 '14 at 03:49

Amal Murali

75,622
18
128
150

answered Apr 22 '14 at 03:47

Adesh Pandey

769
1
9
22

GROUP BY ad_type is good, but how do i group by type1 and type2 ? There is a column in that table called 'ad_type' and the value is either 'type 1' or 'type 2' – b_uk_happy Apr 22 '14 at 03:58
something like GROUP BY ad_type WHERE ad_type = type1 ??? dont know the correct syntax – b_uk_happy Apr 22 '14 at 04:00

score 0 · Answer 2 · edited May 23 '17 at 12:05

You could just run a mysqli_num_rows on your result, which would give you the total, like below. Note that the Type 1 process and the type 2 process are the same, 1 simply uses all one line (shorter code as you requested) and type 2 is a little easier to follow.

$link = mysqli_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysqli_select_db($link, DB_NAME) or die("Couldn't select database");

$Type1Total = mysqli_num_rows(mysqli_query($link, "SELECT `ad_type` FROM `posted_ads` WHERE `ad_type` = 'type1'"));

$Type2Query = "SELECT `ad_type` FROM `posted_ads` WHERE `ad_type` = 'type2'";
$Type2Result = mysqli_query($link, $Type2Query);
$Type2Total = mysqli_num_rows($Type2Result);

And then output the total accordingly

echo "number of type 1 ads = " . $Type1Total . "<br> " number of type 2 ads = " . $Type2Total;

PHP show number of rows in a table with conditions

2 Answers2