While loop to insert values into database?

Question

I currently have a form where I can subscribe and unsubscribe users from newsletters wich looks like this:

enter image description here

The code for the bottom part (the subscriptions part) is this:

 <?php 
$i = 0;  
while($objResult1 = mysql_fetch_array($objQuery1))  
 {  
$i++;  
?>  
<tr>  
   <td><div align="center"><?=$objResult1["ID"];?><input type="hidden" name="mailid" value="<?=$objResult1["ID"];?>"> </div></td>
   <td><div align="center"><?=$objResult1["Titel"];?> </div></td>  
   <td><div align="center"><input type="checkbox" name="sub" value="10"> </div></td>  
   <td><div align="center"><input type="checkbox" name="sub" value="90"> </div></td>
</tr>  
<?php  
 }  
?>

And the code to insert the values into the database is this:

<?php
mysql_connect('localhost','root','root');
mysql_select_db('NAW') or die (mysql_error());

$Klant_ID = $_POST['klantid'];
$Mail_ID  = $_POST['mailid'];
$Status   = $_POST['sub'];
$Datum    = date("d-m-Y");


$sql = mysql_query(
    "INSERT INTO Subscriptions (
        Klant_ID,
        Mail_ID,
        Status,
        Datum
    ) VALUES (
        '".$Klant_ID."',
        '".$Mail_ID."',
        '".$Status."',
        '".$Datum."'
    )"
);

if ($sql === false) {
    die (mysql_error());
} else {
    echo 'Je gegevens zijn succesvol in de database geplaatst.<br><br>Om de gegevens uit de database te bekijken klik <A HREF="klanten.php">hier</A>. <br>Om meer gegevens in te voeren klik <A HREF="index.html">hier</A>.';
}
?>

The database is:

ID   Klant_ID  Mail_ID  Status Datum 
1     6        1               test
2     6        1        test   test
3     10       10       10     19-03-13
16    6        4        90     20-03-2013
17    6        4        10     20-03-2013
18    7        4        90     20-03-2013

Now it will just add 1 row to the database where I checked a checkbox. I want to get it working so it will add 3 rows to the database, for example: When I select the boxes like this: enter image description here

I want these rows to be added to the database:

ID  Klant_ID  Mail_ID  Status  Datum
20  6         1        10      20-03-2013
21  6         2        90      20-03-2013
22  6         4        10      20-03-2013

So my question is if this is posible using a while loop with mysql_fetch_array? I am not a pro in php cause I'm still a student and I can't solve this problem on my own. I hope my question is clear enough but if you have any question just ask in the comments^^ If anyone can show me how it is done or push me in the right direction it would be great!

(I know I shouldn't be using mysql_* anymore but that is not the point here^^)

Simply do a while($row=mysql_fetch_array($result)){ mysql_query("insert query".$row["values"].")") } — Suyash, Mar 20 '13 at 08:43
BTW have you considered using radio buttons instead of checkboxes? A user cannot be both subscribed and unsubscribed, right? — Haralan Dobrev, Mar 20 '13 at 08:44
There is nothing to fetch here, since you are just adding data to the database. — Jocelyn, Mar 20 '13 at 08:45
Your code is vulnerable to SQL injections. Read [How to prevent SQL injection in PHP?](http://stackoverflow.com/q/60174/1409082) to know how to prevent that. — Jocelyn, Mar 20 '13 at 08:46
There are plenty of questions about how to insert data in a mysql database. One of them is: [insert a php array into mysql](http://stackoverflow.com/q/4028037/1409082) — Jocelyn, Mar 20 '13 at 08:49

Trolley · Answer 1 · 2013-03-20T08:50:51.620

You can just loop through the $_POST['sub'] and do your insert in that loop.

First, you have to make sub an array, so replace name="sub" with name="sub[]"

Now you can easily loop through the checked values.

foreach($_POST['sub'] as $subscription){
    mysql_query("INSERT INTO Subscriptions (Klant_ID, Mail_ID, Status, Datum) VALUES   ('".$Klant_ID."', '".$Mail_ID."', '".$subscription."', '".$Datum."')") or die (mysql_error());
}

I would advice you not to use mysql_ functions, as they are deprecated. Look into MySQLi or PDO (my favorite) instead. :)

With PDO, you could just prepare the query before the loop, and then execute it inside the loop. That way, PHP only have to prepare the query once.

$insert = $db->prepare("INSERT INTO Subscriptions (Klant_ID, Mail_ID, Status, Datum) VALUES   (?, ?, ?, ?)")
foreach($_POST['sub'] as $subscription){
    $db->execute(array($Klant_ID, $Mail_ID, $subscription, $Datum));
}

That way, your script would also be injection-safe, as with your current query, it is very vulnerable for injections.

score 2 · Accepted Answer · answered Mar 20 '13 at 08:49

2

Change:

<input type="checkbox" name="sub" value="10">
<input type="hidden" name="mailid" value="<?=$objResult1["ID"];?>">

to:

<input type="checkbox" name="sub[]" value="10">
<input type="hidden" name="mailid[]" value="<?=$objResult1["ID"];?>">

and do in php:

$sql = mysql_query("INSERT INTO Subscriptions (Klant_ID, Mail_ID, Status, Datum) VALUES   ('".$Klant_ID."', '".$Mail_ID."', '".$Status."', '".$Datum."')") or die (mysql_error());

to:

foreach($_POST['sub'] as $i=>$s){
    $sql = mysql_query("INSERT INTO Subscriptions (Klant_ID, Mail_ID, Status, Datum) VALUES   ('".$Klant_ID."', '".$_POST['mailid'][$i]."', '".$s."', '".$Datum."')") or die(mysql_error());
}

NOTE: This is solution is WITHOUT injection and other checks , and created just to show you direction to think.

answered Mar 20 '13 at 08:49

Narek

3,813
4
42
58

This solution may cause a problem when one of the user is neither subscribed nor unsubscribed. The mail id will shift and insert wrong values for wrong mailids. – Savas Vedova Mar 20 '13 at 08:56
You just solved my problem, thnx!^^ – Daanvn Mar 20 '13 at 08:56
@SavasVedova you're right, this is a very bad solution, but this is just direction to think about arrays in inputs. – Narek Mar 20 '13 at 10:34

While loop to insert values into database?

2 Answers2