SQL - syntax error

Question

can you see what is wrong on this query? I have been watching it for really long time and I can't see it.

ERROR: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1

$sql="SELECT country_name FROM countries WHERE country_id IN (";
    foreach($cartContentVsDatabase as $key => $val){
      $sql.= $key['country_id'].",";        
    }
    ")";

You're going to have a trailing `,` after the last country_id — Mark Baker, Sep 06 '16 at 14:36
Should use var_dump($sql); to see your query correctly and where your problem is. — Marc Giroux, Sep 06 '16 at 14:36
You'll have a trailing comma after the IN (...) list, you're not appending the last closing bracket to the string, you seem to be trying to use an array key as an array. Take your pick. — iainn, Sep 06 '16 at 14:37
You didn't concatenate the last part of the string, as @Jens said — The One and Only ChemistryBlob, Sep 06 '16 at 14:37
Don't edit your question to include the answer that worked. Instead, accept the answer that helped you below. — Siyual, Sep 06 '16 at 15:26

score 2 · Answer 1 · edited May 23 '17 at 12:08

Some issues:

You have a trailing comma in your country list,
countries should be quoted as strings,
you are accessing the values from the key instead of the value part of the array elements.
you have a dangling closing parenthesis, which does nothing.
You should not even inject country strings, as that makes your code vulnerable for code injection: use prepared statements.

Here is code you could use:

// first put the countries in an array
foreach($cartContentVsDatabase as $key => $val){
  $countries[] = $val['country_id'];  
}
// create a list of `?`: one for every country
$in = join(',', array_fill(0, count($countries), '?'));

// use that in the query
$sql="SELECT country_name FROM countries WHERE country_id IN ($in)";

// Prepare the statement (this is PDO syntax)
$statement = $pdo->prepare($select);

// Pass the countries as parameter values, and execute
$statement->execute($countries);

See this Q&A for more on prepared statements in the context of this in (...) clause.

Dave · Accepted Answer · 2016-09-06T14:46:59.307

1

try this, change ")"; to $sql.= ")";

    $array_count = count($cartContentVsDatabase);
    $temp_count = 0;
    $sql="SELECT country_name FROM countries WHERE country_id IN (";
    foreach($cartContentVsDatabase as $key => $val){
       $temp_count++;
      if($array_count < $temp_count)
      {
           $sql.= $val['country_id'];   
      }
      else
      {
          $sql.= $val['country_id'].",";   
      }

    }
    $sql.=  ")";

edited Sep 06 '16 at 14:46

answered Sep 06 '16 at 14:39

Dave

3,073
7
20
33

This solves 1 out of the ±6 problems the OP has in their code. This alone will not fix it. – trincot Sep 06 '16 at 14:42
There's still extra `,` in the end – u_mulder Sep 06 '16 at 14:43
And `$key` cannot be array. – u_mulder Sep 06 '16 at 14:45

score 0 · Answer 3 · answered Sep 06 '16 at 14:44

You could make your life a lot easier by

$sql= "SELECT country_name FROM countries WHERE country_id IN (".implode(",",array_column($cartContentVsDatabase,"country_id")). ")";

You can (and probably should) use a prepared query e.g. like the one below:

$sql= "SELECT country_name FROM countries WHERE country_id IN (".implode(",",array_fill(0,count($cartContentVsDatabase),"?")). ")";

and then bind the contents of $cartContentVsDatabase when you execute.

devpro · Answer 4 · 2016-09-06T14:56:06.987

In your code you are not concatenate ")"; properly at the end. you can also store data into array and than use implode() for comma separated values like:

Example:

<?php

$sql = "SELECT country_name FROM countries ";

$countries = array();
foreach($cartContentVsDatabase as $key => $val){
  $countries[] = $val['country_id'];  // store country id in your array
}

if(count($countries) > 0){
     $countrylist = implode("','",$countries); // implode all country list with comma.    
     $sql .= "WHERE country_id IN ('$countrylist')";
}    
echo $sql; // print your query.

?>

Still i don't know, $key['country_id'] is the correct one or not, i think this should be $val['country_id'].

SQL - syntax error

4 Answers4