MYSQL Syntax error in php but sql is valid

Question

I'm trying to start a transaction is mysql and insert data into the database. The database source sql can be found on github here. Here is the error:

Error: START TRANSACTION; INSERT INTO Books(Title, PublicationDate, PurchaseDate, Description, LocationID, GenreID) VALUES('Simple Genius', '2008-4-1','2009-5-7','','Hardbook Library','Fiction'); SET @bookid = LAST_INSERT_ID(); INSERT INTO BookAuthors(FirstName, MiddleName, LastName) VALUES('David', '', 'Baldacci'); SET @authorid = LAST_INSERT_ID(); INSERT INTO AuthorsInBooks(AuthorID, BookID) VALUES(@authorid, @bookid); COMMIT; 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 Books(Title, PublicationDate, PurchaseDate, Description, LocationID,' at line 3

Near 'INSERT INTO Books(Title, PublicationDate, PurchaseDate, Description, LocationID,' doesn't make sense to me because it is missing GenreID after LocationID. Am i missing something? When I copy and paste this code into phpmyadmin it works fine. My php version is 5.4.

Here is php code:

$sql = "
START TRANSACTION;

INSERT INTO Books(Title, PublicationDate, PurchaseDate, Description, LocationID, GenreID)
VALUES('".$Title."', '".$YearWritten."','".$YearPurchased."','".$Description."','".$Location."','".$Genre."');

SET @bookid =  LAST_INSERT_ID();

INSERT INTO BookAuthors(FirstName, MiddleName, LastName)
VALUES('".$AuthFirstName."', '".$AuthMiddleName."', '".$AuthLastName."');

SET @authorid =  LAST_INSERT_ID();

INSERT INTO AuthorsInBooks(AuthorID, BookID)
VALUES(@authorid, @bookid);

COMMIT;
";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);

I was told to use this. Is this not effective enough? @user2864740 mysqli_real_escape_string — Jacob Wilson
– Jacob Wilson, Commented Jan 15, 2016 at 19:00
@JacobWilson: You may want to read stackoverflow.com/q/5741187, which is equally valid for mysqli. — eggyal
– eggyal, Commented Jan 15, 2016 at 19:07

jeroen · Accepted Answer · 2016-01-15 18:23:01Z

2

mysqli_query() can only execute 1 query, if you want to execute multiple queries, you need:

if (mysqli_multi_query($conn, $sql)) {

answered Jan 15, 2016 at 18:23

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

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

7 Comments

eggyal Over a year ago

But rather than doing that, just use API calls to manage transactions and send each command separately... and, of course, parameterise those variables!

Jacob Wilson Over a year ago

Worked perfectly. Thank you so much!

jeroen Over a year ago

@eggyal I completely agree.

Jacob Wilson Over a year ago

Can I see an example of what you mean @eggyal ?

Jacob Wilson Over a year ago

Care to explain @user2864740

|

eggyal · Accepted Answer · 2016-01-15 18:50:15Z

In response to your comment "Can I see an example of what you mean @eggyal ?":

// mysqli provides API calls for managing transactions
mysqli_autocommit($conn, false);

// parameterise variables - NEVER concatenate them into dynamic SQL
$insert_book = mysqli_prepare($conn, '
  INSERT INTO Books
    (Title, PublicationDate, PurchaseDate, Description, LocationID, GenreID)
  VALUES
    (?, ?, ?, ?, ?, ?)
');

// bind the variables that (will) hold the actual values
mysqli_stmt_bind_param(
  $insert_book,
  'siisss', // string, integer, integer, string, string, string
  $Title, $YearWritten, $YearPurchased, $Description, $Location, $Genre
);

// execute the statement (you can change the values of some variables and
// execute repeatedly without repreparing, if so desired - much faster)
mysqli_stmt_execute($insert_book);

// mysqli provides API calls for obtaining generated ids of inserted records
$book_id = mysqli_insert_id($conn);

// ... etc ...

// use the API call to commit your transaction
mysqli_commit($conn);

// tidy up
mysqli_stmt_close($insert_book);

Note that I've not included above any error detection/handling, which you'd certainly want to include in any real-world code.

Collectives™ on Stack Overflow

MYSQL Syntax error in php but sql is valid

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related