How to Insert Data with Apostrophes into MySQL without Syntax Errors?

Troubleshooting Insertion of Data with Apostrophes into MySQL

When executing INSERT queries containing apostrophes (single quotes), such as company names like "Kellogg's," users may encounter syntax errors. This issue arises due to the special interpretation of apostrophes in SQL.

To resolve this problem, the apostrophe in the data must be escaped with a backslash (). By escaping the apostrophe, the SQL interpreter will treat it as a character within the string instead of as a syntax delimiter.

Example:

Kellogg's will become Kellogg\'s

Another effective solution is to use the mysql_real_escape_string() function, which automatically takes care of escaping special characters, including apostrophes. Here's an example function that employs this technique:

function insert($database, $table, $data_array) {
    foreach ($data_array as $key => $value) {
        $tmp_dat[] = "'" . mysql_real_escape_string($value) . "'";
    }
    $data = join(',', $tmp_dat);
    $sql = 'INSERT INTO ' . $table . ' VALUES('. $data.')';
    $result = mysql_query($sql);
}

By incorporating these methods, data containing apostrophes can be successfully inserted into MySQL databases, resolving the syntax errors that arise during insertion.

The above is the detailed content of How to Insert Data with Apostrophes into MySQL without Syntax Errors?. For more information, please follow other related articles on the PHP Chinese website!