counting mysql

[electricocean]

How do I count how many feild have the same name... such as movies that are funny all have the same genre: comedy. So i would count all of the genre comedy in the mysql row.

how would I do that?

thanks,
electricocean

[StupidScript]

$getcount=mysql_query("SELECT count(id) AS howmany FROM table WHERE genre='funny'");

while ($row=mysql_fetch_array($getcount)) {

echo $row["howmany"];

}

There are a couple of ways ... that's one of 'em! ;)

[dkin]

$getcount = mysql_query("SELECT genre FROM table");
$num_rows = mysql_num_rows($getcount);

echo $num_rows;

[mcibor]

Much better is

SELECT genre, COUNT(*) as total FROM table GROUP BY genre

will return:
genre, total
comedy, 58
horror, 70
drama-thriller, 64
drama, 32

Best regards
Michal Cibor

[electricocean]

Hi, I am trying to make a for loop that makes the list of genres formatted.... Like This:

$query = "SELECT * FROM jokes ORDER BY genre";// Probably change query to one the ones you suggested
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
$genre = $row['genre'];
$title = $row['title'];
$joke = $row['jokes'];
$rating = $row['rating'];
for($something //Have no idea how to make a for loop
){
$display_joke = displayJoke('$genre', '$title', '$joke');//my function
echo "<a name=\"$genre\"><span class=\"titlebar\">$genre</span>";
//Create under title(jokes in the genre):echo "<p class=\"jl\"><a href=\"$display_joke\">$title</a></p>";
}

Is there any way to accomplish this?

electricocean

[mcibor]

You are trying the same thing I want to do. you can accomplish that task in two ways:
1. Get list of genres and for each of them select jokes with that genre. (Many sql queries)
2. Get all jokes sorted and on genre change write something. (One sql query, hard php programing)

The first is much easier, as you have to just call a function
function genre($genre){$sql = "SELECT jokes FROM table WHERE genre='$genre';//and spit it out
}

$sql = "SELECT DISTINCT genre FROM table ORDER BY genre";
//query and
while($row = mysql_fetch_assoc($query)){//write genre and some text
genre($row["genre"]);//call function
}

[mcibor]

Second way is harder to progam, as it requires you to think more. What you have is simple sql query:

$sql = "SELECT jokes, genre FROM table ORDER BY genre";
...
while($row = mysql_fetch_array($query))
{
if(!$old) //do the first row stuff - write beginning and first joke
else
{
//check if $row has different genre than $old. If no then write the joke, if yes then write the genre and the joke
if($row["genre"] == $old) //write the genre and the joke
else //write the joke
}
$old = $row["genre"];//write genre to $old to remember last written genre
}

That's it! These are two ways you can what you want. If you come up with some other way please write, as it might be easier than that.
Best regards
Michal Cibor

[electricocean]

Theres no for loops....ok

in the 2nd one what is '$old'?

could I change '$old' in the if statements to a for loop if that's what '$old' is?

electricicoean

[electricocean]

whats the diff between mysql_fetch_array(); and mysql_fetch_assoc(); mcibor used both...

electricocean

[mcibor]

$old is the genre of previous joke. The sql will eg. spit out this:
joke1, max
joke2, max
joke3, police

//So when there's no $old you write first genre: $old = null;
Jokes on MAX:
//and first joke
joke1
//Then you remeber in old the genre: $old = max
//Next joke has the same genre, so you just write it:
joke2
//Next joke has different genre than in $old, so you have to write the genre:
Jokes on POLICE
//And write the joke as well:
joke3
//And put the genre in $old = police

That's it
Hope it cleares things a bit for you
Best regards
Michal Cibor

[mcibor]

mysql_fetch_array gives you both number and association, mysql_fetch_assoc gives only association. Moreover in mysql_fetch_array you can specify what you want to get:

mysql_fetch_array($ans, MYSQL_BOTH): [1] => "bla", ["bla"] => "bla"
mysql_fetch_array($ans): [1] => "bla", ["bla"] => "bla"
mysql_fetch_row($ans): [1] => "bla"
mysql_fetch_assoc: ["bla"] => "bla"

There's more to mysql_fetch_array. You can read about it at [php.net...]

Best regards
Michal Cibor

[electricocean]

but it says if(!$old) before $old is called....