A quicker way to check multiple variations of variables?

Question

I am look for a quicker, cleaner way of writing these 'if' statements in PHP.

if($type == "deals") {
    if($city == "blank" && $category == "blank") {
        $arr = $mysqli->query("SELECT id, hide FROM deals");
    }
    if($city !== "blank" && $category == "blank") {
        $arr = $mysqli->query("SELECT id, hide FROM deals WHERE city=".$city."");
    }
    if($city == "blank" && $category !== "blank") {
        $arr = $mysqli->query("SELECT id, hide FROM deals WHERE category=".$category."");
    }
    if(!$city == "blank" && $category !== "blank") {
        $arr = $mysqli->query("SELECT id, hide FROM deals WHERE city=".$city." AND category=".$category."");
    }
} else { ... same thing with different querys ... }

Suggestions on better alternatives welcome!

Thanks

score 3 · Answer 1 · edited May 23 '17 at 11:50

3

I don't touch php for years. But in pseudo-code:

$whereParam = array();
$sql = array();

$sql[] = "SELECT id, hide FROM deals";

if($city !== "blank") $whereParam[] = "city=".$city;
if($category !== "blank") $whereParam [] = "category=".$category;

$where = implode(' AND ', $whereParm);

if($where !== '') $sql[] = $where;

$arr = $mysqli->query(implode(' WHERE ', $sql));

Improve depend on your other code.

EDIT: I soggest to look at ORMS

edited May 23 '17 at 11:50

Community

1
1

answered May 03 '14 at 12:59

Anton Bessonov

9,208
3
35
38

is `implode` recursive? I'm not following your code. – Filip Haglund May 03 '14 at 13:00
2

very nice, but prefer ternary if statements over single line if statements even if they end `:'';` – andrew May 03 '14 at 13:04
@andrew ternary if statement /can/ lead to not readable code and difficult to find errors. I prefer full if statements: if () {}, but I hate else's and nested if's. – Anton Bessonov May 03 '14 at 13:07
@Filip unfortunately, the "most readable code" it's very relative. Some of people say scala is readable. I'm learning scala. It's NOT readable :( – Anton Bessonov May 03 '14 at 13:12
yeah, unfortunatelly. But in this case i think either if () {} or ternary operator. – Filip Haglund May 03 '14 at 15:23

jackfrankland · Accepted Answer · 2014-05-08T20:16:07.647

1

There is a neat way of achieving the same thing, which provides you with guards against SQL injection, and requires less code!

$stmt = $mysqli->prepare("SELECT id, hide FROM deals WHERE id>? AND (city=? OR 1=?) AND (category=? OR 1=?)");
$cityIsBlank = ($city == "blank");
$categoryIsBlank = ($city == "blank");
$stmt->bindParam("isisi", $last_id, $city, $cityIsBlank, $category, $categoryIsBlank));
$stmt->execute();

If either parameter is "blank", then 1=1 evaluates to true, and the database optimiser will basically not include the where clause in the lookup.

You can read more about how to use prepared statements, and retrieving the values here.

edited May 08 '14 at 20:16

answered May 03 '14 at 17:24

jackfrankland

2,052
1
13
11

I tried this and got Fatal error: Cannot pass parameter 4 by reference. I added in another where variable before city. WHERE id > ? AND ... Added the example to my question – ChristopherStrydom May 05 '14 at 13:30
@ChristopherStrydom Sorry about that, didn't test the code myself. The error was caused because bindParam expects a reference to a variable, and not a value itself. The edits I've made should now work for you. – jackfrankland May 05 '14 at 14:21
Thanks for the help. The code you have as is still doesn't work as it doesn't like the setting of the variables to be inside the statement. I looked at your previous edits and saw that you moved them out at one point. Tried that and it works. Have updated my question with the edits and will make this the answer. Thanks again – ChristopherStrydom May 08 '14 at 19:02
@ChristopherStrydom Great. I'll edit it back to how I had it. – jackfrankland May 08 '14 at 20:13

Filip Haglund · Answer 3 · 2014-05-03T12:56:17.127

0

Try building the query dynamically.

$sql = "SELECT id, hide FROM deals";
$wheres = array("city" => $city, "category" => $category);

if (count($wheres) > 0) {
    $sql .= " WHERE " . implode(" AND ", $wheres);
} 

$arr = $mysqli->query($sql);

Make sure you escape your variables properly to avoid SQL injections. You could use this:

$city = $mysqli->real_escape_string($city);

for each of the variables.

edited May 03 '14 at 12:56

answered May 03 '14 at 12:49

Filip Haglund

13,919
13
64
113

A switch statement only takes one variable doesn't it? – ChristopherStrydom May 03 '14 at 12:54
they could be nested, but I figures this solution would be better for you – Filip Haglund May 03 '14 at 12:56

A quicker way to check multiple variations of variables?

3 Answers3