Only 1 of an array of checkboxes getting into db

PHP
1

I’m trying to copy Kevin Yank’s method for inserting the values from multiple checkboxes (called “skill”) into a lookup db table. However, only 1 item ends up in the db per entry. Using code from Ch7 PHP & MySQL Novice to ninja. Is something wrong? I’ve followed the script as closely as I can and the example is practically identical! Entire form submits but no error thrown.
This is the relevant PDO script:

$insertID = $pdo->lastInsertId();

if (isset($_POST['skill']))
{
	try
	{
		$skillsql= 'INSERT INTO freelance_skills SET flid = :flid, skillid = :skillid';
		$s = $pdo->prepare($skillsql);
		foreach ($_POST['skill'] as $skillid);
		{
			$s->bindValue(':flid', $insertID);
			$s->bindValue(':skillid', $skillid);
			$s->execute();
		}
	}
	catch (PDOException $e)
	{
	$error ='Error adding user skills';
	include 'error.html.php';
	exit();
	}

The calling form is structured like this:

<?php
include '../../includes/db.inc.php';
//query table for press skills
try
{
$presssql = "SELECT * FROM freelance_roles WHERE category = 'Print'";

$pressskills = $pdo->query($presssql);
}
catch (PDOException $e)
{
	$error = 'error getting skill data: '. $e->getMessage();
	include 'error.html.php';

}
foreach ($pressskills as $skillrow):  ?>
	<p><input type="checkbox" name="skill[]" value="<?php htmlout($skillrow['ID']); ?>" id="skill<?php htmlout($skillrow['ID']); ?>"/><label for="skill<?php htmlout($skillrow['ID']); ?>"> <?php htmlout($skillrow['role']); ?></label></p>
<?php endforeach; ?>
2

Should you increment $insertID in the loop somewhere as you add skills?

3

Should you increment $insertID in the loop somewhere as you add skills?

No- that refers to the skill owner (previous query not shown), I am adding multiple skills (via checkbox) per single user (rest of form gathers profile info), so it remains constant. The $insertID refers to that user’s (just added) profile ID.

4

I’m actually surprised this works at all. The SQL Syntax is wonky to me. You are using a mixture of INSERT and UPDATE syntax combined.

What happens if you change this line

$skillsql= 'INSERT INTO freelance_skills SET flid = :flid, skillid = :skillid';

To

$skillsql= 'INSERT INTO freelance_skills (flid, skillid) VALUES (:flid, :skillid)';
5

It makes no difference- the two lines are equivalent, though I did try anyway! I got the code straight out of the book, so I’m surprised it doesn’t work!

6

Okay, time to start debugging this.

If you put var_dump($_POST); before your $skillsql line, what does it output to the screen (can you copy and paste it here)?

7

array(17) { [“MAX_FILE_SIZE”]=> string(4) “2048” [“firstname”]=> string(5) “Peter” [“lastname”]=> string(6) “Parker” …

[“skill”]=> array(5) { [0]=> string(2) “36” [1]=> string(2) “39” [2]=> string(1) “1” [3]=> string(2) “14” [4]=> string(2) “34” } [“act”]=> string(3) “add” [“Submit”]=> string(3) “Add” }

Thanks for the tip, at least I now know that the whole array is properly sent. I’ve cut out a few irrelevant bits for clarity & confidentiality. That would appear to leave the PDO handler or the database table itself, both of which are copied from the book! The table records 21, 34, so it would appear that the last array item is the only one making it through.

8

Okay, I just caught this, but trying removing the semi-colon at the end of your foreach loop.

foreach ($_POST['skill'] as $skillid); // remove this semi-colon
9

Okay, I’m confident that semi-colon is your issue. Here is why:

The semi-colon is telling PHP, this is the end of the foreach statement. So it is looping over your skills, placing each one in $skillid but never executing the execute command for your prepared statement because the semi-colon tells it there is nothing more for the foreach to process.

Removing the semi-colon now makes the content inside the curly braces part of the foreach statement and therefore it should execute that logic for each skill.

10

As usual, something so small, it’s impossible to see! :rolleyes: Thank you- the script now adds multiple lines to the table!:smiley: