12-26-2005, 03:57 PM #1
long shot: php mysql column represent query?
- Member Since
- Jul 25, 2004
- Gaithersburg, MD
- Mac Pro 2.6ghz Quad Xeon, 23" Cinema
Here's an example:
we have two tables, one is called job_categories which has two columns:
job_category_id (integer, primary key, auto increment)
job_category_title (title of the job category)
second table is titled jobs, which is a list of jobs. One of the columns is job_category_id to correspond to the category it belongs in. Say we want to get a query that will list each job category title individually, and then how many jobs exist in the jobs table with that category_id. i.e
Human Resources (8)
Information Technology (0)
Is there any way to do that query without having to do a count query individually on each row returned in some sort of a loop? I don't have any of my SQL books here (I'm at home with the family for Christmas, brought the powerbook with me).
I know it's a long shot asking here, but if anyone can offer a suggestion or point me to a better forum please let me know!
01-22-2006, 06:31 PM #2
- Member Since
- Jun 25, 2005
- On the road
- 2011 MBP, i7, 16GB RAM, MBP 2.16Ghz Core Duo, 2GB ram, Dual 867Mhz MDD, 1.75GB ram, ATI 9800 Pro vid
You've probably resolved this by now. I'm not even sure exactly what you want, but...
select job_categories.job_category_title, jobs.job, count(*) mycount
from job_categories, jobs
where job_categories.job_category_id = jobs.job_category_id
group by job_categories.job_category_title, jobs.job;
If you could perform a loop through job_categories and use the id to find the jobs and count the result. I think maybe that is what you wanted to do, but I don't know how to do that in PHP.
Note: The syntax I used above will work in Oracle which I'm very familiar with, but you may need to use AS before mycount.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By conor in forum OS X - Operating SystemReplies: 5Last Post: 06-11-2009, 07:44 PM
By blinkdude182 in forum Web Design and HostingReplies: 4Last Post: 02-08-2009, 01:52 AM
By richardmojo in forum Web Design and HostingReplies: 2Last Post: 05-09-2005, 10:05 PM
By design2 in forum Web Design and HostingReplies: 3Last Post: 08-07-2004, 06:22 PM
By Emrys in forum OS X - Development and DarwinReplies: 2Last Post: 11-14-2003, 01:30 AM