CONCAT SQL Query in PHP

Question

This is my code but I am getting an SQL Syntax error;

$insert = "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area1."')";
$insert .= "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area2."')";
$insert .= "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area3."')";
$insert .= "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area4."')";
$insert .= "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area5."')";

What is the correct way to carry out this query?

Uueerdo · Accepted Answer · 2015-11-09 18:57:36Z

3

When inserting multiple tuples, this is the notation used.

$insert = "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '".$area1."')";
$insert .= ", (NULL, '".$area2."')";
$insert .= ", (NULL, '".$area3."')";
$insert .= ", (NULL, '".$area4."')";
$insert .= ", (NULL, '".$area5."')";
$insert .= ";";

Providing multiple INSERT clauses suggests separate queries; which could be acceptable depending on MySQL library, but each would need to be ; terminated.

That said, you really should be using parameterized queries. When prepared, their speed is usually comparable to this (outside of some larger bulk inserts).

edited Nov 9, 2015 at 18:57

answered Nov 9, 2015 at 18:53

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

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

Comments

Abdul Basit · Accepted Answer · 2015-11-09 18:59:08Z

0

You need to specify the column names only once and then values separated by commas. eg

INSERT INTO students (Name, Age) VALUES ('John',10), ('Bob',10), ('Jeremy',10);

So just replace "INSERT IGNORE INTO locations (location_id, location_name) VALUES " in all statements except the first one with a comma "," and also insert semi colon at the end.

answered Nov 9, 2015 at 18:59

Abdul Basit

3743 silver badges12 bronze badges

Comments

ihojose · Accepted Answer · 2015-11-09 19:04:06Z

0

USE This SQL Syntax (use ;):

$insert = "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '$area1');";
$insert .= "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '$area2');";
$insert .= "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '$area3');";
$insert .= "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '$area4');";

Or

$insert = "INSERT IGNORE INTO locations (location_id, location_name) VALUES (NULL, '$area1')";
$insert .= ", (NULL, '$area2')";
$insert .= ", (NULL, '$area3')";
$insert .= ", (NULL, '$area4');";

answered Nov 9, 2015 at 19:04

ihojose

3112 silver badges13 bronze badges

Collectives™ on Stack Overflow

CONCAT SQL Query in PHP

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related