Need Help With mySQL Query

[jamesbrauman]

Hello all, how are you going?

I have a table inside my database called 'literatureworks'. This table contains information about lots of different literature works. This table is set out like so:

 

[id]  [name]  [category]  [genre]  [date_submitted] ....

 

I am using PHP and MySQL, and I want to craft a query to return the number of items per genre where the category is equal to the category I specify, as well as the current genre name and category name. I will then loop through the rows retured (hopefully there is one row per genre returned), and echo this to my page. My brain is half-dead this morning, so forgive me, but this is what I have so far:

 

//Query the database and get the result

$result = mysql_query("SELECT * FROM literatureworks, COUNT(name) WHERE category='Fiction' GROUP BY genre");

//Start our loop

while ($ow = mysql_fetch_array($result)) {

$currentcategory = "Fiction";

$currentgenre = $row['genre'];

$totalitemsingenre = $row['COUNT(name)'];

echo "$currentcategory: $currentgenre contains $totalitemsingenre items.";

}

 

I have no idea where to go from here, thankyou for reading.

[awpti]

SELECT COUNT(name) as name_count FROM literatureworks WHERE category = 'Fiction' GROUP BY genre

 

It's going to be slow as hell with the WHERE and GROUP BY clause if you have a lot of data.

[fenway]

SELECT COUNT(name) as name_count FROM literatureworks WHERE category = 'Fiction' GROUP BY genre

 

It's going to be slow as hell with the WHERE and GROUP BY clause if you have a lot of data.

Unless you have a covering index.