mysql query issue

how to choose all

[whatson]

Ok, I am trying to write

SELECT * FROM pages WHERE category = ALL ORDER BY name

So this issue is category = ALL. How do I make a query that result in all the categories being displayed?

I tried:
SELECT * FROM pages WHERE category='' ORDER BY name
which obviously didnt work.

So I want to display all rows from the column category. And yes, I need the WHERE code in there.

[LifeinAsia]

So I want to display all rows from the column category. And yes, I need the WHERE code in there.

If you want to display all rows regardless of the values of the Category field, you do NOT need the WHERE clause.

If you want to show all the rows except where the Category field has no value, then use:
WHERE Category IS NOT NULL

[topr8]

do you want to return each category only once?

if so you need a group by clause.

SELECT category FROM pages GROUP BY category, ORDER BY category

[cffrost2]

I think the confusion here is the need to display all rows from the column "category". The problem with that statement is every row in the "pages" table has a field for a category value. So technically, you would display all rows in the pages table regardless of the column "category" because the returned results would include the "category" values from each row in the "pages" table.

Does that make sense? My question to you is, are you looking to only display the category values from each row in the pages table without duplicates? If so, topr8 is pretty much on point.

[whatson]

So if you have a drop down box for filtering results, there would be a variable, say $category.
The qry would be
SELECT * FROM pages WHERE category='$category' ORDER BY name

So if someone wants to view all the pages for all the categories, what should the value of $category be?

[lostdreamer]

if you want to view all the pages for all the categories, you should not have a WHERE clause there ;)

if(isSet($category) && $category != "")
  $query = "SELECT * FROM pages WHERE category='". $category ."' ORDER BY name";
else 
  $query = "SELECT * FROM pages ORDER BY name";

Something like that.

What you **could** do, but note this is NOT best practise:

$query = "SELECT * FROM pages WHERE category LIKE '". $category ."' ORDER BY name";

With LIKE instead of WHERE you can do wildcard searches by using "%" so " category LIKE '%' " gives you all results

[whatson]

I went with the if statement. I just thought there would be an easier way.
The other option I considered was:
$category = 'WHERE category = $category', and add that variable in.

[LifeinAsia]

And, of course, the usual caveats apply regarding protection from SQL injection attacks...

You don't want someone posting to your page with a category of something like "test; [add malicious statements here]"