Count(id) in MySQL into a string

Question

Hello, I have the SQL query:

$query = "SELECT yearoffiling, COUNT( id )
FROM files
GROUP BY yearoffiling
LIMIT 0 , 30";

How do I put the values from COUNT(id) inside a string?

Thank you.

Answer 1

$query = "SELECT yearoffiling, COUNT( id ) total FROM files GROUP BY yearoffiling LIMIT 0 , 30";

$result = mysql_query($query);

while ($ary = mysql_fetch_assoc($result)){

    echo $ary['total'];

}

This is how I would do it "old school". I recommend using prepared statements and PDO these days. Hope this helps.

Answer 2

You can use either the convert() or cast() functions to do this.

SELECT cast(COUNT(id) as char), ....

Answer 3

Jeremy, just to add to yours i think you may need the AS attribute to do the modifaction

$query = "SELECT yearoffiling, COUNT( id ) AS total FROM files GROUP BY yearoffiling LIMIT 0 , 30";

Answer 4

There's two steps to this:

1. Define a column alias for the COUNT(id)
2. Optional: Change datatype MySQL uses for the column to minimize datatype conversion issues

Defining a Column Alias

A column alias is always defined after the column declaration, including computed columns like COUNT(id) in your example. As you've noticed, they're required for computed columns in order to reference the value in other places. You can use the AS notation, or simply declare the alias within single quotes. Examples:

COUNT(id) AS count_id
COUNT(id) 'count_id'

Both are valid syntax, but mind that if you use MySQL keywords you'll have to use backticks (`) to escape the value for the query to execute.

You can name an alias anything you like, but it really should be informational. If unsure, use the hallway test - randomly ask people in the hallway if it makes sense.

Changing the Datatype

This is required when you deal with any programming that does not perform implicit type casting, which would require you to get the column value (in this case an integer) and convert it to a string (or any other desired data type - decimal, float, etc.). You can use either of the MySQL CAST or CONVERT functions - they are synonyms of one another. Examples:

CAST(COUNT(id) AS VARCHAR(4))
CONVERT(COUNT(id), VARCHAR(4))

Be aware that this is occurring with the realm of MySQL, so you can only CAST/CONVERT to MySQL datatypes.