MySQL "INSERT INTO"-error: which quotation do I have to use?

Question

I'm making a search engine based on the API of Faroo.com (http://www.faroo.com/hp/api/api.html) for a school project. I would like to index the index of Faroo, so that users (in my situation, children) can vote up or vote down individual results.

What my (PHP)-script is like:

Look in the MySQL-database if the query exists.

yes => load the results from the database and show them to the user

no => load the results from Faroo, show those results to the user and store them in the database

My database looks like this:

I'm getting all the data stored in the columns from the Faroo API, except for the 'id'-column.

The last part (of storing the Faroo-data in the database) is where it goes wrong:

for($x=0; $x<$tel; $x++){            
    $sql = "INSERT INTO queries (`id`, `query`, `title`, `url`, `domain`, `kwic`, `votes`) VALUES (NULL, $q, $titles[$x], $urls[$x], $domains[$x], $kwics[$x], 0);";
    echo '<br />'.$x.'e query: @'.$sql.'@';
    if(!$resultaat = $db->query($sql)){
        die('De query kon niet worden uitgevoerd: [' . $db->error . ']');
    }            
    $resultaat = mysqli_fetch_array($resultaat);
}

$tel is a variable which counts the number of results I get from Faroo. It gets defined before this piece of code. When I run this code, I am getting a nice MySQL-error:

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 'States Bureau of Mines - Wikipedia, the free encyclopedia, www.wikipedia.org' at line 1

I've searched, and searched, but I couldn't find what the SQL-error is. I think it has something to do with the strange characters in the strings, or maybe my quotation is false?

Kind regards, Max

You need to quote your string values; or even better use [parameterized queries](http://stackoverflow.com/questions/728229/parameters-in-mysqli). — Burhan Khalid, Feb 22 '14 at 11:26

score 3 · Accepted Answer · answered Feb 22 '14 at 11:27

I think you need to use single quotes ' for varchar columns, so change as follow

$sql = "INSERT INTO queries (`id`, `query`, `title`, `url`, `domain`, `kwic`, `votes`) VALUES (NULL, '$q', '$titles[$x]', '$urls[$x]', '$domains[$x]', '$kwics[$x]', 0)";

You also have an extra double quote at the end of the query which i removed, you won't need singles quotes for columns id and votes since they are integer fields

MySQL "INSERT INTO"-error: which quotation do I have to use?

1 Answers1