php mysql form insert more than one record at once whats the best way?

I have a form.

Name

option1
option2
option3
etc... all the way up to option 100

When I insert the data into mysql I want to create a separate record for each option filled out. And if no option is filled out for option2 option3 ect.. it does not insert a row for that.

What is my best choice of going about this a foreach?

 

You could append the values you want to insert into a sql statement in your loop and then run your statement after that.

INSERT INTO Table ( Column1, Column2 ) VALUES ( Value1, Value2 ),( Value1, Value2 ),...

PHP:

 

Is that more efficient?

If it helps any... Pretty much its going to look like this in the database.. I just wasn't sure what the most effcient or best way was. Thanks

name - option1
"" - option2
"" - option3
nextname - option1
nextname -option2

 

You should rethink your database. I dunno what you're registering, but a better way would be separate tables for users (if you have more than just name) and one for all the options in one row(1-100) linked via an unique id.

 

I completely agree with you. You have noooooo idea how bad I agree with you. And after reading your post a few days a quote stuck with me " you are the warden of the prison lets face you run the show but even though they are the people who pay you, you can either let the prisoners run the show or you can be the warden" So I have thought about it for a few days and I think I am going to go over the project managers head and just do things correctly." Because in all honesty what happens is they know somethings but they do not understand everything, and there not exactly the type that will ask for help when they should. And they have been calling the shots and get so upset because the bandied fixes or building, and how long it takes because of how they wanted things built before(In other words not to much future planning). But in the long run it takes so much longer because of all the half **sing. (In other words use the horrible piece of software or use that and them copy and paste sort of thing) I am sure you get the idea. Then what happens so much of the time is someone will make a mistake then I end up spending hours of trying to find out what went wrong because the first person who made the mistake trys to delete it. It was the same concept of logging all the records when someone inserts a record. Ha! anyways I am doing to do it that way, because it will save time in the future they are just not focused enough to see into the next few months once there databases get so bloated and they get mad because they are slowing down because of the type of the structure they wanted in the first place. (Its been like that for years at this one place and it drives me nuts!) I am going to take charge. Thank you if you wouldn't have said that I would probably have never thought of the warden story.

 

I went over the project managers head and just said screw so we can avoid stuff down the road. And then I used this code.

block=$_POST['block'];
$num=$_POST['num'];
$num2id=$_POST['num2id'];

for($i=0;$i<=count($block);$i++) {
if (!empty($block[$i])){
$query3 = $dbi->prepare('INSERT INTO blockit (`num`,`num2ud`,`block_#`)  VALUES (?,?,?)');
$query3->bindValue(1, $num);
$query3->bindValue(2, $num);
$query3->bindValue(3, $block[$i]);

$query3->execute();
}
}

PHP:

 

You actually missed a fun detail of how prepared queries work. Prepare and bind can be done BEFORE the loop. Also, shouldn't that second bind be $num2id? I'd also suggest NOT wasting time on "variables for NOTHING" and use bindParam instead of bindValue. BindValue is static, it stores the value fixed. BindParam lets you change that variable's value and it will follow-through in the query. Also, foreach? USE IT.

I'm assuming you're using PDO since there is no bindValue in mysqli.

$stmt = $dbi->prepare('
	INSERT INTO blockit (
		num, num2ud, block_#
	) VALUES (
		?, ?, ?
	)
');
$stmt->bindValue(1, $_POST['num']);
$stmt->bindValue(2, $_POST['num2id']);
$value = ''; // must be non-null / set for bind to work
$stmt->bindParam(3, $value);

foreach ($_POST['block'] as $value) if (!empty($value)) $stmt->execute();

Code (markup):

Untestest, but should work. Using bindParam makes PDO look at $value every time instead of using a fixed value.

That's the beauty of prepared queries and bound parameters, you prepare and bind once, then execute multiple times.

-- edit -- I'd probably also throw a check to make sure $_POST['block'] isset and is_array

-- edit edit -- Might also be good to cross-check against a whitelist of valid values since one shouldn't blindly trust $_POST.

 

thank you very much even better!