CODEWITHSUNDEEP
phpmysql
Dustin
Dustin

Reputation: 4459

MySQL: Display number of records based on 2 columns with PHP

I have the following columns..

Name, pageID, promoID

Bob, 123, promo1
Steve, 123, promo1
Nancy, 123, promo2
Lyn, 123, promo2
Amy, 123, promo2
Jeff, 123, promo2
Joe, 456, promo3
Bill, 456, promo3
Joe, 456, promo3

I want to create a report of only records that have pageID of 123 and then group them by promoID. So my table output would look like this..

Records for pageID 123..
promo1 = 2 entries
promo2 = 4 entries

What query would accomplish this?

Here's what I currently have spitting out into an HTML table..

$querysubmissions = mysql_query("SELECT promoID FROM submissions WHERE fbPageID = '123'");
$numbersubmissions = mysql_num_rows($querysubmissions); 


$query = mysql_query("SELECT * FROM submissions WHERE fbPageID = '$page_id' GROUP BY promoTitle");

while($data = mysql_fetch_assoc($query)){
    $title = $data['promoTitle'];
    echo '<tr>';
    echo '<td>'.$title.'</td>';
    echo '<td>'.$numbersubmissions.'</td>'; 
    echo "<td><a href='submission-csv.php?promoid=".$data['promoID']."'>Export</a></td>";
    echo '</tr>';
    }

    mysql_close();

But this just returns the total number of submissions for the 123 pageID and all promoIDs. And how would I echo this out in PHP?

Upvotes: 0

Views: 243

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

Ths query will display in the 'promo1 = 2 entries' format

SELECT 'Records for pageID 123' ResultDisplay
UNION
SELECT
    CONCAT(promoID,' = ',cnt,' entr',IF(cnt=1,'y','ies'))
FROM
    (SELECT promoID,count(1) cnt 
    FROM submissions  
    WHERE pageID = '123' 
    GROUP BY promoID) A;

Here is your sample data loaded into a submissions table:

mysql> use junk
Database changed
mysql> drop table if exists submissions;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> create table submissions
    -> (name varchar(10),pageID int,promoID varchar(10)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into submissions values
    -> ('Bob'  , 123 , 'promo1'),
    -> ('Steve', 123 , 'promo1'),
    -> ('Nancy', 123 , 'promo2'),
    -> ('Lyn'  , 123 , 'promo2'),
    -> ('Amy'  , 123 , 'promo2'),
    -> ('Jeff' , 123 , 'promo2'),
    -> ('Joe'  , 456 , 'promo3'),
    -> ('Bill' , 456 , 'promo3'),
    -> ('Joe'  , 456 , 'promo3');
select * from submissions;Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from submissions;
+-------+--------+---------+
| name  | pageID | promoID |
+-------+--------+---------+
| Bob   |    123 | promo1  |
| Steve |    123 | promo1  |
| Nancy |    123 | promo2  |
| Lyn   |    123 | promo2  |
| Amy   |    123 | promo2  |
| Jeff  |    123 | promo2  |
| Joe   |    456 | promo3  |
| Bill  |    456 | promo3  |
| Joe   |    456 | promo3  |
+-------+--------+---------+
9 rows in set (0.00 sec)

Here is execution of the query:

mysql> SELECT 'Records for pageID 123' ResultDisplay
    -> UNION
    -> SELECT
    ->     CONCAT(promoID,' = ',cnt,' entr',IF(cnt=1,'y','ies'))
    -> FROM
    ->     (SELECT promoID,count(1) cnt
    ->     FROM submissions
    ->     WHERE PageID = '123'
    ->     GROUP BY promoID) A
    -> ;
+------------------------+
| ResultDisplay          |
+------------------------+
| Records for pageID 123 |
| promo1 = 2 entries     |
| promo2 = 4 entries     |
+------------------------+
3 rows in set (0.03 sec)

Give it a Try !!!

Here it is in PHP

$givenpageID = 123;
$sql = "SELECT 'Records for pageID " . $givenpageID . "' ResultDisplay UNION SELECT CONCAT(promoID,' = ',cnt,' entr',IF(cnt=1,'y','ies')) FROM (SELECT promoID,count(1) cnt FROM submissions WHERE PageID = '" . $givenpageID . "' GROUP BY promoID) A";
$result = mysql_query($sql);
if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}
while ($row = mysql_fetch_assoc($result)) {
    echo $row["ResultDisplay"];
}
mysql_free_result($result);

Upvotes: 0

AndyD273
AndyD273

Reputation: 7279

You can do all of this in the sql query itself.

Select PromoID, Count(PromoID) as pCount From submissions where fbPageID = '123' Group By PromoID

You can also do this for more than one page

Select concat('Page ',fbPageID,', ',PromoID,' = ',Count(PromoID)) as result From submissions Group By fbPageID, PromoID

This has the added bonus of not needing to do a select for each page. so your results would look like

Page 123, Promo1 = 2
Page 123, Promo2 = 4
Page 456, Promo3 = 3

Upvotes: 0

Mario Lurig
Mario Lurig

Reputation: 792

$querysubmissions = mysql_query("SELECT promoID,COUNT(promoID) as count FROM submissions WHERE fbPageID = '123' GROUP BY promoID");
$numbersubmissions = mysql_num_rows($querysubmissions);

Upvotes: 0

Naftali
Naftali

Reputation: 146310

Try GROUP BY in your select and use count(*):

SELECT promoID, count(*) as `count`
FROM submissions 
WHERE fbPageID = '123'
GROUP BY promoID;

Upvotes: 5

Related Questions