Writing a query...

[k2webfusion]

I'm trying to query a user field table, field6 specifically. This is a user profile field I setup for members to tell us what type of boat they have (brands like Cigarette, Wellcraft, Sonic, etc.). The query below tells me how many members have boats by excluding entries like none, na, no, n/a. (Otherwise the entry is the brand of boat.)

// How Many Total Boats

$totalboats =$DB_site->query_first('select count(*)AS totalboats
FROM userfield
WHERE field6 <> "n/a" AND field6 <> "n"');
$totalboats=$totalboats['totalboats'];

But now I need to look and count up how many are Cigarettes, Scarabs, Sonics, etc.

//Counting the brands

$cigbrand=$DB_site->query_first('select count(*)AS cigbrand
FROM userfield
WHERE field6 = "cigarette"');
$cigbrand=$cigbrand['cigbrand'];

This could be replicated for each brand I want to search for, then use PHP to do the math for me? There would seem to be an easier way to count all the boats, and also count by brand, eliminating the "none, na, n/a, still looking" entries some of our site members have entered. I'm lost on the syntax piece using the WHERE variables which may hold the key to getting all of this in one query. Any ideas?

[WingMan]

I think you need to look at the Aggregate 'Group by' function.

select count(*)AS Total, field6 As cigbrand
FROM userfield
WHERE field6 <> "n/a" AND field6 <> "n"
GROUP by field6
ORDER BY 1 DESC

This should list all avaliable brands and the total for each one, starting with the highest occurance.

Of course if you create a look-up table that contains only those brands that are acceptable then you could join the two tables and so you would not have to filter out the unknowns in the SQL as only the brands in the lookup table will be counted as 'Valid'

[k2webfusion]

Ahhhh, I see!! Thank you for the suggestion. :) I knew there had to be an easier way, but being new to SQL and working late again fogged my brain.

I've seen the JOIN command and will read up on it, as well as search the board for similar things. Thanks again.

[k2webfusion]

This query works fine in mySQL Admin panel and returns the right info. $boatbrands=$DB_site->query_first('select count(*)
AS countboats, field6 as boatbrands FROM userfield
WHERE field6 <> "n/a" AND field6 <> "n" AND field6 <> "none"
AND field6 <> "NA"
GROUP by field6
ORDER BY 1 DESC LIMIT 10');

I've called it from my template as $boatbrands, but it only shows the following: Array 16 Wellcraft

It should be showing
16 Wellcraft
15 Cigarette
12 Sonic
etc.

I've looked at PHP.NET and mysql.com and neither are very good at explaining the "array". The problem is probably handled by a WHILE loop statement which is next on my list of things to read up on. Wondering why the result "Array" is showing up and the query by itself, which works directly in the database, doesn't work from the php file and template?