PHP update MySQL and export to csv

Question

I have a PHP page that shows pending claims which can have their status changed when a checkbox is selected and the array updated.

 <?php include("partials/header.php"); ?>            

<p>Manage expenses here!</p>    
<body>
 <style type="text/css">
#dis{
    display:none;
}
</style>    

    <div id="dis">
    <!-- here message will be displayed -->
    </div>

<p>Current Claims</p>    
    <div class="container">           
            <form method="post" action="export.php" enctype="multipart/form-data" >   

        <table cellspacing="0" width="100%" id="example" class="table table-striped table-hover table-responsive">
        <thead>
        <tr>
        <th>ID</th>
        <th>Employee ID</th>
        <th>Ref</th>
        <th>Date</th>
        <th>Filed Date</th>
        <th>Type</th>
        <th>Client</th>
        <th>Project</th>
        <th>Narrative</th>
        <th>Net</th>
        <th>VAT</th>
        <th>Status</th>
        <th></th>
        </tr>
        </thead>
        <tbody>
    <?php
//connection details       
require_once 'dbconfig.php';
//end connection details

$query = "SELECT * FROM claim WHERE status='pending'";
$result = mysql_query($query);

if($result === false) { 
    die(mysql_error());
                      }           

while($row = mysql_fetch_array($result)){
    $row_id = $row['c_id'];
    $employee_id = $row['employee_id'];
    $claim_ref = $row['claim_ref'];
    $transaction_date = $row['transaction_date'];
    $filed_date = $row['filed_date'];
    $expense_type_id = $row['expense_type_id'];
    $client_code_id = $row['client_code_id'];
    $project_code_id = $row['project_code_id'];
    $narrative = $row['narrative'];
    $net = $row['net'];
    $vat = $row['vat'];
    $status = $row['status'];       

            echo
            "<tr>
            <td>" . $row_id . "</td>
            <td>" . $employee_id . "</td>
            <td>" . $claim_ref . "</td>
            <td>" . $transaction_date . "</td>
            <td>" . $filed_date . "</td>
            <td>" . $expense_type_id . "</td>
            <td>" . $client_code_id . "</td>
            <td>" . $project_code_id . "</td>
            <td>" . $narrative . "</td>
            <td>" . $net . "</td>
            <td>" . $vat . "</td>
            <td>" . $status . "</td>
            <td><input type='checkbox' name='c_id[]' value='" . $row_id . "'/></td>
            </tr>";
            }

        echo "</tbody></table><input type='submit' value='accept'></form>"; //end form

/***** Update Status *****/

/*print_r($_POST);*/
    if(gettype($_POST['c_id'])=="array"){
        foreach($_POST['c_id'] as $val){
         $id_c = $val;
         $query2 = "UPDATE claim SET status = 'accepted' where c_id='".$id_c."'";
         $result2 = mysql_query($query2);
         if($result2 === false) { 
            die(mysql_error());
         }
         echo "Status " .$id_c. " is updated. <br>";
        }
    }   
    mysql_close();

        ?>
        </tbody>
        </table>            
        </div>    
    </div>        
    <br />

</script>
</body>
</html>

Q1. I was wondering whether the rows which are having the status column changed to accepted, can somehow also be exported to a csv file.

Q2. Is there a way to have two buttons, one for accepting and the other one for rejecting as each claim has three possible statuses: pending, accepted, rejected

Update: the claim table is as follows: http://sqlfiddle.com/#!9/5ce91

   create table claim (
   c_id INT(5) zerofill primary key auto_increment, 
   claim_ref VARCHAR(7),
   employee_id integer NOT NULL REFERENCES employee(emp_id),
   transaction_date DATE,
   filed_date DATE,
   expense_type_id VARCHAR(20) REFERENCES expense_type(expense_type),
   client_code_id VARCHAR(7) REFERENCES client(client_code),
   project_code_id VARCHAR(10) REFERENCES project(project_code),
   narrative VARCHAR(255),
   net decimal(6,2), 
   vat decimal(6,2),
   status ENUM('pending', 'rejected', 'accepted'))
   ENGINE = InnoDB;

Another bit is that the project ends in about a week so I'd rather not get into updating to PDO or MySQLi although if anyone has a complete solution in one of these it would be just as good, thanks.

***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 31 '16 at 21:13
If you don't know how to create csv: http://stackoverflow.com/questions/4249432/export-to-csv-via-php. — jaro1989, Aug 31 '16 at 21:31
@jjj We need DB as nogad said, SQL fiddle like: http://sqlfiddle.com/#!9/a11b4/13 — Jitesh Sojitra, Sep 03 '16 at 11:28

PHP update MySQL and export to csv

0 Answers0