SQL error , I tried to insert data in 3 table because other 2 is bridge

Question

Error:

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

is there any better way ?

$insertSQL = sprintf("INSERT INTO booking (book_id,book_start_rent,book_end_rent,book_total,staff_id) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['book_id'], "text"),
                       GetSQLValueString($_POST['book_start_rent'], "date"),
                       GetSQLValueString($_POST['book_end_rent'], "date"),
                       GetSQLValueString($_POST['book_total'], "double"),
                       GetSQLValueString($_POST["state2"], "text")

                       /"INSERT INTO car_booking (car_id, book_id) VALUES (%s, %s)",
                                            GetSQLValueString($_POST['state'], "text"),
                                            GetSQLValueString($_POST['book_id'], "text")

                          /"INSERT INTO member_booking (mem_id, book_id) VALUES (%s, %s)",
                              GetSQLValueString($colname_information2, "text"),
                              GetSQLValueString($_POST['book_id'], "text")

                            );

edit: I tried to put semicolon ; at the end of insert, got an error syntax error, unexpected ';'

$insertSQL = sprintf("INSERT INTO booking (book_id,book_start_rent,book_end_rent,book_total,staff_id) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['book_id'], "text"),
                       GetSQLValueString($_POST['book_start_rent'], "text"),
                       GetSQLValueString($_POST['book_end_rent'], "text"),
                       GetSQLValueString($_POST['book_total'], "double"),
                       GetSQLValueString($_POST["state2"], "text");

                       "INSERT INTO car_booking (car_id, book_id) VALUES (%s, %s)",
                                            GetSQLValueString($_POST['state'], "text"),
                                            GetSQLValueString($_POST['book_id'], "text");

                          "INSERT INTO member_booking (mem_id, book_id) VALUES (%s, %s)",
                              GetSQLValueString($colname_information2, "text"),
                              GetSQLValueString($_POST['book_id'], "text");

                            );

edit 2: I finally can insert, but there is new error which is

Cannot add or update a child row: a foreign key constraint fails (car rental booking.member_booking, CONSTRAINT FK__member FOREIGN KEY (mem_id) REFERENCES member (mem_id) ON DELETE CASCADE ON UPDATE CASCADE)

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form2")) {
                              $insertSQL = sprintf("INSERT INTO booking (book_id,book_start_rent,book_end_rent,book_total,staff_id) VALUES (%s, %s, %s, %s, %s)",
                                                   GetSQLValueString($_POST['book_id'], "text"),
                                                   GetSQLValueString($_POST['book_start_rent'], "text"),
                                                   GetSQLValueString($_POST['book_end_rent'], "text"),
                                                   GetSQLValueString($_POST['book_total'], "double"),
                                                   GetSQLValueString($_POST["state2"], "text"));

                $insertSQL2 = sprintf("INSERT INTO car_booking (car_id, book_id) VALUES (%s, %s)",
                                     GetSQLValueString($_POST['state'], "text"),
                                     GetSQLValueString($_POST['book_id'], "text")  );

                                     $insertSQL3 = sprintf("INSERT INTO member_booking (mem_id, book_id) VALUES (%s, %s)",
                                         GetSQLValueString($colname_information2, "text"),
                                         GetSQLValueString($_POST['book_id'], "text")  );

You have an error in the **generated** SQL. Look at that first. — Álvaro González, Mar 08 '17 at 17:38
You can execute a single insert in one query (if you don' use a multi query env). you must perform 3 separated insert ... the error is simply that you have not ; (semicolon) at the end for the each insert — ScaisEdge, Mar 08 '17 at 17:38
1) Unless you're using mysqli's multi_query, you can't do all 3 inserts in the same query. Separate them out. 2) You're open for SQL injection. Make sure you're using PDO or mysqli, and use prepared statements and parameter binding. — aynber, Mar 08 '17 at 17:38
BTW, you have a very interesting division in your code, which I assume is totally involuntary: `GetSQLValueString(...) / "INSERT INTO..."`. Where you trying to comment out the string? As in `// "Insert INTO..."`? — Álvaro González, Mar 08 '17 at 17:40
if you really want to execute multiple queries at once with one sql use a semicolon after each — Dan Ionescu, Mar 08 '17 at 17:49
@ÁlvaroGonzález I thought needed to use / for escape the " ". — XitoxReturns, Mar 08 '17 at 17:57
@DanIonescu , I tried but got errror "syntax error, unexpected ';' " — XitoxReturns, Mar 08 '17 at 18:01
Well, `/` is the [division operator](http://php.net/manual/en/language.operators.arithmetic.php). If you need to escape something it's because you appear to be using a database library from the dinosaur age. Why not just PDO? — Álvaro González, Mar 08 '17 at 18:01
@ÁlvaroGonzález ahh I see, it is for my college`s project , they only teach us to connect database sql from dreamweaver. — XitoxReturns, Mar 08 '17 at 18:08
@XitoxReturns Just run each INSERT query as a separate query. — aynber, Mar 08 '17 at 18:42

Dan Ionescu · Answer 1 · 2017-03-08T18:07:57.753

0

You could use PDO:

// init PDO connection
$db = new PDO('mysql:host=localhost;dbname=testdb;', 'username', 'password');
$sql1 = "INSERT INTO booking (book_id, book_start_rent, book_end_rent, book_total, staff_id) VALUES (:book_id, :book_start, :book_end_rent, :book_total, :staff_id)";
$prepared1 = $db->prepare($sql);
$result1 = $prepared->execute(
    array(
        ':book_id' => GetSQLValueString($_POST['book_id'], "text"),
        ':book_start' => GetSQLValueString($_POST['book_start_rent'], "date"),
        ':book_end_rent' => GetSQLValueString($_POST['book_end_rent'], "date"),
        ':book_total' => GetSQLValueString($_POST['book_total'], "double"),
        ':staff_id' => GetSQLValueString($_POST["state2"], "text"),
    )
);
// and so on for each of your 3 queries

edited Mar 08 '17 at 18:07

answered Mar 08 '17 at 18:03

Dan Ionescu

3,135
1
12
17

1

There's no need to use getSQLValueString when using parameter binding. – aynber Mar 08 '17 at 18:07
If you're assuming that all that it does it's sql injection, but maby it's something more i don't know what's in the function – Dan Ionescu Mar 08 '17 at 18:09
That's pretty much all it does. http://stackoverflow.com/questions/4458180/php-getsqlvaluestring-function – aynber Mar 08 '17 at 18:35

SQL error , I tried to insert data in 3 table because other 2 is bridge

1 Answers1