8 replies to this topic

[Thevenin]

Hi all, I have an easy question for you.

I have a MySQL database with some entries, like these:

Quote

id ------ id_name
1 ------ Mark
2 ------ Jordan
3 ------ Peter
4 ------ Lucas
5 ------ Mark
6 ------ John

I'd like to have something like this:

Quote

Mark (2)
John (1)
Jordan (1)
......
......
......

The query could be:

$query=" S_ELECT * FROM MyTable  ";


$result=mysql_query($query);

echo mysql_error();

But I don't know how to continue.
Any help please?

[webcodez]

Hi there,

Think this is what you would like to do:

(created small example function)

function getCount($id_name) {

  $query = "SELECT COUNT(*) FROM MyTable WHERE id_name = '$id_name' "; //gets the amount of times a row with the id_name equal to the value of '$id_name' -occurs
  $result  = mysql_query($query);
  $fetch   = mysql_fetch_assoc($result);
  return $fetch['COUNT(*)'];

}

//for example:
echo "Mark(".getCount('Mark').")";

Or anything similiar.

Let me know if this is what you meant ;)

Cheers.

[SoN9ne]

I would recommend that you use

COUNT(1)

instead of

COUNT(*)

Although this seems to be a matter of choice since I have never seen a benchmark to prove the count(1) is faster...

I think this is what you are trying to do. This will return 2 columns of results. The first will be the name and the second will be the total count of times it is used in the db.

SELECT DISTINCT id_name, (SELECT count(1) FROM MyTable tb2 WHERE tb1.id_name=tb2.id_name) as total
FROM MyTable tb1

You should use this when fetching the data on the page; this is meant to be a one time query. I wouldn't recommend calling the getCount() function for every name since it could easily get heavy on the system. Not to mention the memory footprint that would cause on larger systems.

The previous code would work if you are only using it for a smaller system, for larger systems I would recommend a more optimized approach.

[Orjan]

It's much simpler. just use GROUP in sql.

SELECT name, count(name) AS occurances FROM table GROUP BY name

now, you get a table back containing the names and the occurances for each name in the table.

[webcodez]

yep, that's basicly what I meant =]. Just included an example of the actual use of the results.

[Thevenin]

Sorry to be a n00b, just a question.

I used this code:

unction getCount($id_name) {


  $query = "SELECT COUNT(*) FROM MyTable WHERE id_name = '$id_name' "; //gets the amount of times a row with the id_name equal to the value of '$id_name' -occurs

  $result  = mysql_query($query);

  $fetch   = mysql_fetch_assoc($result);

  return $fetch['COUNT(*)'];


}


//for example:

echo "Mark(".getCount('Mark').")";  

But, maybe is a stupid question, there could be a way to not specify a name and for example "echo" the first 5 people with the highest count?
Like this:

Quote

Mark (7)
John (5)
Jordan (4)
Peter (2)
John (1)
Ron (1)

[Orjan]

This SQL will do that.

SELECT name, count(name) AS occurances FROM table GROUP BY name ORDER BY occurances DESC LIMIT 5

[Thevenin]

Wonderful, sorry I've done a mistake in the transcription of the code.
Resolved.

Thank you very very much.:thumbup:

[nofolo]

thanks for information, useful for me...