mysql count include to json object

Question

Hi!

I made a php/ajax/mysql/json script for my site, to show all the tags.

If i GET the page id it will show me all the tags of that page. But! In my main page i'd like to show all tags, from all pages WITH a counter.

For example if i tagged 4 pages with "ilovestackoverflow" i'd like to see in my main page "ilovestackoverflow (4)"

i know i can count the lines with mysql COUNT() but unfortunatlety i can't use it in this case, however i tried much way :(

(function sqlnez($value) is my stripslashes script)

the php:

$server = mysql_connect($host, $user, $password);
$connection = mysql_select_db($database, $server);
//if got pageID
if(isset($_GET['id'])) {
$query = mysql_query("SELECT * FROM tags WHERE id=".sqlnez($_GET['id'])."");
$json = "({ tags:["; 
for ($x = 0, $numrows = mysql_num_rows($query); $x < $numrows; $x++) {
$row = mysql_fetch_assoc($query);
$json .= "{tag:'" . $row["tag"] . "'}";
if ($x < mysql_num_rows($query) -1)  
$json .= ",";  
else  
$json .= "]})";
}
}
//this is the part what needs help
//if got nothing
else { 
$query = mysql_query("SELECT * FROM tags GROUP BY tag");
$json = "({ tags:["; 
for ($x = 0, $numrows = mysql_num_rows($query); $x < $numrows; $x++) {
$row = mysql_fetch_assoc($query);
$json .= "{tag:'" . $row["tag"] . "',counter:'" . I WANT THE COUNTER HERE . "'}";
if ($x < mysql_num_rows($query) -1)  
$json .= ",";  
else  
$json .= "]})";
}
}
$response = $_GET["callback"] . $json;
echo utf8_encode($response);
mysql_close($server);

Thanks for your help and time :)

Answer 1

Instead of writing json by hand (this is an extremely bad idea, no matter what) you should use json_encode

Answer 2

your sql request should be SELECT tag, COUNT (*) as count FROM tags GROUP BY tag then replace I WANT THE COUNTER HERE with $row['count']