插入带有数组的MYSQL时出现PHP错误

Question

I have a script which inserts all data in array to MYSQL. But when there is just a single word in the array, the script gives no error, while when there are multiple words, it gives a

Column count doesn't match value count at row 1

Here is my code

<?php
if($_SERVER['REQUEST_METHOD'] == 'POST') {
    include("connect.php");
    $counter = 0;
    $counters = 0;
    $string = mysql_real_escape_string($_POST['words']);
    $arr = explode(" ", $string);
    mysql_query("SET charset utf8");
    $sql = mysql_query("SELECT `word` FROM unicode WHERE word IN ('".implode("', '", $arr) . "')") or die (mysql_error());
    $dupes = array();
    while($r = mysql_fetch_assoc($sql)) {
        $dupes[] = $r['word'];
    }
    $newwords = array_diff($arr, $dupes);
    if(count($newwords)) {
        $word = implode("'),('", $newwords);
        $md5 = md5($word);
        $sqli = mysql_query("INSERT INTO unicode (word, word_hash) VALUES ('$word', '$md5')") or die (mysql_error());
    }
}
?>

Please help....

Answer 1

As a rule, when I have problems with SQL I do the following things to track down the issue.

1. ECHO out the SQL query I am trying to run against the DB. This makes sure that I am passing the value of the variable and not the the text '$variable'.

2. Switch on and check the general.log table in the MySQL DB (assuming you are using MySQL). This will show you the last queries run against the DB and will prove one way or another if your script is even executing anything against the DB.

Lastly I am not as au fait with imploding etc as suggest above to comment, however I would also add the following. Looking at your query it looks as if you are doing I what I talked about in point 1.

$sqli = mysql_query("INSERT INTO unicode (word, word_hash) VALUES ('$word', '$md5')") or die (mysql_error());

The single quotes around $word and $md5 would mean literally pass $word and $md5 into the DB. When using variables within double quote " ... " you do not need to put anything around them just use them as is. Or if you would like to use single quote marks you can concatenate the query string.

$sqli = mysql_query('INSERT INTO unicode (word, word_hash) VALUES ( ' . $word . ', ' . $md5 . ')') or die...

Again echo out the query as you have it (without the mysqli_query function) to confirm.

Hope this helps.

S