Column count doesn't match value count at row 1

[smproph]

Why am I getting this error when there are 3 Fields with 3 values?

 

Column count doesn't match value count at row 1

 

$sql5="INSERT INTO participants (participant_name, team_no, sport)
		VALUES ('".implode("','$_POST[team]'),('",$_POST['participant_name'])."','$_POST[team]','$sport')";

 

[Pikachu2000]

Echo the query string and see what it actually is.

[xyph]

You really should program with error reporting/display on.

 

Your implode function is typed incorrectly, and you aren't concatenating it properly.

[smproph]

How should it look? I'm relatively new to php. It works perfectly whenever I take the '$sport' out of values and sport out of INPUT. But when I add them in I get that error

[xyph]

Echo the query string and see what it actually is.

[smproph]

INSERT INTO participants (participant_name, team_no, sport) VALUES ('ply2','Team'),('ply1','Team'),('pl5','Team'),('jfsd','Team'),('da','Team','dodgeball')

 

I can see the dodgeball is only being added to the last player. But I cannot figure out why. Does it have to do to with my implode function?

[DavidAM]

It is definitely your implode function call. It took me a while to figure out what you were trying to do. And when you come back to this script in a few months to make changes, it will take you a while to figure out how to do that. Just off the top of my head, a cleaner way to write that would be:

 

$team = 4;
$pnames = array('DavidAM', 'smproph', 'Pikachu2000', 'xyph');
$sport = 'programming';

$values = array();
foreach ($pnames as $pName) {
$values[] = "('$pName', '$team', '$sport')";
}

$sql5="INSERT INTO participants (participant_name, team_no, sport) VALUES " .
implode(',', $values);

 

There may be an even more elegant way of doing it, but that's worth more than twice what you paid me for it.

[smproph]

Problem is that now I would have to hardcode the players name. I tried adding

 

$name= $_POST['participant_name'];
$pnames = array($name);

 

But as you probably know it is only adding one player instead of however many the user selected and it adds the name as "Array"

[xyph]

INSERT INTO participants (participant_name, team_no, sport) VALUES ('ply2','Team'),('ply1','Team'),('pl5','Team'),('jfsd','Team'),('da','Team','dodgeball')

 

I can see the dodgeball is only being added to the last player. But I cannot figure out why. Does it have to do to with my implode function?

 

My mistake. You had everything formatted properly, I didn't know what $_POST values were arrays and which were strings. Sorry.

 

DavidAM has the ideal solution IMO. A concatenated string over an array you later implode would save a little bit of memory, but the difference is negligible on any modern system not getting thousands of requests per second

[xyph]

You want to use

 

foreach ($_POST['participant_name'] as $pName) {
$values[] = "('$pName', '{$_POST['team']}', '{$sport')";
}

 

I think

 

He was just showing you an example.

[DavidAM]

Yeah, I was just showing an example. Last time I provided an example using the OP's code, with the $_POST intact, I got blasted for not sanitizing the inputs. So, I thought I would just give an example and hope the "programmer" could understand the concept and translate to his/her needs.

 

By the way - It is NOT a good idea to use $_POST (or $_GET or even $_COOKIE) directly in a query without sanitizing them first . So, to expand the example:

 

$values = array();
foreach ($_POST['participant_name'] as $pName) {
$values[] = sprintf("('%s', %d, '%s')", 
	mysql_real_escape_string($pName),
	int_val($_POST['team'],
	mysql_real_escape_string($sport));
/* UNLESS the team_no column is a VARCHAR or CHAR or TEXT 
	in which case it would be:
$values[] = sprintf("('%s', '%s', '%s')", 
	mysql_real_escape_string($pName),
	mysql_real_escape_string($_POST['team']),
	mysql_real_escape_string($sport));
*/
}

$sql5="INSERT INTO participants (participant_name, team_no, sport) VALUES " .
implode(',', $values);

 

I'm guessing we are using mySql here. If not, use the appropriate escaping mechanism for your database of choice.