SELECT statement with a wildcard

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

SELECT statement with a wildcard

Post by evilmonkey »

Hello. I'm trying to make a select statement based on user input. My dillema is this: there may or may not be user input for a particular variable. The user has the option to search (for example) by category, by username, by both, or by no parameters (lists all results). The way I figured I'd do it is this:

Code: Select all

<?php
if (isset($_GET['category'])) {
$category = $_GET['category']; }
else {
$category = //wildcard here, because i want results from all categories
}
if (isset($_GET['username'])) {
$username = $_GET['username']; }
else {
$username = //same dillema
}
//finally, the query
$query = "SELECT * FROM `table` WHERE `category` = $category AND `username`= $username AND /*more variables */";
?>
So if $_GET['category'] or $_GET['username'] are unset, I want MySQL to pull all the results. I tried both the * and the %, but to no avail. What is the wildcard symbol?

Thanks!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

" LIKE '%' " instead of " = '' "
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Hmm, and if there's a value? with LIKE 'value' work? What is LIKE anyway?
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

I'd build the query dynamically by adding where clauses as necessary. something like this:

Code: Select all

q = 'select * from table where 1=1'
if (?) q .= ' and col1=' . $val
.....
just be careful with your quotes... this is a place where sql injection attacks could occurr.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

SQL injection won't be a problem because the user isn't entering any data. It's all from drop down boxes...Hmm...now that I think about it, I'm using $_GET[]...maybe I should use $_POST[] instead?

EDIT: I just realized, that way will result in vary messy code for me...I'm validating user input instead, so something like if ($_GET['gender'] == "F" || $_GET['gender'] =="M") instead of just if (isset($_GET['gender'])). (just as an example)
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post by hedge »

Well you still need to watch for SQL injection... you can't assume that your script will necessarilly be called by the html you build.

It's trivial to grab the html and modify it to post to your script with values of my choosing. This can be done with a GET or a POST, POST really isn't any more secure than GET.
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

Hmm, I see your point...look above for my edit, do you thik that's sufficient security?
Post Reply