Multiple data grabs in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
thisismyurl
Forum Newbie
Posts: 15
Joined: Wed Dec 03, 2008 8:00 am

Multiple data grabs in MySQL

Post by thisismyurl »

Hi all, I'm curious if anybody around here can help me with a small MySQL problem. Actually, I simply don't know how to do something and I'm hoping some kind soul here knows ...

I have a web site game that I'm programming and right now I'm doing multiple MySQL grabs but I'm sure there must be a better way.

Code: Select all

 
$query_getpopulation = "SELECT COUNT(id) AS total FROM `c` WHERE v = $v AND age > 65;";
...
$senior = $row_getpopulation['total'];
 
$query_getpopulation = "SELECT COUNT(id) AS total FROM `c` WHERE v = $v AND age < 18;";
...
$child = $row_getpopulation['total'];
 
$query_getpopulation = "SELECT COUNT(id) AS total FROM `c` WHERE v = $v AND gender = 1;";
...
$male = $row_getpopulation['total'];
 
As you can see, what I'm trying to do is get the values for $senior,$child,$male from the same database with only slightly different values. To accomplish this, I'm making three separate calls to the database. Is there a way for me to get all three in a single call?
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Re: Multiple data grabs in MySQL

Post by dude81 »

Absolutely, you can do that by making aliases
say something like this

Code: Select all

 
select count(x.id) as senior,  count(y.id) as child, count(z.id) as male from c as x, c as y, c as z  where x.age>65 and y.age<18 and z.gender =1
 
I've not checked this on my local tables. Can you check and let us know.
User avatar
thisismyurl
Forum Newbie
Posts: 15
Joined: Wed Dec 03, 2008 8:00 am

Re: Multiple data grabs in MySQL

Post by thisismyurl »

Thanks so much! My mistake was that I've been trying to place the AS calls *with* the declarations, not after. I had to tweak it a little but this helped a lot (sometimes all it takes is a little push in the right direction right?). Thanks again.
Post Reply