status change on button click php mysql PDO

Question

I am trying to change the package status as received in MySQL table, i guess the action is not performing well, can someone please spot the error, i am pasting the code below.

When i am putting the action code inside the while loop, it changes the status to Received for all the records. But when i am putting it outside the while loop, nothing happens.

<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
    <thead>
        <tr>
            <th>Customer Email</th>
            <th>Shipping Company</th>
            <th>Delivery Date</th>
            <th>Tracking ID</th>
            <th>Destination Address</th>
            <th>Destination ZIP</th>
            <th>Mark As Recieved</th>

        </tr>
    </thead>
    <tbody>
    <?php
        require('config.php');
        $conn = new PDO("mysql:host=".$DB_HOST.";dbname=".$DB_NAME,$DB_USER,$DB_PASS);
        $sql = "SELECT * FROM packages_to_be_shipped_on_bremail_address";

        $q = $conn->prepare($sql);
        $q->execute();

        $q->bindColumn(2, $custemail);
        $q->bindColumn(3, $shipcompany);
        $q->bindColumn(4, $deliverydate);
        $q->bindColumn(5, $trackingid);
        $q->bindColumn(6, $destaddress);
        $q->bindColumn(7, $destzip);
        $q->bindColumn(8, $status);

        while($q->fetch()){                             
    ?>
        <tr class="odd gradeX">
            <td><?php echo $custemail ; ?></td>
            <td><?php echo $shipcompany; ?></td>
            <td><?php echo $deliverydate; ?></td>
            <td><?php echo $trackingid; ?></td>
            <td><?php echo $destaddress; ?></td>
            <td><?php echo $destzip; ?></td>
            <td>
            <?php 
            if($status == "Pending") {
                echo "
                    <form action='#' method='post' name='updatestatus'>
                    <input type='submit' name='submit' value='Mark As Recieved' />
                    </form> 
                ";
            }
            else {
                echo "Recieved";
            }
        }
            ?>                                                
            </td>
        </tr>
    <?php 
    $status = "Recieved";
    if(isset($_POST['submit'])){
        while($q->fetch()) {
            $sql = "UPDATE packages_to_be_shipped_on_bremail_address SET status=? WHERE cust_email=?";
            $q = $conn->prepare($sql);
            $q->execute(array($status,$custemail));
            header('Location:cust_orders.php');
        }
    }                                           
     ?>
    </tbody>
</table>

Shouldn't this start from `1`? `$q->bindColumn(2, $custemail);` — Funk Forty Niner, Apr 15 '14 at 12:48
@Fred-ii- most likely Column `1` is the row `id`, which the OP does not want to retrieve/return — Sean, Apr 15 '14 at 12:49
I was under the impression that PDO needs to follow a sequence @Sean — Funk Forty Niner, Apr 15 '14 at 12:50
@Fred-ii- not when using `bindColumn()` as this is binding the results from the query, not binding parameters to the query — Sean, Apr 15 '14 at 12:52
You need to add `$custemail;` to your form -> `
`. Then use that posted value in your query -> `$q->execute(array($status,$_POST['custemail']));`. — Sean, Apr 15 '14 at 12:54
Also, remove the loop `while($q->fetch()) {}` in the `isset($_POST['submit'])` as it is unnessesary as you are only getting 1 `$custemail` posted value at a time. — Sean, Apr 15 '14 at 12:56
@Sean i did follow all your instruction, it changes for all the records in the table, not for the particular one. — Rajesh Vishnani, Apr 15 '14 at 12:58
It would only update all if it is still in a loop, which you should remove, and if you did not specify as a specific value in the `WHERE`, which is with `$_POST['custemail']` in your execute. If those are changed there should be no way for it to update all records. — Sean, Apr 15 '14 at 13:05
`` `$q->execute(array($status,$_POST['custemail']));` i did these 2 changes and removed the while loop. — Rajesh Vishnani, Apr 15 '14 at 13:09
No, not the field. Do they all have the same value -> `id=1 cust_email=name@email.com, id=2 cust_email=name@email.com, etc.` If the `cust_email` is the same value for each record, then you will need to use the `id` or other unique value in your update. — Sean, Apr 15 '14 at 13:17

score 2 · Accepted Answer · answered Apr 15 '14 at 13:42

Please find the corrected code with the proper output.

 <table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
        <thead>
            <tr>
                <th>Customer Email</th>
                <th>Shipping Company</th>
                <th>Delivery Date</th>
                <th>Tracking ID</th>
                <th>Destination Address</th>
                <th>Destination ZIP</th>
                <th>Mark As Recieved</th>

            </tr>
        </thead>
        <tbody>
        <?php
            require('config.php');
            $conn = new PDO("mysql:host=".$DB_HOST.";dbname=".$DB_NAME,$DB_USER,$DB_PASS);
            $sql = "SELECT * FROM packages_to_be_shipped_on_bremail_address";

            $q = $conn->prepare($sql);
            $q->execute();

            $q->bindColumn(1, $pid);
            $q->bindColumn(2, $custemail);
            $q->bindColumn(3, $shipcompany);
            $q->bindColumn(4, $deliverydate);
            $q->bindColumn(5, $trackingid);
            $q->bindColumn(6, $destaddress);
            $q->bindColumn(7, $destzip);
            $q->bindColumn(8, $status);

            while($q->fetch()){                             
        ?>
            <tr class="odd gradeX">
                <td><?php echo $custemail ; ?></td>
                <td><?php echo $shipcompany; ?></td>
                <td><?php echo $deliverydate; ?></td>
                <td><?php echo $trackingid; ?></td>
                <td><?php echo $destaddress; ?></td>
                <td><?php echo $destzip; ?></td>
                <td>
                <?php 
                if($status == "Pending") {
                    echo "
                        <form action='#' method='post' name='updatestatus'>
                        <input type='hidden' name='pid' value='$pid'>
                        <input class='btn btn-inverse' type='submit' name='submit' value='Mark As Recieved'><i class='icon-refresh icon-white'></i></input> 
                        </form> 
                    ";
                }
                else {
                    echo "Recieved";
                }
            }
                ?>                                                
                </td>
            </tr>
        <?php 
        $status = "Recieved";
        if(isset($_POST['submit'])){                                            
            $sql = "UPDATE packages_to_be_shipped_on_bremail_address SET status=? WHERE package_id=?";
            $q = $conn->prepare($sql);
            $q->execute(array($status,$_POST['pid']));
            header('Location:cust_orders.php');                                         
        }                                           
         ?>
        </tbody>
    </table>

