Welcome to WebmasterWorld Guest from 54.145.209.34

Forum Moderators: coopster & jatar k

mysql query issue

how to choose all

   
9:41 pm on Sep 12, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
9:52 pm on Sep 12, 2012 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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
10:11 pm on Sep 12, 2012 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
10:33 pm on Sep 12, 2012 (gmt 0)

5+ Year Member



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.
12:30 am on Sep 13, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
10:24 am on Sep 13, 2012 (gmt 0)



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
7:54 pm on Sep 13, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
8:45 pm on Sep 13, 2012 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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]"
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month