PHP | SQL syntax error when inserting array

Question

Hi guys,

I am having some trouble inserting an array into the sql database.

my error is as follows:

Unable to add : 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 '06:45:23,i want to leave a comment)' at line 1 

My query var_dump is:

string(136) "INSERT INTO news_comments (news_id,comment_by,comment_date,comment) VALUES (17263,Philip,2010-05-11 06:45:23,i want to leave a comment)"

My question is how can i add an empty value to id as it is the primary key and not news_id

my insert function looks like this:

function insertQuery($tbl, &$data)
    {
        global $mysqli;
        $_SESSION['errors'] = array();
        require_once  '../config/mysqli.php';
        $query = "INSERT INTO $tbl (".implode(',',array_keys($data)).") VALUES (".implode(',',array_values($data)).")";
        var_dump($query);
        if($result = mysqli_query($mysqli, $query))
        {
        //$id = mysqli_insert_id($mysqli);
        print 'Very well done sir!';
        }
        else
        {
            array_push($_SESSION['errors'], 'Unable to add : ' . mysqli_error($mysqli));
        }
    }

Note: arrays are not my strong point so i may be using them in-correctly!

Answer 1

the values (if not numeric) have to be placed in between quotes. go through the array and place the values into quotes, then you can implode it as you did in your code snippet.

Answer 2

You need to wrap your data with single quotes 'yyyy-mm-dd hh:mm:ss' (You need to apply them to all text fields (date, varchar, char, text so on). Also, make sure to properly escape any single quotes that might be part of the text.

Answer 3

You need a " arround the string i want to leave a comment comment

Try this

$query = "INSERT INTO $tbl (".implode(',',array_keys($data)).") VALUES (\"".implode('","',array_values($data))."\")";

Answer 4

I think you are missing quotes (') around string values eg values should be like:

17263,'Philip','2010-05-11 06:45:23','i want to leave a comment'

Note: Don't forget to use mysql_real_escape_string function for string values.

Answer 5

You are using arrays correctly here, even if in an odd manner. Your main problem is that how you're doing it, you're breaking the query:

INSERT INTO news_comments (news_id,comment_by,comment_date,comment) VALUES (17263,Philip,2010-05-11 06:45:23,i want to leave a comment)

The string values should be quoted, as such:

INSERT INTO news_comments (news_id,comment_by,comment_date,comment) VALUES (17263,'Philip','2010-05-11 06:45:23','i want to leave a comment')

Technically, if you put quotes around your numbers, too, it won't make a difference, since MySQL should convert them to numbers as needed. So, your code should be more like this:

$query = "INSERT INTO $tbl (".implode(',',array_keys($data)).") VALUES ('".implode("','",array_values($data))."')";

Notice the extra ' quotes in the second array_implode. This will wrap every value with a single quote, allowing it to be used in the database.

Note, however, that if any of the values contain a ', then it will break. You need to escape these, usually using a double, turning it into ''. if you use mysql_escape_string, it will take care of all this for you, but it has to be done on each individual value.