Issue getting multiple data to insert into db

Question

I am having issues getting the record to insert multiple data. It only wants to insert the last item. I have in the while loop quantity, item_name, amount, and item number. So if there is 3 items I need all 3 to be inserted along with the user information lanId, employee_name, department, cost_center.

action.php

<form action="test.php" method="post">';      

                      $uid = $_SESSION["uid"];
                      $sql = "SELECT * FROM cart WHERE user_id = '$uid'";
                      $run_query = mysqli_query($con,$sql);
                      while($row=mysqli_fetch_array($run_query)){
                          $x++;
                     echo  

                     '<br>'.'<input type="text" name="item_name" value="'.$row["product_title"].'">
                      <input type="text" name="quantity" value="'.$row["qty"].'">
                      <input type="text" name="amount" value="'.$row["price"].'">
                      <input type="text" name="item_number" value="'.$x.'">';
                    }


                      echo"<br>
                                 <label>Lan ID</label>
                                    <input type='text' name='lanId' id='lanId'  autocomplete='off'  class='form-control' >
                                <label>Employee Name</label>
                                    <input type='text' name='employee_name' id='name'   autocomplete='off'  class='form-control'>
                                <label>Department</label>
                                    <select name='department' id='department'  class='form-control'>
                                        <option value =''>Select Department...</option>
                                        <option value ='OTHER'> OTHER</option>

                                    </select>

                            <label>Cost Center</label>
                            <input type='text' class='form-control' name='cost_center' value=''>
                            <label>Total amount</label>
                            <input type='text' class='form-control' name='total_amt' value='$total_amt'>
                            <br><br><br>
                            <input type='submit' class='btn btn-primary' value='Submit'>

                        </form>";

test.php

    <?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
include('db.php');

$item_name = $_POST["item_name"];
$amount = $_POST["amount"];
$quantity = $_POST['quantity'];
$lanId = $_POST["lanId"];
$employee_name = $_POST["employee_name"];
$department = $_POST['department'];
$cost_center = $_POST['cost_center'];
$total_amt = $_POST['total_amt'];

$sql = "INSERT INTO `order` 
        ( `item_name`, `amount`, `quantity`, 
        `lanId`, `employee_name` , `department`, `cost_center`, `total_amt`) 
        VALUES ('$item_name', '$amount', '$quantity', 
        '$lanId', '$employee_name', '$department', '$cost_center','$total_amt')";
        $run_query = mysqli_query($con,$sql);
        if($run_query){
            echo "
                <div class='alert alert-success'>
                    <a href='http://a0319p528/project2/profile.php' class='close' data-dismiss='alert' aria-label='close'>&times;</a>
                <b>data inserted successfully..!</b>
                </div>
            ";
        }

        ?>

