How to do multiple queries to SQL

Question

I am trying to create a table and , insert some values in database with help of PHP script. While inserting only 1 row , it works fine. An when I try to enter more number of rows , It gives error;

I need to write whole insert statement for every query as I am using an online excel to SQL query converter.

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');
include_once("connect_to_Mysql.php");
$sqlCommand = "CREATE TABLE book (
Book_Index INT,
Book_name VARCHAR(38) CHARACTER SET utf8,
author_name VARCHAR(5) CHARACTER SET utf8,
Publisher VARCHAR(5) CHARACTER SET utf8,
Category INT,
Subcategory VARCHAR(21) CHARACTER SET utf8,
Price INT)";

$query = mysqli_query($db_conx,$sqlCommand) or die(mysqli_error($db_conx));
echo "<h3>Success creating book table</h3>";

$sqlCommand = "
INSERT INTO book VALUES (1,'Concepts of Physics Volume I','hc verma','bharti bhavan','jee','physics',635,'english');
 INSERT INTO book VALUES (2,'PLANE TRIGONOMETRY Part-1','sl loni','arihant publication','jee','math',95,'english');
 INSERT INTO book VALUES (3,'VIT (VELLORE) EDGE SOLVED PAPERS & 10 MOCK        TESTS (2007-2015)','Arihant Experts','arihant publication','vit','all in one',345,'english');
 INSERT INTO book VALUES (4,'A Textbook of Algebra','sk goyal','arihant publication','jee','math',425,'english');
INSERT INTO book VALUES (5,'New Pattern IIT JEE PHYSICS','dc pandey','arihant publication','jee','physics',665,'english');
INSERT INTO book VALUES (6,'Objective Approach to Mathematics','amit m aggrawal','arihant publication','jee','math',710,'english');
INSERT INTO book VALUES (7,'A Complete Success Package for','Experts Compilation','arihant publication','bitsat','all in one',775,'english');
INSERT INTO book VALUES (8,'15 Years'' Solved Papers for AMU Engineering     Entrance Exam','arihant Experts','arihant publication','AMU','all in one',325,'english');
INSERT INTO book VALUES (9,'Solved Papers & 5 Mock Tests for BVP Engineering','arihant Experts','arihant publication','BVP','all in one',365,'english');

";
$query = mysqli_query($db_conx,$sqlCommand) or die(mysqli_error($db_conx));
echo "<h3>Success populating the pages table with data</h3>";

?>

OUTPUT

Successful database connection, happy coding!!!
Success creating book table
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 'INSERT INTO book VALUES (2,'PLANE TRIGONOMETRY Part-1','sl loni','arihant public' at line 2

change your syntax to something more like that found in this prior question http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql essentially `insert into book values (field1, field2, field3), (field1,field2, field3)...` each new record is represented by a new "set" `(),` last set containing a `;` instead of `,` and the field list before the values I believe needs to be defined. Note: this syntax varies by RDBMS. So this is a mySQL link — xQbert, Aug 19 '15 at 18:40
Take advantage of multi-query http://php.net/manual/en/mysqli.multi-query.php — Funk Forty Niner, Aug 19 '15 at 18:43
you don't. mysqli doesn't allow multiple queries in a single query() call for security reasons. either use the multi-value extended syntax that mysql offers, or run multiple query() calls. — Marc B, Aug 19 '15 at 18:43
@xQbert Yeah, that works fine... but I have my database in excel. And I am using an online excel to sql query converter. — tarun14110, Aug 19 '15 at 18:44
thing is; are the codes for all the INSERTs being generated by that converter? That...is the question. — Funk Forty Niner, Aug 19 '15 at 18:49
@tarun14110... so you're saying the driver which converts the mySQL syntax to Excel doesn't support multi line insert statements? A) Find a new driver, B) choose a different Database which supports this method c) use multiple insert statements. if you're locked in to Excel and driver, C is your only choice. You may be able to do what Fred -ii suggests, I've not tried it. ***Excel as a back end database when mySQL is tagged is not something I would have guessed.*** — xQbert, Aug 19 '15 at 18:54

score 2 · Accepted Answer · edited Aug 19 '15 at 19:21

2

Use mysqli_multi_query instead of mysqli_query, that should do it.

$query = mysqli_multi_query($db_conx,$sqlCommand) or die(mysqli_error($db_conx));

http://php.net/manual/en/mysqli.multi-query.php

edited Aug 19 '15 at 19:21

Jay Blanchard

34,243
16
77
119

answered Aug 19 '15 at 18:43

mrcheshire

525
2
8

actually, that alone won't do it. The queries would need to be on individual lines. `$query = "SELECT CURRENT_USER();"; $query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";` as per the manual example. http://php.net/manual/en/mysqli.multi-query.php – Funk Forty Niner Aug 19 '15 at 18:46
@Fred-ii- semicolons are the query separators, newlines are irrelevant. – Barmar Aug 19 '15 at 19:09
@Barmar Alrighty. However, wouldn't the OP have to add spaces before their existing INSERT? Wouldn't that throw a syntax error? Plus, OP is no longer responding, nor do I see a green tick next to the answer here (yet). Edit: OP accepted it. – Funk Forty Niner Aug 19 '15 at 19:25
mysqli_multi_query() worked smoothly. Thanks to all – tarun14110 Aug 19 '15 at 19:28
1

@Fred-ii- No, you don't need space after semicolon. – Barmar Aug 19 '15 at 19:31

Danyal Sandeelo · Answer 2 · 2015-08-20T07:05:26.553

An easier approach could be to split the string with ; and iterate of each query in the array. Execute all of them one by one..

$sqlCommandArray[]=explode(";",$sqlCommand);

for($i=0;$i<count($sqlCommandArray);$i++){
try{
  mysqli_query($db_conx,$sqlCommandArray[$i]);
} 
catch(Exception $ex){
  echo "there was an error in executing the queries";
}
}

This would do the job without any modifications.

 $query = mysqli_multi_query($db_conx,$sqlCommand);

How to do multiple queries to SQL

2 Answers2

Linked

Related