homepage Welcome to WebmasterWorld Guest from 54.205.254.108
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mysql query issue
how to choose all
whatson




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

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




msg:4494492
 9:52 pm on Sep 12, 2012 (gmt 0)

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




msg:4494496
 10:11 pm on Sep 12, 2012 (gmt 0)

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




msg:4494498
 10:33 pm on Sep 12, 2012 (gmt 0)

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




msg:4494527
 12:30 am on Sep 13, 2012 (gmt 0)

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




msg:4494669
 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

whatson




msg:4494863
 7:54 pm on Sep 13, 2012 (gmt 0)

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




msg:4494882
 8:45 pm on Sep 13, 2012 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved