MySQL Table Loop using PHP

Question

I have an online form which collects consumer data and stores in a dedicated MySQL database. In some instances, data is passed in the URL under the "RefID" variable which is also stored in the database and attached to each registration.

I use the 'mysql_num_rows ($result)' to fetch all agent details on another page but this only returns ALL available details. My goal is as follows:

GOAL

I want to create an HTML table in which rows are automatically generated based on the list of all registrations on my site. A new row is created IF and ONLY IF a unique RefID is present on that particular record. In the event the field is NULL, it is reported on a single line.

In short, the HTML table could look something like this:

RefID - Number of Enrollments

abc123 - 10

baseball - 11

twonk - 7

NULL - 33

Where abc123 is a particular RefID and 10 is the number of times that RefID appears in the DB. If a new registration comes in with RefID = "horses", a new row is created, showing "horses - 1". The HTML table will be viewable by account administrators needing to see the number of enrollments for a particular RefID (which they won't know ahead of time).

Anybody have any suggestions?

Answer 1

My suggestion is to not create a table for this. Simply query the existing table for the numbers when you need them:

SELECT RefID, COUNT(*) AS num_enrollments FROM yourTable GROUP BY RefID;

This query won't count all the NULLs, if you really need the results to show the NULLs do this:

(SELECT RefID, COUNT(*) AS num_enrollments FROM yourTable GROUP BY RefID)
UNION
(SELECT "NULL", COUNT(*) FROM yourTable WHERE RefID IS NULL);

If you really want a table representation of this information, use a view using one of the above queries.

Answer 2

The only (ugly) solution I can think of is to select only distinct values on the RefID column, then do a separate query with COUNT.

Answer 3

I think this is what you want, I've test it and returns correct values:

<?php

$query = mysql_query(" SELECT DISTINCT RefID FROM test_this ");

echo '<table>';

while ($data = mysql_fetch_array($query)) {

    $uniq = mysql_num_rows(mysql_query(" SELECT * FROM test_this where RefID = '".$data['RefID']."' "));

    echo '<tr><td>'.$data["RefID"].'</td><td>'.$uniq.'</td></tr>';



}

echo '</table>';

?>

If its not the solution to your problem, just let me know, I'll try to help better!

EDITED: The result from the above was a table with:

abc123 - 5

baseball - 4

twonk - 6