current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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....

share|improve this question
1  
You're imploding $words so you'd end up with something like VALUES ('word1'),('word2'),('word3') –  billyonecan Jul 17 '13 at 9:32

3 Answers 3

up vote 1 down vote accepted

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

share|improve this answer
    
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 ''),('ch'),('fgh, a1f3c00a8cb073d61cd6827189a118fe)' at line 1 –  shahbaz Jul 17 '13 at 10:36
    
can you add the full SQL statement by echoing the $sqli without the mysqli_query function to this thread? –  Simon Jul 17 '13 at 11:24
    
srry...actually i m a newbie....can u plz tell me the code to echo $sqli without mysql_query???thxx in advance –  shahbaz Jul 18 '13 at 9:12
    
Assign the variable the $sqli = ("INSERT INTO unicode (word, word_hash) VALUES ('$word', '$md5')") or die (mysql_error()); - The echo $sqli . "<br>"; –  Simon Jul 19 '13 at 8:31
    
Did you get anywhere with this issue? –  Simon Jul 22 '13 at 9:07
up vote 1 down vote

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.

share|improve this answer
up vote 1 down vote

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')
share|improve this answer
    
Looking at your current code, you'd have to calculate the md5 finger printfrom $word, and then implode $newwords again afterwards so that $md5 is available to use –  billyonecan Jul 17 '13 at 9:45
    
sorry...i didn't get it....actually i think this is the problem...but i need to generate the md5 of each word and insert the word and md5...?? thxx in advance... –  shahbaz Jul 17 '13 at 10:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.