Mysqli Insert Error Incorrect Syntax

mysqli insert error incorrect syntax

The most mistake-proof way to add a variable into an SQL query is to add it through a prepared statement.

So, for every query you run, if at least one variable is going to be used, you have to substitute it with a placeholder, then prepare your query, and then execute it, passing variables separately.

First of all, you have to alter your query, adding placeholders in place of variables. Your query will become:

$sql = "INSERT INTO users (fname, lname) VALUES (?, ?)";

Then, you will have to prepare it, bind variables, and execute:

$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $fname, $lname);
mysqli_stmt_execute($stmt);

As you can see, it's just three simple commands:

• prepare() where you send a query with placeholders
• bind_param where you send a string with types ("s" is for string and you can use it for any type actually) and than actual variables.
• and execute()

This way, you can always be sure that not a single SQL syntax error can be caused by the data you added to the query! As a bonus, this code is bullet-proof against SQL injection too!

It is very important to understand that simply adding quotes around a variable is not enough and will eventually lead to innumerable problems, from syntax errors to SQL injections. On the other hand, due to the very nature of prepared statements, it's a bullet-proof solution that makes it impossible to introduce any problem through a data variable.

mysqli insert, what is wrong?

Your data is not inserting because you haven't even executed your query.

if (mysqli_num_rows($query) > 0) {

    echo "Username already exists";

} else { 

    $sql = "INSERT INTO users (Username, Password, FirstName, LastName, Email, ContactNumber) VALUES ('".$_POST["Username"]."','".$_POST["Password"]."','".$_POST["FirstName"]."','".$_POST["LastName"]."','".$_POST["Email"]."','".$_POST["ContactNumber"]."')";

    /* Run your query and check for errors */
    $query = mysqli_query($conn, $sql) or die(mysqli_error($conn)); 
}

MySQL INSERT Syntax error and unsure why

Two possible syntax for INSERT statement.
In the first following case, you specify only col you want to fill.

INSERT INTO TABLE (COL1, COL2, COL3)
VALUES (VAL_COL1, VAL_COL2, VAL_COL3);

You can also INSERT without providing col_name but you will have to specify value of all columns and in the good order.

The first opton is better in my opinion and will avoid you many mistakes especially when you have a lot of different column in your table.

I have an SQL syntax error when inputing data

update your query replace single quote(') from table name and column name with (`), Like

$sql = "INSERT INTO `user` (`fname`, `lname`) VALUES ('$x','$y')";

MySQL Syntax error on Insert Query from PHP

Passerby, thank you for your comment. This was my first experience with using mysqli, I changed my query to use the "bind_param" method, and everything works now. For anyone else with a similar problem, here is the corrected code...

        $mysqli = new mysqli($UM_Settings["database_options"]["server_name"], $UM_Settings["database_options"]["username"], $UM_Settings["database_options"]["password"], $UM_Settings["database_options"]["database_name"]);
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }

    $advertisementNameNew = $_POST['advertisementNameNew'];
    $destinationURLNew = $_POST['destinationURLNew'];
    $dropboxUploadFile = $_POST['dropboxUploadFile'];
    $backgroundColorNew = $_POST['backgroundColorNew'];
    $bannerStartDateNew = DateStringToMySQL($_POST['bannerStartDateNew']);
    $bannerEndDateNew = DateStringToMySQL($_POST['bannerEndDateNew']);
    $bannerSetTimerNew = intval($_POST['bannerSetTimerNew']);
    $tmpUserID = UM_GetCookie("UM_UserID");
    $tmpAddDate = DateStringToMySQL('now');

    /* Prepared statement, stage 1: prepare */
    if (!($stmt = $mysqli->prepare("INSERT INTO `ADVERTISEMENTS` (`user_id`, `ad_name`, `click_url`, `img_url`, `bg_color`, `start_date`, `end_date`, `timer_delay`, `add_date`) VALUES (?,?,?,?,?,?,?,?,?)"))) {
        echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
    }

    if (!$stmt->bind_param("issssssis",$tmpUserID, $advertisementNameNew, $destinationURLNew, $dropboxUploadFile, $backgroundColorNew, $bannerStartDateNew, $bannerEndDateNew, $bannerSetTimerNew, $tmpAddDate)) {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
    }

    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }

    $_GET['ad_id'] = $stmt->insert_id;
    $stmt->close(); 

SQL Replace or Insert Into Syntax error

MySQL does not support any INSERT OR REPLACE INTO syntax that I've seen. You may be looking for something like INSERT INTO ... ON DUPLICATE KEY UPDATE. In your example it may look like this:

INSERT INTO myTable (name, string, lang) VALUES (param1, param2, param3)
ON DUPLICATE KEY UPDATE name = param1, string = param2, lang = param3;

More information can be found in the documentation here: https://dev.mysql.com/doc/refman/5.0/en/insert.html

An example of this can be found using this SQL Fiddle.

---

Related Topics