PHP error while inserting in MYSQL with arrays

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

You're imploding $words so you'd end up with something like VALUES ('word1'),('word2'),('word3') — billyonecan
– billyonecan, Commented Jul 17, 2013 at 9:32

Simon · Accepted Answer · 2013-07-17 09:58:20Z

1

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

answered Jul 17, 2013 at 9:58

Simon

2382 gold badges3 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

shahbaz Over a year ago

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

Simon Over a year ago

can you add the full SQL statement by echoing the $sqli without the mysqli_query function to this thread?

shahbaz Over a year ago

srry...actually i m a newbie....can u plz tell me the code to echo $sqli without mysql_query???thxx in advance

Simon Over a year ago

Assign the variable the $sqli = ("INSERT INTO unicode (word, word_hash) VALUES ('$word', '$md5')") or die (mysql_error()); - The echo $sqli . "<br>";

Simon Over a year ago

Did you get anywhere with this issue?

billyonecan · Accepted Answer · 2013-07-17 09:38:31Z

1

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')

answered Jul 17, 2013 at 9:38

billyonecan

20.3k9 gold badges44 silver badges66 bronze badges

2 Comments

billyonecan Over a year ago

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

shahbaz Over a year ago

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

Amal · Accepted Answer · 2013-07-17 09:40:06Z

1

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.

edited Jul 17, 2013 at 9:40

answered Jul 17, 2013 at 9:34

Amal

76.8k18 gold badges133 silver badges154 bronze badges

Collectives™ on Stack Overflow

PHP error while inserting in MYSQL with arrays

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related