How to concatenate string with OR-operator in php?

Question

I want to build a query based on the output from a for-loop. The query now looks like this:

$result = $pdo->query("SELECT {$fields}, ST_AsGeoJSON(geom, 5) AS geojson FROM government_table WHERE '".$gov_where_statement."' {$order}");

and the $gov_where_statement comes from the for-loop:

for ($i=0; $i<count($gov)-1; $i++){
    $gov_where_statement .="government='".$gov[$i]."' "&&" ";
}

for ($i=0; $i<count($gov); $i++){
  if( !next( $gov ) ) {
      $gov_where_statement .="government='".$gov[$i]."'"; 
   }
}

If there are three governments, I want the query to be interpreted as:

 $result = $pdo->query("SELECT {$fields}, ST_AsGeoJSON(geom, 5) AS geojson FROM government_table WHERE government='first' && government='second' && government='third' {$order}");

Problem arises in first part of the for-loop with the "&&". Putting a var_dump($gov_where_statement) after the for-loop returns 11government='third' where 11 comes from the for-loop with the &&-operator. If there would have been four governments it would look like: 111government='fourth'.

How can I produce government='first' && government='second' && government='third' so that the query understand the &&-operator?

First off, you need to add the `&&` in the string. RIght now, you're actually closing the string, adding `&&` and then another string. Also, you say you want `OR` but `&&` is an `AND`. Change it to: `$gov_where_statement .="government='".$gov[$i]."' OR ";`. However, you should really look into using prepared statements using placeholders instead of injecting the values directly into the query. — M. Eriksson, Oct 15 '19 at 08:54
@MagnusEriksson Yes, I made a last minute change to && but the correct was to use OR and as you wrote it. I however also had to change the query from '".$gov_where_statement."' to {$gov_where_statement}. That solved it. And I will do as suggested a prepared statement using placeholders to avoid SQL injections. Thank you very much! — agh, Oct 15 '19 at 09:49

Elias Soares · Answer 1 · 2019-10-15T09:32:07.810

0

You can use join() to solve it:

$gov_where_statement = [];

for ($i=0; $i<count($gov)-1; $i++){
    $gov_where_statement[] = "government='{$gov[i]}'";
}

$gov_where_statement = join(' OR ', $gov_where_statement);

Note that your entire code might be susceptible to SQL Injection Attack. Consider using PDO prepared statements instead.

edited Oct 15 '19 at 09:32

answered Oct 15 '19 at 09:14

Elias Soares

9,884
4
29
59

I do not recommend that anyone use this technique. – mickmackusa Oct 15 '19 at 09:25
Can you explain the reason? – Elias Soares Oct 15 '19 at 09:28
I recommend the what is in the duplicate page. – mickmackusa Oct 15 '19 at 09:31
Meh, this method is perfectly fine as long as you control the data being used so there are no security concerns. – 404 Oct 15 '19 at 10:03
Exactly. But since we don't know the source of givend data, I wrote about the sql injection concern. – Elias Soares Oct 15 '19 at 10:04

score 0 · Accepted Answer · answered Oct 15 '19 at 09:37

I would usually use prepared statement to prevent SQL injection. So I would separately build an array of placeholder and the substitution values. Also since you're looking to build the OR condition against single column and N different values, I think it's better to simply use the IN syntax:

<?php

// build a placeholder array [':gov1', ':gov2', ... ':govN']
$keys = array_map(function ($key) {
  return ':gov' . $key;
}, range(1, sizeof($gov)));

// build a value array [':gov1' => $gov[0], ':gov2' => $gov[1], ... ':govN' => $gov[n-1]]
$values = array_combine($keys, $gov);

// form the SQL statement with placeholder for prepare
// i.e. WHERE government IN (:gov1, :gov2, ... :govN)
$where_clause = !empty($gov) ? 'WHERE government IN (' . implode($keys, ', ') . ')' : '';

// prepare the statement
$stmt = $dbh->prepare("SELECT {$fields}, ST_AsGeoJSON(geom, 5) AS geojson FROM government_table {$where_clause} {$order}");

// execute the prepared statement with the values
$result = $stmt->execute($values);

How to concatenate string with OR-operator in php?

2 Answers2