Execute Multiple MySQL Queries using PDO from PHP Post

Question

I have a PHP script to post the following data to add-rma-process.php after submission:

$_POST['rmanumber']
$_POST['parent']
$_POST['qty']

However, there are also other fields which are to be posted but will depend on the $_POST['qty'] variable. Say, the $_POST['qty'] = 5 then I will have $_POST['pn1'], $_POST['sn1'], $_POST['rm1'] up to $_POST['pn5'], $_POST['sn5'], $_POST['rm5']. I think you guys get the logic.

Once add-rma-process.php receives these data, I am doing this:

require("common.php");
for($i=0; $i<$_POST['qty']; $i++) {
    $count = $i+1; // to start with 1 instead of 0
    $query = "INSERT INTO rmadb (rmanumber, parent, childpn, childsn, remarks, user, date) VALUES (:rmanumber, :parent, :childpn, :childsn, :remarks, :user, NOW())";
    $query_params = array(
        ":rmanumber" => $_POST['rmanumber'],
        ":parent" => $_POST['parent'],
        ":childpn" => $_POST['pn$count'],
        ":childsn" => $_POST['sn$count'],
        ":remarks" => $_POST['rm$count'],
        ":user" => $_SESSION['user']['fname']." ".$_SESSION['user']['lname']
    );
        try { 
            $stmt = $db->prepare($query);
            $res = $stmt->execute($query_params);
        } catch(PDOException $ex) {
            die("Failed to run query: " . $ex->getMessage());
        }
}

What I was trying to do is do a for loop to execute the query until the condition is met but it is not working. What seems to be wrong?

You really should do away with your rm1, sn1, pn1 input field naming schema and just pass the data using array access notation (i.e. rm[], sn[], pn[]. This will cause all the input to be compiled into arrays $_POST['rm'], $_POST['sn'], $_POST['pn'] that would be much easier to work with in PHP. — Mike Brant
– Mike Brant, Commented Jan 15, 2014 at 1:20

peterm · Accepted Answer · 2014-01-15 01:15:21Z

You should use double quotes here so that key can be evaluated
```
$_POST["pn$count"]
       ^        ^ 
```
You don't need to introduce $count variable. Change condition in for
You should prepare your query once and then execute it multiple times with different parameters. That's the whole point behind prepared statements. Preventing sql injections is just a beautiful side effect.

That being said your might look something like this

require("common.php");

$query = "INSERT INTO rmadb (rmanumber, parent, childpn, childsn, remarks, user, date) VALUES (:rmanumber, :parent, :childpn, :childsn, :remarks, :user, NOW())";
$stmt = $db->prepare($query);
for ($i = 1; $i <= $_POST['qty']; $i++) {
    $query_params = array(
        ":rmanumber" => $_POST['rmanumber'],
        ":parent"    => $_POST['parent'],
        ":childpn"   => $_POST["pn$i"],
        ":childsn"   => $_POST["sn$i"],
        ":remarks"   => $_POST["rm$i"],
        ":user"      => $_SESSION['user']['fname']." ".$_SESSION['user']['lname']
    );
    $res = $stmt->execute($query_params);
}

Mike Brant · Accepted Answer · 2014-01-15 01:24:59Z

1

Anytime you see yourself naming inputs like rm1, rm2, etc. know that that this is a clear anti-pattern. You should be using array access notation for your input names like:

<input name="rm[]" ... />

PHP will automatically take all inputs with same name and compile into an array that is available in $_POST - so $POST['rm'] and so forth.

This would simplify you loop to something like

$count = $_POST['qty']; // not shown you probably want to validate this value before using it
for ($i = 0; $i < $count; $i++) {
    $query_params = array(
        ":rmanumber" => $_POST['rmanumber'],
        ":parent"    => $_POST['parent'],
        ":childpn"   => $_POST['pn'][$i],
        ":childsn"   => $_POST['sn'][$i],
        ":remarks"   => $_POST['rm'][$i],
        ":user"      => $_SESSION['user']['fname']." ".$_SESSION['user']['lname']
    );
    $res = $stmt->execute($query_params);
}

Note that since I am guessing you are using some kind of javascript in your form to create X number of input fields based on the value in qty, this saves you a lot of headache in javascript in trying to number each input field. You can easily just clone the same input field (or template for the input field) and insert it into the DOM X times without the need to individually change it's one's name property.

answered Jan 15, 2014 at 1:24

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

1 Comment

Chester Over a year ago

Thanks Mike. The values are generated by PHP as well. The user types in the quantity before the form is generated. Also, yes you are correct that I am validating the variables before doing anything. Thanks for the help...

Collectives™ on Stack Overflow

Execute Multiple MySQL Queries using PDO from PHP Post

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related