mysqli count

[cartaysm]

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?

[TRI0N]

$i = 0;
$result = $mysqli->query("SELECT COUNT(Gender) FROM table where Gender = 'F'");
$i = $i + 1 ;
$Female = $result->fetch_row();

 

Echo $i for your counter...

[cartaysm]

Thank you for your response. That isnt exactly what I was looking for, the place you added a counter is working fine, that pulls a single column and counts it. The problem I am having is that if I use that select statement i will have to use about 100 different statements which seems like a waste.

 

The other option is below where I iterate through an array that was built with joins, the problem is that it only pulls 1 record. I could try and add a counter to the iteration to see if produces more values. Is that what you meant?

 

$registeredinfo = array_count_values($stmt->fetch_assoc());

foreach ($registeredinfo as $key => $val) {

print "$key = $val <br />";

}

[cartaysm]

As it turns out I finally found the answer just posting it for anyone else that stumble accross this

 

$result = $mysqli->query("SELECT Category from Table");
$arr = array();
while ($states = $result->fetch_row()){
foreach ($states as $state => $value) {
$arr[] = $value;
}
}
$arr = array_count_values($arr);
foreach ($arr as $key => $value) {
print("".$key.": ".$value."");
}

 

This code will pull multiple rows and then split and count each row...