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....
As a rule, when I have problems with SQL I do the following things to track down the issue.
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'.
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
The number of column parameters in your INSERT query is more than 2, but you've only provided 2 values.
$word = implode("'),('", $newwords);
This statement here is the culprit. When you implode the $newwords array, you'd probably get more than 2 values. When inserted into the MySQL query, it won't match with the number of VALUES you've provided. That's causing the error.
You're imploding $newwords, so the resulting query would look something like:
...VALUES ('word1'),('word2'),('word3', 'md5 string')
Add $md5 to implode():
$md5 = 'md5 string';
$word = implode("', '$md5'),('", array('word1', 'word2', 'word3'));
Outputs:
...VALUES ('word1', 'md5 string'),('word2', 'md5 string'),('word3', 'md5 string')