MySQL count include to jSon object

Question

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 :)

In the future, you should try to indent your code. It's better for everyone. — Álvaro González
– Álvaro González, Commented Feb 24, 2010 at 12:00

Mathieu · Accepted Answer · 2010-02-24 10:27:27Z

1

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']

answered Feb 24, 2010 at 10:27

Mathieu

5,7033 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Répás Over a year ago

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.../tagcloud.php on line 39 That is the $query = mysql_query("SELECT tag, COUNT (*) as count FROM tags GROUP BY tag"); line. :(

Álvaro González Over a year ago

@Zoltan Repas You must always check the return value of mysql_query(): it can return a result set... or not. See the manual for more info.

shylent · Accepted Answer · 2010-02-24 10:24:29Z

0

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

answered Feb 24, 2010 at 10:24

shylent

10.1k6 gold badges41 silver badges56 bronze badges

Collectives™ on Stack Overflow

MySQL count include to jSon object

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related