PHP MYSQL syntax

Question

I'm having a little trouble with my MYSQL query

I have a DB full of products and I have a dropdown menu which lets a user select what time of day they'd like to get get results for :-

Dropdown
- Breakfast
- Lunch
- Evening
- Anytime

At the moment my statement is

SELECT * from DEALS WHERE timeofday='post data from form';

Now this works fine, but with the option for 'Anytime' I'd like the query to be able to search for results of all/any of the above.

I was thinking of perhaps doing an IF statement which fires off 2 separate queries, one which says if the $_POST['timeofday'] == 'Anytime' then fire off

SELECT * from DEALS where timeofday='Breakfast' 
OR timeofday='Lunch' OR timeofday='Evening';

otherwise just do the normal query, although wondered if it was possible to do this in just one statement.

Kind regards

How do you know? can you see my code? I may have filtered all of my vars already ;P. — Adam
– Adam, Commented Jul 8, 2012 at 11:20
You could improve your second query by using timeofday IN(...). — flec
– flec, Commented Jul 8, 2012 at 11:24

Sergey Eremin · Accepted Answer · 2012-07-08 11:41:43Z

2

$query = 'SELECT * from DEALS';
if ($_POST['timeofday'] != 'Anytime') {
    $query .= ' WHERE timeofday="' . $_POST['timeofday'] . '"';
}

As DCoder mentioned, this approach is vulnerable to sql injection... You should check/sanitize the input or use prepared statements. In this case where there is a predefined set of values you can:

$knownTimesOfDay = array('Breakfast', 'Lunch', 'Evening', 'Anytime');
if (!in_array($_POST['timeofday'])) {
    die('Unsuppotred time of day... Did it really come from the form?');
}
$query = 'SELECT * from DEALS';
if ($_POST['timeofday'] != 'Anytime') {
    $query .= ' WHERE timeofday="' . $_POST['timeofday'] . '"';
}

edited Jul 8, 2012 at 11:41

answered Jul 8, 2012 at 11:15

Sergey Eremin

11.1k2 gold badges43 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adam Over a year ago

That was going to be my solution, is this the best way to achieve this? I wanted to see if there was a more elegant way of achieving it :(

user1233508 Over a year ago

This is vulnerable to SQL injection. I can see OP is familiar with it, but you should definitely mention it for future readers.

Gerald Versluis · Accepted Answer · 2012-07-08 11:16:22Z

0

Don't think it can be done in one statement.

You are going to have to use an if statement anyhow.

answered Jul 8, 2012 at 11:16

Gerald Versluis

34.4k7 gold badges88 silver badges110 bronze badges

1 Comment

Adam Over a year ago

Okay cool. Thanks for your feedback. I am not very good at SQL so thought there would be a better way.

Razvan · Accepted Answer · 2012-07-08 11:16:45Z

0

if these are the only 3 possible values for timeofday,then you can have an if in the php script like this:

if($_POST['timeofday'] != 'Anytime' )
    sql .= "where timeofday='".$_POST['timeofday']."'";

answered Jul 8, 2012 at 11:16

Razvan

10.2k6 gold badges41 silver badges51 bronze badges

Comments

Franco · Accepted Answer · 2012-07-08 11:51:33Z

0

This could turn out to be negative depending on the items you have in the table, but you could use:

SELECT * from DEALS where timeofday LIKE '%{$post_data}%'

It would return all the results from timeofday if $post_data was an empty string.

answered Jul 8, 2012 at 11:51

Franco

1719 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL syntax

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related