Help with qeury

vBulletin Owner

#1 03-06-2007, 04:50 PM

Join Date: Oct 2006

Posts: 144

Help with qeury

I'm not good with mysql queries. I only know the basics plus a LEFT JOINS.
What I want to do is list all the categories from the user table which I can do but I want to output the number of rows called.
I had to add "empty" as the default because "WHERE hosting_category IS NOT NULL" wouldn't work.

PHP Code:

  $getlist = $db->query_read("SELECT hosting_category FROM " . TABLE_PREFIX . "user 
WHERE hosting_category != 'empty' 
GROUP BY hosting_category ");

--------------------
vHosting Pro
Manage you hosting requests easier

Xen Web Hosting
Offering ad-free hosting with features such as, cpanel, fantastico, PHP and MySQL support, and more

Quote |

SirAdrian

vBSetup Team
vBulletin Owner

#2 03-06-2007, 07:13 PM

SirAdrian will become famous soon enough

Join Date: Nov 2006

Posts: 42

Re: Help with qeury

So you want a list of all the categories and the number of categories? If it's the same table, this isn't really what the aggregate functions are for. You can just do a $db->num_rows($getlist) call to get the total.

Is there another table or something I am missing?

--------------------
Adrian Schneider

Quote |

harmor

vBulletin Owner

#3 03-06-2007, 07:16 PM

Join Date: Oct 2006

Posts: 144

Re: Help with qeury

I want to list the number of categories present.
Say that there are two users that have the category "Business". I want it to output "Business (2)".
There is no other table.

I'll try the num_rows() function to see if it works.

--------------------
vHosting Pro
Manage you hosting requests easier

Xen Web Hosting
Offering ad-free hosting with features such as, cpanel, fantastico, PHP and MySQL support, and more

Quote |

SirAdrian

vBSetup Team
vBulletin Owner

#4 03-06-2007, 08:42 PM

Join Date: Nov 2006

Posts: 42

Re: Help with qeury

Oh ok gotcha. You can do the processing in your while loop, or use this query:

Code:

SELECT hosting_category, count(userid) as total_users
FROM user
WHERE hosting_category != 'empty'
GROUP BY hosting_category

BTW you should either use an empty string (!= '') or NULL (is not null) for empty values, otherwise you are filling up your database with junk.

--------------------
Adrian Schneider

Quote |

harmor

vBulletin Owner

#5 03-06-2007, 08:58 PM

Join Date: Oct 2006

Posts: 144

Re: Help with qeury

I explained why I added the word "empty" to all users' columns. I never tried (!='') when the columns had no values. I'll clean the columns and try it.

Thank you. It works.

--------------------
vHosting Pro
Manage you hosting requests easier

Xen Web Hosting
Offering ad-free hosting with features such as, cpanel, fantastico, PHP and MySQL support, and more

Last edited by harmor; 03-06-2007 at 09:05 PM.

Quote |