counting one short

greatepier

😕
I want to count the field "doctype" in table (binary_data)

$Telregels = "select count(*) from binary_data where doctype like 'B%'";
$Telling = mysql_query($Telregels,$Verbinding);
$Aantal = mysql_fetch_row($Telling);
echo $Aantal[0];

Initially I thought this worked, but it returns one record less then in the database.
I now have 71 one records, but this script returns 70.
What I am I doing wrong?

Actually I wanted to have a table like
doctype number of records
A 10
B 70

total 80
Is that possible in one command?

nemonoman

What I am I doing wrong?

The missing record isn't LIKE 'B%'. Maybe you added a space before the B? so it's like ' B%'

Is that possible in one command?
No.

You can get the
A 10
B 70
part with one query:

select count(*) from binary_data GROUP BY SUBSTRING(doctype, 1,1)
and the

total 80
with a second SQL query or with PHP code.

greatepier

Thanks

The missing record isn't LIKE 'B%'. Maybe you added a space before the B? so it's like ' B%'

You were right! I made a typo but overlooked it everytime.

I now can run individual queries which give:
B 72
G 1
R 1
total 74

select count(*) from binary_data GROUP BY SUBSTRING(doctype, 1,1)

I changed the code to:

print ("<br><br> Totalen per groep ")  ;
$Telregels = "select count(*) from binary_data GROUP BY SUBSTRING(doctype, 1,1)";
$Telling = mysql_query($Telregels,$Verbinding); 
$Aantal = mysql_fetch_row($Telling);
echo $Aantal[0];

This returns:
Totalen per groep 72

It gives only the total for "B" and it doesn't do the grouping grouping. What do I do wrong?

nemonoman

You need a loop:

 print ("<br><br> Totalen per groep ")  ;
$Telregels = "
SELECT CONCAT(SUBSTRING(doctype,1,1),' ', COUNT(*))
FROM binary_data 
GROUP BY SUBSTRING(doctype, 1,1)";
$Telling = mysql_query($Telregels,$Verbinding); 
//loop through all the returned rows -- one for each letter
while($Aantal =mysql_fetch_array($Telling)){
    echo $Aantal[0]<BR>;  

}

I also added the first letter of the doctype to the query

greatepier

Your script is unfortunately failing and gives the following error:

Totalen per groep
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in
.........line 28

Line 28 is:

while($Aantal = mysql_fetch_array($Telling)){

I searched the internet and found http://nl3.php.net/mysql_fetch_array.
There they mentioned "result_type ->
The type of array that is to be fetched. It's a constant and can take the following values: MYSQL_ASSOC, MYSQL_NUM, and the default value of MYSQL_BOTH. "

So I also tried

while($Aantal = mysql_fetch_array($Telling, MYSQL_BOTH)){

But that gives the same error.

Any idea how to fix this?

nemonoman

That error message typically means your query failed because of some error.

In this case, there was an extra comma in the query. I just edited the origninal code above and it now SHOULD work. Copy it out NOW, Paste the edited version and try again.

No way to find out if it works until you try it, however, since you've got the database and I don't.

greatepier

The new code works, except the <br> part, but I found a workaround.
So it now works !

Thank you nemonoman for your help.