I want to count column values in a few different tables with different filters...
I can do the long way which would be like this;
$result = $mysqli->query("SELECT COUNT(Gender) FROM table where Gender = 'F'");
$Female = $result->fetch_row();
$result = $mysqli->query("SELECT COUNT(Class) FROM table2 where Class = 'SprintT'");
$ST = $result->fetch_row();
$result = $mysqli->query("SELECT COUNT(Class) FROM table2 where Class = 'SprintD'");
$SD = $result->fetch_row();
Which allows to me edit the page anyway I want, but seems like a waste to connect to the database 100 times just for the data when I could do something like below.
Or I can search the follow way;
$builder = "SELECT ";
$builder .= "b.Gender, b.State, b.Country, ";
$builder .= "d.Class, d.Category, d.ShirtSize, d.Hear, d.AmountPaid, d.Discounts, d.Status ";
$builder .= "FROM table = b ";
$builder .= "LEFT OUTER JOIN table2 = d ";
$builder .= "on b.id = d.ID";
$stmt = $mysqli->query($builder);
$registeredinfo = array_count_values($stmt->fetch_assoc());
foreach ($registeredinfo as $key => $val) {
print "$key = $val <br />";
}
Produces;
M = 1
Ohio = 1
USA = 1
SprintT= 1
AgeGroup = 1
PreviouslyParticipated = 1
But this doesn’t seem to go through all lines I pull, example if I had 2 lines instead of one I would expect the numbers to change to 2 but they remain at 1.
Anyone have any ideas how I pull a lot of categories and then count them efficiently?