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?

Ben S · Accepted Answer · 2010-06-14 20:03:33Z

2

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.

answered Jun 14, 2010 at 20:03

Ben S

69.6k32 gold badges175 silver badges214 bronze badges

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

3 Comments

JM4 Over a year ago

my apologies. When I say 'create a table', I am referring to an HTML table on the 'administrator page'.

Ben S Over a year ago

I see. Disregard my answer then :|

Matt Brunmeier Over a year ago

The answer is still valid - you just need to go through the results to create the HTML table. I know count(*) will count null rows, but I'm not sure whether the group by would group 'null' refIDs together. I imagine it would, so your second query may not be necessary.

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 14, 2010 at 22:11

Zuul

16.3k6 gold badges63 silver badges88 bronze badges

Comments

TheLQ · Accepted Answer · 2010-06-14 21:38:57Z

0

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.

answered Jun 14, 2010 at 21:38

TheLQ

15k15 gold badges74 silver badges107 bronze badges

Collectives™ on Stack Overflow

MySQL Table Loop using PHP

GOAL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

GOAL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related