Cannot insert post array in mysql table

Question

I'm trying to insert a POST array consisting in 8 fields to a mysql table of 8 columns, but I'm getting this error when submitting the form:

Error: Column count doesn't match value count at row 1

When I search this error says the data passed doesn't fit in the column count of the database table, but the table has 8 columns. What I'm doing wrong?

Here's my code:

<html>
<body>
<form action="" method="post">

Nombre: <input type="text" name="data[]">
Apellido: <input type="text" name="data[]"></br>
Direccion: <input type="text" name="data[]"></br>
Telefono: <input type="text" name="data[]">
Telefono 2: <input type="text" name="data[]"></br>
Email: <input type="text" name="data[]"></br>
Edad: <input type="text" name="data[]"></br>
Foto: <input type="text" name="data[]"> 
<input type="submit">

</form>

<?php

$con=mysql_connect("localhost","root","");

if (!$con){  die('Could not connect: ' . mysql_error()); }


mysql_select_db("ag_online", $con);

foreach($_POST['data'] as $d ){
    $sql = "INSERT INTO `contacts` VALUES ('', '".$d."');";
    mysql_query( $sql ); 
}


if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}   
echo "1 record added";

mysql_close($con);

?>

</body>
</html>

zipser · Accepted Answer · 2013-03-31 20:04:43Z

2

If the table has 8 columns, you cannot use:

$sql = "INSERT INTO `contacts` VALUES ('', '".$d."');";

directly.

You have to specify which columns you are inserting:

$sql = "INSERT INTO `contacts` (col1, col2) VALUES ('', '".$d."');";

Update
When you using

foreach($_POST['data'] as $d ){
    $sql = "INSERT INTO `contacts` VALUES ('', '".$d."');";
    mysql_query( $sql ); 
}

is the same as using

foreach($_POST['data'] as $d ){
    $sql = "INSERT INTO `contacts` (Nombre, Apellido, Direccion, Telefono, Telefono2, Email, Edad, Foto)  VALUES ('', '".$d."');";
    mysql_query( $sql ); 
}

as you say in the comments.

However, as you are looping on $_POST['data'], it will come to something like:

first loop:

    $sql = "INSERT INTO `contacts` (Nombre, Apellido, Direccion, Telefono, Telefono2, Email, Edad, Foto)  VALUES ('', 'FIRST_VALUE');";

first loop:

    $sql = "INSERT INTO `contacts` (Nombre, Apellido, Direccion, Telefono, Telefono2, Email, Edad, Foto)  VALUES ('', 'SECOND_VALUE');";

that is, you will try to insert 2 values after saying you will give 8.

You can use the following to populate a variable $info and then do an unique insert:

$info=implode(",", $_POST['data']);
$info="\"".implode("\",\"", $_POST['data'])."\""; //<--- updated answer, to have all items "wrapped"

and then

$sql = "INSERT INTO `contacts` (Nombre, Apellido, Direccion, Telefono, Telefono2, Email, Edad, Foto)  VALUES ('', $info);";

edited Mar 31, 2013 at 20:04

answered Mar 31, 2013 at 18:30

zipser

2401 silver badge8 bronze badges

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

23 Comments

Guille Over a year ago

I've tried: foreach($_POST['datos'] as $d ){ $sql = "INSERT INTO contactos (Nombre, Apellido, Direccion, Telefono, Telefono2, Email, Edad, Foto) VALUES ('', '".$d."');"; mysql_query( $sql ); }

zipser Over a year ago

Can you show a print_r($_POST['datos']); this way we can see what can be happening

Ivan Yonkov Over a year ago

how are you going to insert nombre, apellida, direccion, telefono, etc.. with values ('', '', 'id'), it DOES NOT match again. As @zipser said you have to explicively tell which column you are going to insert in. If you are inserting 2 values, then only 2 columns

Guille Over a year ago

sure: Array ( [0] => Name [1] => LastName [2] => Address [3] => 123 [4] => 456 [5] => [email protected] [6] => 25 [7] => photo )

zipser Over a year ago

OK so you have to follow what @RoyalBg is telling you: insert as many fields as you indicate on INSERT INTO contacts (field1, field2, ...) VALUES ....

|

devBinnooh · Accepted Answer · 2013-03-31 18:53:28Z

0

If you are trying to have each one in column, then you might want to consider this

$sql = "INSERT INTO `contacts` VALUES ('";
foreach($_POST['data'] as $d ){
 $sql .= $d."','";
}
 $sql = substr_replace($sql, "", -2);
 $sql .= ");";
 mysql_query( $sql );

edited Mar 31, 2013 at 18:53

answered Mar 31, 2013 at 18:36

devBinnooh

6113 silver badges12 bronze badges

3 Comments

kuldeep.kamboj Over a year ago

It will give trailing comma + apostrophe which will generate error, You need removal of last comma + apostrophe outside loop.

Guille Over a year ago

i'm getting this error with that code: Parse error: syntax error, unexpected T_FOREACH in C:\wamp\www\ABM 1\index.php on line 29

kuldeep.kamboj Over a year ago

Add semi colon after first line

Collectives™ on Stack Overflow

Cannot insert post array in mysql table

2 Answers 2

23 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

23 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related