MySql - simple question

I have a table:

id names
1 john, nick
2 luke, nick
3 simon, john, nick
4 alfred, mike, joe, joey, nick

What I would like ti do now is to make a query that would provide me with the names that have been used the most... Like:

name often
Nick 4
john 2
luke 1
simon 1
alfred 1
mike 1
joe 1
joey 1

Thank you for your answers

 

SELECT names FROM table ORDER BY id ASC

 

No that would put out something like
john, nick
luke, nick
simon, john, nick
alfred, mike, joe, joey, nick

 

i'm not sure how you;d do that might require you too use split function?

i'm not sure

 

select names, count(*) from table group by names

 

RaginBajin, wouldn't that count how many rows have the exact same content ?
For example
jim joe
jim joe
luke joe

would be like:
jim joe 2
luke joe 1

But I would like it like:
joe 3
jim 2
luke 1

 

Ahh well, then you have some issues that you won't be able to solve with SQL. Since that name column is one column, you can just split on that and have the engine run that.

You could split the column, put it into a temporary table, and then do a count on it.

As a FYI, it is a very bad idea to have a name column with data that you have setup now.. In the future, you should really seperate out your data into seperate columns.

 

Hi Lukaslo,

Of the top of my head you could do the following if your using PHP, this would give the following output, you would still need to look up some of the sorting commands in the PHP manual to get the correct order.



john 2
nick 4
luke 1
simon 1
alfred 1
mike 1
joe 1
joey 1

$query = "SELECT names FROM test_table WHERE 1";
$result = mysql_query($query) or die("Invalid query: " . mysql_error());

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

$names = explode(',',$data['names']);

	foreach($names as $name){
	$name = trim($name);
	$name_count[$name] += 1;
	}
}


foreach($name_count as $name => $count){
echo $name.' '.$count.'<br>';
}

PHP:

Like RaginBajin said though you really need to redesign your database.

I hope this helps.

 

Thank you, I am acctualy using something a little bit different

<?php
include 'config.php';

$values = '';
$result = mysql_query("SELECT tags FROM story");
while($row = mysql_fetch_array($result))
    $values .= $row['tags']

$values = explode(' ', $values);
print_r(array_count_values($values));
?>

Code (markup):

But thank you for your explanation.

BTW Why did you use this: mysql_fetch_assoc ?

 

No particular reason, I just copied and pasted it from some other sample code I had.


Does that code work for you?


I get with $values = explode(' ', $values);
Array ( [john,] => 2 [nickluke,] => 1 [nicksimon,] => 1 [nickalfred,] => 1 [mike,] => 1 [joe,] => 1 [joey,] => 1 [nick] => 1 )

Or

Array ( [john] => 1 [ nickluke] => 1 [ nicksimon] => 1 [ john] => 1 [ nickalfred] => 1 [ mike] => 1 [ joe] => 1 [ joey] => 1 [ nick] => 1 )

With $values = explode(',', $values);

It doesn’t seems to split the names or count them properly?

 

Oh sorry I forgot to tell you that I am not using , anymore