Populate HTML table from MYSQL and counting rows

Question

I'm trying to populate a HTML table with mysql data, I have the following data in Mysql:

ID, IP, Referal

I want to create a table that shows a list of the referals and how often they occur, for example:

ID,    IP,      Referal
1     1.1.1.1   google.com
2     2.2.2.2   google.com
3     3.3.3.3   test.com
4     4.4.4.4   another.com

Should output:

google.com     2
test.com       1
another.com    1

What I've tried was this:

<table class="table table-bordered table-primary">
<tbody>
<?php
$sql="SELECT * FROM traffic";
$result=mysql_query($sql);
?>
<?php while($row = mysql_fetch_array($result)) { ?>
<tr >
<td class="tc"><font face="Arial, Helvetica, sans-serif"><?php if($row['referal']==''){
echo "Empty Referal";
} else { echo $row['referal']; }?></font></td>

<td class="tc"><center><font face="Arial, Helvetica, sans-serif"><?php $referal = $row['referal'];
$sql="SELECT COUNT(*) FROM traffic WHERE referal = $referal";
$num_rows = mysql_num_rows($result);

echo "$num_rows";

?></font></center></td>

</tr>
                    <?php } ?>
            </tbody>
        </table>

But that didn't count each refer individually, also it created a new table row for each entry even if the referal was the same.

Any help is greatly appreciated.

can you print the output which you are getting it from query by using this print_r($result); — Venkata Krishna
– Venkata Krishna, Commented Mar 26, 2013 at 18:00

Misch · Accepted Answer · 2013-03-26 18:09:08Z

2

You are probably looking for the GROUP BY keyword of SQL:

SELECT Referal, COUNT(*) FROM traffic GROUP BY Referal

This will give you exactly the table you want, without any additional for-loop in php

answered Mar 26, 2013 at 18:09

Misch

10.9k4 gold badges37 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

UnholyRanger · Accepted Answer · 2013-03-26 18:07:15Z

2

The thing is that you are gathering the amount of rows found in your last query and NOT your count query:

$sql="SELECT COUNT(*) FROM traffic WHERE referal = '$referal'";
$num_rows = mysql_num_rows($result);

First off, the COUNT command will return you a single row with a single column containing the count found. You should use the following:

$sql="SELECT COUNT(*) as count FROM traffic WHERE referal = '$referal'";
$numResult = mysql_fetch_array(mysql_query($sql));
$num_rows = $numResult['count'];

Also, it is VERY unneeded to put quotes around a variable you are echoing. This will suffice:

echo $num_rows; //NOT echo "$num_rows";

NOTICE: Do not use MySQL_* functions as they have been deprecated as of PHP 5.5. Use MySQLi_* or PDO instead.

answered Mar 26, 2013 at 18:07

UnholyRanger

1,97114 silver badges24 bronze badges

3 Comments

Marius Prollak Over a year ago

Thanks, this works beautifully. It groups the count accordingly now. Although I still have the Issue that it creates a new table row for every entry, even if it is the same referal. If you can hint me in the right direction that will be greatly appreciated !

Marius Prollak Over a year ago

GROUP BY Referal sorted that one out, unfortunately I can't accept 2 answers :(

UnholyRanger Over a year ago

You can use the query @Misch suggested as the main query or use the DISTINCT command to grab distinct referrals

Collectives™ on Stack Overflow

Populate HTML table from MYSQL and counting rows

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related