I done a search and the closest was a tut on COUNT() but didn't get the results I am trying to get.
I created a sample library for producers and all the data is in a table called samples and they are filtered by a category name IE: lbeat (Loops beat) and obeat (One shot beat). Now in the page that displays all the categories I wish to place the amount in each category but I keep on getting the TOTAL records and not for each category.
When I filter the count to the category field I get a 0 count.
The COUNT() tut just gave me total records and not total for each category.
How could I get the result I am after?
Ta muchly
0
Count
Started by _*Creative Insanity_*, Mar 04 2007 06:08 PM
12 replies to this topic
#2
-
- Members
-
- 970 posts
P2L Jedi
- Gender:Male
- Location:A strange world where water falls out of the sky... for no reason.
- Interests:Graphic Design, Coding, Splinter Cell, Cats
Posted 04 March 2007 - 07:47 PM
Could you show us your code?
The COUNT() method basically works like this.
And you can then use the WHERE clause to do your filtering as usual.
The COUNT() method basically works like this.
SELECT COUNT(*) FROM `table`
And you can then use the WHERE clause to do your filtering as usual.
SELECT COUNT(*) FROM `table` WHERE `column` = 'value'
#3 _*Creative Insanity_*
-
- Guests
Posted 04 March 2007 - 10:26 PM
Done that and that gives the TOTAL for the table and not for each category.
When you add the filtering you get a zero count.
What I mean here is that there may be 100 entries and 25 could be one category 10 another and so on. This is what I am after so when echoed out I will get something like.
Loops - Beat 25 One Shot - Beat 10
and so on.
But thanks for the reply
When you add the filtering you get a zero count.
What I mean here is that there may be 100 entries and 25 could be one category 10 another and so on. This is what I am after so when echoed out I will get something like.
Loops - Beat 25 One Shot - Beat 10
and so on.
But thanks for the reply
Edited by Creative Insanity, 04 March 2007 - 10:28 PM.
#4
-
- Members
-
- 970 posts
P2L Jedi
- Gender:Male
- Location:A strange world where water falls out of the sky... for no reason.
- Interests:Graphic Design, Coding, Splinter Cell, Cats
Posted 05 March 2007 - 10:26 AM
I don't understand how it wouldn't be working. You should be able to just use the WHERE clause and do like, "WHERE `category` = 'lbeat'".
You're going to have to show us your SQL and PHP, I have a feeling you may have a syntax error that isn't letting the query do as you think it is (happens to me all the time, lol), or you may not be using the right function for grabbing the result or something (hey, had it happen to me alot too
).
You're going to have to show us your SQL and PHP, I have a feeling you may have a syntax error that isn't letting the query do as you think it is (happens to me all the time, lol), or you may not be using the right function for grabbing the result or something (hey, had it happen to me alot too
).
#5 _*Creative Insanity_*
-
- Guests
Posted 05 March 2007 - 02:14 PM
Ok.. here is the table..
It is the cat field I want to do the count on and I was doing the filtering on the cat field like:
Oh better show how the database entries are:
CREATE TABLE `samples` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, `preview` varchar(150) NOT NULL, `cat` varchar(30) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
It is the cat field I want to do the count on and I was doing the filtering on the cat field like:
SELECT count(*) from samples where cat = cat
Oh better show how the database entries are:
Attached Files
-
sql.png 9.69K
48 downloads
Edited by Creative Insanity, 05 March 2007 - 02:19 PM.
#6
-
- Members
-
- 970 posts
P2L Jedi
- Gender:Male
- Location:A strange world where water falls out of the sky... for no reason.
- Interests:Graphic Design, Coding, Splinter Cell, Cats
Posted 05 March 2007 - 05:13 PM
SELECT count(*) from samples where cat = catLOL
I'm sorry, but I really gotta laugh at that.
This is selecting all of your data, as it looking for entries where the column is equal to itself.
Basically you are saying this.
SELECT COUNT(*) FROM samples WHERE 1=1
Always gonna evaluate true buddy.
You need it like this.
SELECT COUNT(*) FROM `samples` WHERE `cat` = 'obeat'
Notice the quotes that signify the string 'obeat'; otherwise it will evaluate it as the value of a column, and try to look for something in a column called obeat.
Thank you for the quick laugh.
#7 _*Creative Insanity_*
-
- Guests
Posted 05 March 2007 - 05:43 PM
Quote
Thank you for the quick laugh.
Still learning and trying to get to grips with sql.
yeah but that would make them all have the same amount like this:
Quote
Loops - Ambiences 7
Loops - Atmospheres 7
Loops - Beats 7
Loops - Effects 7
Loops - Instruments 7
Loops - Sounds 7
Loops - Stabs 7
Loops - Synths 7
Loops - Vocal 7
Loops - Voices 7
One Shot - Ambiences 7
One Shot - Atmospheres 7
One Shot - Beats 7
One Shot - Effects 7
One Shot - Instruments 7
One Shot - Sounds 7
One Shot - Stabs 7
One Shot - Synths 7
One Shot - Vocal 7
One Shot - Voices 7
Loops - Atmospheres 7
Loops - Beats 7
Loops - Effects 7
Loops - Instruments 7
Loops - Sounds 7
Loops - Stabs 7
Loops - Synths 7
Loops - Vocal 7
Loops - Voices 7
One Shot - Ambiences 7
One Shot - Atmospheres 7
One Shot - Beats 7
One Shot - Effects 7
One Shot - Instruments 7
One Shot - Sounds 7
One Shot - Stabs 7
One Shot - Synths 7
One Shot - Vocal 7
One Shot - Voices 7
Just to give you another laugh LOL.. I just tried this and get a 0 count. Doh!
WHERE samples.cat ='oambie' AND samples.cat = 'obeat' AND samples.cat = 'lbeat'
Edited by Creative Insanity, 05 March 2007 - 05:55 PM.
#8
-
- P2L Staff
-
- 1,850 posts
P2L Staff
- Gender:Male
- Location:Saskatoon, Saskatchewan
- Interests:Guitar, Programming, Storm Chasing, Games (Designing and playing), Hockey, Photography
Posted 05 March 2007 - 06:40 PM
I may be wrong but I think with that sql query it is looking for all categories that has each of those values. So if none of the samples.cat = oambie, obeat or lbeat it won't show because there are none giving you a count of 0.
Try using OR instead of AND.
dEcade
Try using OR instead of AND.
dEcade
Edited by dEcade, 05 March 2007 - 06:41 PM.
#9 _*Creative Insanity_*
-
- Guests
Posted 05 March 2007 - 07:04 PM
Ok decade lbeat has one and obeat has 7. But get 0, I will try OR as you suggested but I think that will give me the wrong result from what I am looking for.
As I suspected decade.. they all say 8 with OR. But thanks for the tip anyway.
As I suspected decade.. they all say 8 with OR. But thanks for the tip anyway.
Edited by Creative Insanity, 05 March 2007 - 07:06 PM.
#10
-
- Members
-
- 970 posts
P2L Jedi
- Gender:Male
- Location:A strange world where water falls out of the sky... for no reason.
- Interests:Graphic Design, Coding, Splinter Cell, Cats
Posted 06 March 2007 - 04:21 PM
What is the point in that?
I thought you wanted a count of each category, such as how many have the category of 'obeat' and such?
Look, you need to use seperate queries man...
Just use logic with SQL.
In your previous query.
You are telling SQL this.
'Select the count of the rows, from the table samples, where the category is equal to 'oambie' and obeat and lbeat at the same time.'
Since you can't have a value equal to three different strings at the same time, that of course won't work.
Using the same thing with OR, would be saying this.
'Select the count of the rows, from the table samples, where the category is equal to either 'oambie' or obeat or lbeat.'
Which is the same as selecting all the rows pretty much.
Logic.
I thought you wanted a count of each category, such as how many have the category of 'obeat' and such?
Look, you need to use seperate queries man...
<?php
// Connection assumed
$lbeat_count = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM `samples` WHERE `cat` = 'lbeat'")) or die(mysql_error());
$obeat_count = mysql_fetch_row(mysql_query("SELECT COUNT(*) FROM `samples` WHERE `cat` = 'obeat'")) or die(mysql_error());
echo 'lbeats: '.$lbeat_count[0].' | obeats: '.$obeat_count[0];
?>
Just use logic with SQL.
In your previous query.
SELECT COUNT(*) FROM samples WHERE samples.cat ='oambie' AND samples.cat = 'obeat' AND samples.cat = 'lbeat'
You are telling SQL this.
'Select the count of the rows, from the table samples, where the category is equal to 'oambie' and obeat and lbeat at the same time.'
Since you can't have a value equal to three different strings at the same time, that of course won't work.
Using the same thing with OR, would be saying this.
'Select the count of the rows, from the table samples, where the category is equal to either 'oambie' or obeat or lbeat.'
Which is the same as selecting all the rows pretty much.
Logic.
#11 _*Creative Insanity_*
-
- Guests
Posted 06 March 2007 - 04:31 PM
Hey Demonslay, you are a legend mate.. Just getting to grips with all this.. now it is starting to make sence.
Ta heaps! I will let you know how I get on.
I am not as young as many here.. and for an old bugger (over 50) I still like to learn stuff.
Magic this is, pure magic.
Ta heaps! I will let you know how I get on.
I am not as young as many here.. and for an old bugger (over 50) I still like to learn stuff.
Magic this is, pure magic.
#12 _*Creative Insanity_*
-
- Guests
Posted 09 March 2007 - 01:48 PM
I want to say thanks heaps to Demonslay for the help with the project and I am happy to say it is all sussed.
As you can see by the image below (I used an image because it is a login app) it all looks nice and dandy.
Thanks once again Demonslay, you are a legend for helping this old fart LOL
As you can see by the image below (I used an image because it is a login app) it all looks nice and dandy.
Thanks once again Demonslay, you are a legend for helping this old fart LOL
Attached Files
-
done.png 62.04K
40 downloads
#13
-
- Members
-
- 970 posts
P2L Jedi
- Gender:Male
- Location:A strange world where water falls out of the sky... for no reason.
- Interests:Graphic Design, Coding, Splinter Cell, Cats
Posted 09 March 2007 - 04:34 PM
No problem.
Good luck with your site.
Good luck with your site.
1 user(s) are reading this topic
0 members, 1 guests, 0 anonymous users
