Unique Row Count Across Columns

[webguy6]

Hi all,

I've been learning PHP and SQL slowly over the past couple of weeks for a project (so please be gentle!).

I have a database, MEMBERS, as follows:

ID | Name       | Job Title     | Current Employer | State | Manager
1  | Joe Smith  | Sample Title  | Big Company      | CA    | No

There are several thousand entries and I wish to count them. I have a GET search page where a person can search for any of the fields, eg. ID, Name, Title, etc. It works as intended. So if I search for a job title, it returns all IDs, names, etc associated with that job title.

However, I also want to show a Results Summary somewhere on the results page that also counts the number of unique results.

Eg. I search for Developer:

Job Title:
Software Developer (835)
I.T. Developer (500)
Developer (233)
...

Current Employer:
Friendly Company (200)
Analyze This (145)
...

State:
CA (456)
TX (340)
...

Manager:
Yes (200)
No (2500)

The best example I can think of is SimplyHired.com where you enter a search term and it summarizes all the fields.

Further, for the sake of narrowing the results, I want to click on each link and then limit the results based on that condition. Eg. If I click on Friendly Company (200), it will then update the search results page with entries only applicable to that company, and all other row counts for each column heading would adjust accordingly. Eg. find and count unique job titles in Friendly Company, states, etc.

I understand that this might be an onerous request so if someone could kindly point me in the right direction, I'd be most appreciative. Thanks!

[blocblue]

Hi,

You could do something like:

SELECT `name`, COUNT(`name`) FROM `member` GROUP BY `name`;

And repeat this for each column. Or you could combine them all into a single query, by using a subquery for each column.

R.