`cost_center\`,,` in the columnlist, one too many commas. Remove that. In the future, you should use [`mysqli_error()`](http://php.net/manual/en/mysqli.error.php) to get the actual error from MySQL, it makes everyones life a hell of a lot easier than playing the "guessing game" :-) — Qirel, Sep 12 '17 at 18:26
You're already using an API that supports **prepared statements** with bounded variable input, you should utilize parameterized queries with placeholders (prepared statements) to protect your database against [SQL-injection](http://stackoverflow.com/q/60174/)! Get started with [`mysqli::prepare()`](http://php.net/mysqli.prepare) and [`mysqli_stmt::bind_param()`](http://php.net/mysqli-stmt.bind-param). — Qirel, Sep 12 '17 at 18:27
@Quirel I fixed that. It was not the issue I just added that extra field while I was typing the question — Donny, Sep 12 '17 at 18:30
You should **always** show us your exact code. If you don't, there might be issues we're not seeing because of it - or you may have had a copy/pasta error (like now?), which fools us that way. Like I said in the first comment, enable proper error-reporting. Also PHP error-reporting, `error_reporting(E_ALL); ini_set('display_errors', 1);`. It might also be an issue that can be fixed by using prepared statements instead of injecting variables directly into the query. — Qirel, Sep 12 '17 at 18:31
I am getting error with quantity and total amount it is saying undefined error. Once those are fix I still think it will not insert — Donny, Sep 12 '17 at 18:33
How can you know that without trying? ;-) Fix those undefined indexes, and get MySQL to throw your error at you upon failure. You can add `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` that forces MySQLi to throw exceptions (that you can catch), those you cannot ignore. — Qirel, Sep 12 '17 at 18:35
(Some of) Your input name attributes and POST arrays don't match for one thing. — Funk Forty Niner, Sep 12 '17 at 18:35
ok got it working except it is only grabbing only the last item not all the items, also total amount didn't work I got error message on that so I removed total amount just to test. I would like to also insert total amount. PHP Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Unknown column 'total_amt' in 'field list'' in C:\inetpub\wwwroot\project2\test.php:19 Stack trace: #0 C:\inetpub\wwwroot\project2\test.php(19): mysqli_query(Object(mysqli), 'INSERT INTO `or...') #1 {main} thrown in C:\inetpub\wwwroot\project2\test.php on line 19 — Donny, Sep 12 '17 at 18:45
Well there you go! MySQL can't find the `total_amt` column, so most likely it's named something else. — Qirel, Sep 12 '17 at 18:48
what about the issue with the while loop to get all the data instead of the last one. I figured total amount not in the db I am making such stupid mistakes again sorry — Donny, Sep 12 '17 at 18:50
@Donny Since the question just got closed (with I voted to re-open, as you have edited and improved your question), I'm unable to post an answer. But what you need is to use "HTML arrays" (even though there really isn't such a thing) and utilzie a prepared statement. Have a look at this pastebin: https://pastebin.com/pdhgESnc - I can post it as an answer with better explanation should the question be reopened. — Qirel, Sep 12 '17 at 19:45
Sadly, you're not in a position to re-open this question. It needs to go through community review. The important thing is that you get it working, eh? :-) Have a look at the pastebin and let me know if you run into issues. — Qirel, Sep 12 '17 at 19:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/154267/discussion-between-donny-and-qirel). — Donny, Sep 12 '17 at 19:50

score 2 · Accepted Answer · answered Sep 13 '17 at 12:19

The first issue is that you're only getting the last inputs from the form, because you overwrite them in each iteration of your loop. You can treat them as "HTML arrays", by having name="amount[]". This will make $_POST['amount'] an array and not a single string, which you can then loop over when the form is submitted and handled in PHP.

<input type="text" name="item_name[]" value="'.$row["product_title"].'">
<input type="text" name="quantity[]" value="'.$row["qty"].'">
<input type="text" name="amount[]" value="'.$row["price"].'">
<input type="text" name="item_number[]" value="'.$x.'">';

Furthermore, you should use prepared statements instead of a regular query, for two reasons

It will handle all quoting issues internally, so you don't have to worry about it. This in turn prevents SQL injection (a security improvement)
You will be able to execute the same query multiple times with different values

Using a prepared statement, you can boil down your query to what's shown below. This will loop over the elements that have multiple values (like shown above) and run a query for each iteration.

$lanId = $_POST["lanId"];
$employee_name = $_POST["employee_name"];
$department = $_POST['department'];
$cost_center = $_POST['cost_center'];
$total_amt = $_POST['total_amt'];

$stmt = $con->prepare("INSERT INTO `order` (`item_name`, `amount`, `quantity`, `lanId`, `employee_name` , `department`, `cost_center`, `total_amt`)
                                            VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
foreach ($_POST['item_name'] as $key=>$item_name) {
    $amount = $_POST['amount'][$key];
    $quantity = $_POST['quantity'][$key];

    $stmt->bind_param("ssssssss", $item_name, $amount, $quantity, $lanId, $employee_name, $department, $cost_center, $total_amt);
    $stmt->execute();
}
if ($stmt->affected_rows) {
    echo "
            <div class='alert alert-success'>
                <a href='http://a0319p528/project2/profile.php' class='close' data-dismiss='alert' aria-label='close'>&times;</a>
            <b>data inserted successfully..!</b>
            </div>
        ";
}
$stmt->close();

Issue getting multiple data to insert into db

1 Answers1