MySQL Syntax error on Insert Query from PHP

Question

I'm getting a non-descriptive syntax error on a MYSQL query from PHP. If I "echo" the text of the query and paste it into a MySQL query window, the code works. Here is the SQL for the query, the error code, and the error message...

INSERT INTO ADVERTISEMENTS (`user_id`, `ad_name`, `click_url`, `img_url`, `bg_color`, `start_date`, `end_date`, `timer_delay`, `add_date`) VALUES (2, 'Test New Ad', 'http://www.google.com', 'red_arrow.png', '#000000', '1980-05-11 00:00:00', '2020-05-01 00:00:00', 5, '2013-07-14 22:21:59');

Error Code: 1064
Error Msg: 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 '' at line 1

Here is the PHP code I am using...

$link = mysqli_connect($UM_Settings["database_options"]["server_name"], $UM_Settings["database_options"]["username"], $UM_Settings["database_options"]["password"], $UM_Settings["database_options"]["database_name"]);

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

echo "INSERT INTO ADVERTISEMENTS(`user_id`, `ad_name`, `click_url`, `img_url`, `bg_color`, `start_date`, `end_date`, `timer_delay`, `add_date`) VALUES ($tmpUserID, '$advertisementNameNew', '$destinationURLNew', '$dropboxUploadFile', '$backgroundColorNew', '$bannerStartDateNew', '$bannerEndDateNew', $bannerSetTimerNew, '$tmpAddDate');<br />";

if (!mysqli_query($link, "INSERT INTO ADVERTISEMENTS(`user_id`, `ad_name`, `click_url`, `img_url`, `bg_color`, `start_date`, `end_date`, `timer_delay`, `add_date`) VALUES ($tmpUserID, '$advertisementNameNew', '$destinationURLNew', '$dropboxUploadFile', '$backgroundColorNew', '$bannerStartDateNew', '$bannerEndDateNew', $bannerSetTimerNew, '$tmpAddDate');")) {
    printf("Error Code: %s\n",  mysqli_errno($link));
    echo "<br />";
    printf("Error Msg: %s\n",  mysqli_error($link));
}

I know that the database connection is working. I am able to select and update tables. I can also insert into other tables with different queries.

I am open to any suggestions.

Thank you in advance for your help!

Please Echo all the Variables before insertion query, so that you can know whether the values are missing or not. — Bharu
– Bharu, Commented Jul 15, 2013 at 3:52
I think you're not using mysqli the good way; and I notice that you ended your statment with a ;, which may not be necessary in API calls. — Passerby
– Passerby, Commented Jul 15, 2013 at 4:00
try (accent)ADVERTISEMENTS(accent) lose the ; at the end ... where you echo the line store it to a variable echo $query = "INSERT INT.... then in your if(!(mysqli_query($link,$query))){} — tyler
– tyler, Commented Jul 15, 2013 at 4:18
Can you tell me what is returned by DateStringToMySQL function? Also, change the quotes used in the query and try. — Debashis
– Debashis, Commented Jul 15, 2013 at 6:18

Julien Spronck · Accepted Answer · 2013-07-15 04:06:30Z

1

I see a few errors in your query strings.

First, all your variables are passed as literal strings: "... VALUES ($tmpUserID, '$advertisementNameNew', ..." should be "... VALUES (".$tmpUserID.", '".$advertisementNameNew."', ...".

Second, I see missing quotes around $bannerSetTimerNew.

Third, there is an extra ;.

here's how I would write the query:

if (!mysqli_query($link, "INSERT INTO ADVERTISEMENTS (user_id, ad_name, click_url, img_url, bg_color, start_date, end_date, timer_delay, add_date) VALUES (".$tmpUserID.", '".$advertisementNameNew."', '".$destinationURLNew."', '".$dropboxUploadFile."', '".$backgroundColorNew."', '".$bannerStartDateNew."', '".$bannerEndDateNew."', '".$bannerSetTimerNew."', '".$tmpAddDate."')")) { ...

I didnt test it though.

hope this helps.

edited Jul 15, 2013 at 4:06

answered Jul 15, 2013 at 3:52

Julien Spronck

15.5k5 gold badges50 silver badges57 bronze badges

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

Comments

Pranay Prakash · Accepted Answer · 2013-07-15 04:01:01Z

0

I see a ; at the end of the query. Are you sure that should be there?

answered Jul 15, 2013 at 4:01

Pranay Prakash

3684 silver badges13 bronze badges

Comments

Hitesh · Accepted Answer · 2013-07-15 05:58:15Z

0

There are two things 1. Remove the ; from at the end of the query. 2. I hope timer_delay field has datatype "Int" if its a VARCHAR then you will have to include quotes for that field value.

I hope this will help.

answered Jul 15, 2013 at 5:58

Hitesh

451 silver badge4 bronze badges

Comments

user2582108 · Accepted Answer · 2013-07-16 03:29:26Z

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();

Collectives™ on Stack Overflow

MySQL Syntax error on Insert Query from PHP

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related