In PHP how can I get the count of each instance in a mySQL table column?

Question

To clarify, I am trying to get a table to output that displays my MySQL database table in a certain way. The table is populated with names, birth-years, and zodiac animals.

What I want to do is have a table row that says something like "cat zodiac signs" and then a heading of "name" and "birth year". Under that I want each persons' name and year they were born. I want this done for each zodiac animal.

The issue I am having is that the heading for is displaying each instance of the zodiac column. I know my code is wrong, I am just not sure what to do next! Please help! Here is my code

$TableName = "zodiacshared";
        $SQLstring = "SELECT * FROM $TableName GROUP BY sign, birthyear";
        $QueryResult = @mysql_query($SQLstring, $DBConnect);
        if (mysql_num_rows($QueryResult) == 0){
            echo "<p> There are no entries in the chinese zodiac gallery! </p>";
        }
        else{
            echo "<table width = '100%' border = '1'>";
            while ($row=mysql_fetch_array($QueryResult)){
                    echo "<th colspan='2'>"; echo $row['sign']; echo " zodiac signs</th>";
                    echo "<tr><th>Name</th><th>Birth Year</th></tr>";
                    echo "<tr>";
                    echo "<td>"; echo $row['name']; echo "</td>";
                    echo "<td>"; echo $row['birthyear']; echo "</td>";
                    echo "</tr>";

            }
            echo "</table>";
            mysql_free_result($QueryResult);
        }

Edit: Right that may be helpful My hopeful output is something like this (this is a rough draft made on Excel, but gets point across):

desired output

edit 2: Solution was:

$current_zodiac= '';
            while ($row=mysql_fetch_array($QueryResult)){
                    if ($row['sign']!=$current_zodiac){
                        echo "<th colspan='2'>"; echo $row['sign']; echo " zodiac signs</th>";
                        echo "<tr><th>Name</th><th>Birth Year</th></tr>";
                    }else{
                        //echo the other rows
                    }
                        echo "<tr>";
                        echo "<td>"; echo $row['name']; echo "</td>";
                        echo "<td>"; echo $row['birthyear']; echo "</td>";
                        echo "</tr>";
                    $current_zodiac = $row['sign']; //assign new zodiac to current zodiac variable

            }

can you show in a text table what you want the output to look like and some sample data in the sql table. Pictures don't shine a lot of light on things, half the time. Plus we can't use them as effectively — Drew
– Drew, Commented Dec 4, 2015 at 2:56

Alif Jamaluddin · Accepted Answer · 2015-12-04 03:10:24Z

1

You can have one variable to store current zodiac name in your while loop. So every time the zodiac name is unique which means new zodiac found in the while loop, you can print new header for it.

For example, the flow could be like this.

$current_zodiac = '';
while ($row=mysql_fetch_array($QueryResult)){
if ($row['sign']!=$current_zodiac){
 echo "<th colspan='2'>"; echo $row['sign']; echo " zodiac signs</th>";
}else{
//echo the other rows
}
$current_zodiac = $row['sign']; //assign new zodiac to current zodiac variable

}

answered Dec 4, 2015 at 3:10

Alif Jamaluddin

5184 silver badges18 bronze badges

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

1 Comment

renegade_ghost Over a year ago

Thanks so much! That idea mostly worked, I tweaked it slightly and now it works. Without your baseline it would have taken much longer, thank you.

Collectives™ on Stack Overflow

In PHP how can I get the count of each instance in a mySQL table column?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related