score 0 · Answer 2 · answered Apr 15 '14 at 13:19

You need to define email, try this:

<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
<thead>
    <tr>
        <th>Customer Email</th>
        <th>Shipping Company</th>
        <th>Delivery Date</th>
        <th>Tracking ID</th>
        <th>Destination Address</th>
        <th>Destination ZIP</th>
        <th>Mark As Recieved</th>

    </tr>
</thead>
<tbody>
<?php
    require('config.php');
    $conn = new PDO("mysql:host=".$DB_HOST.";dbname=".$DB_NAME,$DB_USER,$DB_PASS);
    $sql = "SELECT * FROM packages_to_be_shipped_on_bremail_address";

    $q = $conn->prepare($sql);
    $q->execute();

    $q->bindColumn(2, $custemail);
    $q->bindColumn(3, $shipcompany);
    $q->bindColumn(4, $deliverydate);
    $q->bindColumn(5, $trackingid);
    $q->bindColumn(6, $destaddress);
    $q->bindColumn(7, $destzip);
    $q->bindColumn(8, $status);

    while($q->fetch()){                             
?>
    <tr class="odd gradeX">
        <td><?php echo $custemail ; ?></td>
        <td><?php echo $shipcompany; ?></td>
        <td><?php echo $deliverydate; ?></td>
        <td><?php echo $trackingid; ?></td>
        <td><?php echo $destaddress; ?></td>
        <td><?php echo $destzip; ?></td>
        <td>
        <?php 
        if($status == "Pending") {
            echo "
                <form action='#' method='post' name='updatestatus'>
                <input type='submit' name='submit' value='Mark As Recieved' />
                <input type='hidden' name='cust_email' value='<?php echo $custemail; ?>' />
                </form> 
            ";
        }
        else {
            echo "Recieved";
        }
    }
        ?>                                                
        </td>
    </tr>
<?php 
$status = "Recieved";
if(isset($_POST['submit'])){
    while($q->fetch()) {
        $sql = "UPDATE packages_to_be_shipped_on_bremail_address SET status=? WHERE cust_email=?";
        $q = $conn->prepare($sql);
        $q->execute(array($status,$_POST['cust_email']));
        header('Location:cust_orders.php');
    }
}                                           
 ?>
</tbody>

If you want to do multiple update by one submit. For this you need create one form for all items, like this:

<form action='#' method='post' name='updatestatus'>
<table cellpadding="0" cellspacing="0" border="0" class="table table-striped table-bordered" id="example">
<thead>
    <tr>
        <th>Customer Email</th>
        <th>Shipping Company</th>
        <th>Delivery Date</th>
        <th>Tracking ID</th>
        <th>Destination Address</th>
        <th>Destination ZIP</th>
        <th>Mark As Recieved</th>

    </tr>
</thead>
<tbody>
<?php
    require('config.php');
    $conn = new PDO("mysql:host=".$DB_HOST.";dbname=".$DB_NAME,$DB_USER,$DB_PASS);
    $sql = "SELECT * FROM packages_to_be_shipped_on_bremail_address";

    $q = $conn->prepare($sql);
    $q->execute();

    $q->bindColumn(2, $custemail);
    $q->bindColumn(3, $shipcompany);
    $q->bindColumn(4, $deliverydate);
    $q->bindColumn(5, $trackingid);
    $q->bindColumn(6, $destaddress);
    $q->bindColumn(7, $destzip);
    $q->bindColumn(8, $status);

    while($q->fetch()){                             
?
    <tr class="odd gradeX">
        <td><?php echo $custemail ; ?></td>
        <td><?php echo $shipcompany; ?></td>
        <td><?php echo $deliverydate; ?></td>
        <td><?php echo $trackingid; ?></td>
        <td><?php echo $destaddress; ?></td>
        <td><?php echo $destzip; ?></td>
        <td>
        <?php 
        if($status == "Pending") {
            echo "
                <input type='checkbox' name='cust_email[]' value='<?php echo $custemail; ?>' />
            ";
        }
        else {
            echo "Recieved";
        }
    }
        ?>                                                
        </td>
    </tr>
    <tr class="odd gradeX">
        <td colspan="7">
                <input type='submit' name='submit' value='Mark As Recieved' />
        </td>
    <tr>
</tbody></table></form> 
<?php 
$status = "Recieved";
if(isset($_POST['submit'])){
    while($q->fetch()) {
        $sql = "UPDATE packages_to_be_shipped_on_bremail_address SET status=? WHERE cust_email=?";
        $q = $conn->prepare($sql);
        foreach($_POST['cust_email'] as $cust_email)
        $q->execute(array($status,$cust_email));
        header('Location:cust_orders.php');
    }
}                                           
 ?>

status change on button click php mysql PDO

2 Answers2