current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 0 down vote favorite

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!

share|improve this question
    
Please Echo all the Variables before insertion query, so that you can know whether the values are missing or not. – Bharu Jul 15 '13 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 Jul 15 '13 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))){} – Josh Cox Jul 15 '13 at 4:18
    
Can you tell me what is returned by DateStringToMySQL function? Also, change the quotes used in the query and try. – Debashis Jul 15 '13 at 6:18
up vote 1 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote accepted

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();
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.