Jump to content

I need a button to change value in mysql from 0 to 1


dioti

Recommended Posts

I don't know many things in php yet so i need help to find out how to set a button to change value of "Confirmed" row from 0 to 1 in table "sales". I commented "This Button" at the beginning of the button i want to do the process.

<?php
                    $conn = $pdo->open();

                    try{
                      $stmt = $conn->prepare("SELECT *, sales.id AS salesid FROM sales LEFT JOIN users ON users.id=sales.user_id ORDER BY sales_date DESC");
                      $stmt->execute();
                      foreach($stmt as $row){
                        $stmt = $conn->prepare("SELECT * FROM details LEFT JOIN products ON products.id=details.product_id WHERE details.sales_id=:id");
                        $stmt->execute(['id'=>$row['salesid']]);
                        $total = 0;
                        foreach($stmt as $details){
                          $subtotal = $details['price']*$details['quantity'];
                          $total += $subtotal;
                        }
                        if($row['Confirmed'] == 0){
                        echo "
                          <tr>
                            <td class='hidden'></td>
                            <td>".date('M d, Y', strtotime($row['sales_date']))."</td>
                            <td>".$row['firstname'].' '.$row['lastname']."</td>
                            <td>".$row['country'].' , '.$row['city']." , ".$row['address']."</td>
                            <td width='25px;'>&#8378; ".number_format($total, 2)."</td>
                            <td width='18px;'><button type='button' class='btn btn-info btn-sm btn-flat transact' data-id='".$row['salesid']."'><i class='fa fa-search'></i> View</button></td>
                            <td width='18px;'>
                            <!-- This Button --> <button type='button' class='btn btn-success btn-sm btn-flat confirm' data-id='".$row['salesid']."'><i class='fa fa-check'></i> Confirm</button></td>
                          </tr>
                         
                        ";
                        }
                      }
                    }
                    catch(PDOException $e){
                      echo $e->getMessage();
                    }

                    $pdo->close();
                  ?>

 

Link to comment
Share on other sites

Do not run queries in a loop like that where you are using each row from one query to provide the value to do another select query.

You apparently are aware of joins, so just do a single query using another join.

If you are concerned with rows where confirmed == 0, put that condition in your query instead of retrieving every row and the ignoring those you don't want.

Also, don't use select *. Specifiy the columns you require. (i would rewrite the query for you but, because you  used "*", I don't know what you are selecting from which tables.)

Now to your question...

Put the output in a form so it can be submitted to the server to update the records.

Link to comment
Share on other sites

@Barand thank you, i already found the solution few mins ago, but can you write me a code or tell me how to put the condition to my query ?

new code:

<?php
                    $conn = $pdo->open();

                    try{
                      $stmt = $conn->prepare("SELECT *, sales.id AS salesid FROM sales LEFT JOIN users ON users.id=sales.user_id ORDER BY sales_date DESC");
                      $stmt->execute();
                      foreach($stmt as $row){
                        $stmt = $conn->prepare("SELECT * FROM details LEFT JOIN products ON products.id=details.product_id WHERE details.sales_id=:id");
                        $stmt->execute(['id'=>$row['salesid']]);
                        $total = 0;
                        foreach($stmt as $details){
                          $subtotal = $details['price']*$details['quantity'];
                          $total += $subtotal;
                        }
                        if($row['Confirmed'] == 0){
                        echo "
                          <tr>
                            <td class='hidden'></td>
                            <td>".date('M d, Y', strtotime($row['sales_date']))."</td>
                            <td>".$row['firstname'].' '.$row['lastname']."</td>
                            <td>".$row['country'].' , '.$row['city']." , ".$row['address']."</td>
                            <td width='25px;'>&#8378; ".number_format($total, 2)."</td>
                            <td width='18px;'><button type='button' class='btn btn-info btn-sm btn-flat transact' data-id='".$row['salesid']."'><i class='fa fa-search'></i> View</button></td>
                            <form action='' method='post'>
                            <td width='18px;'>
                            <input type='hidden' name='confirm' value='1'> <input class='btn btn-success btn-sm btn-flat' type='submit' value='Confirm'>
                            </td>
                            </form>
                          </tr>
                         
                        ";
                        if(isset($_POST['confirm']) && $_POST['confirm'])
                        {
                          $stmt = $conn->prepare("UPDATE sales SET Confirmed = 1 WHERE id = '".$row['salesid']."'");                                                         
                          $stmt->execute();  
                                                
                        }
                        }
                      }
                    }
                    catch(PDOException $e){
                      echo $e->getMessage();
                    }

                    $pdo->close();                    
                  ?>

 

Link to comment
Share on other sites

Here's another solution

<?php
include 'db_inc.php';
$db = pdoConnect();

###
### PROCESS POSTED DATA
###
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $stmt = $db->prepare("UPDATE sales
                          SET confirmed = 1
                          WHERE id = ?
                         ");
    foreach ($_POST['confirm'] as $id => $v) {
        $stmt->execute([ $id ]); 
    }
    header("Location: #");    // reload the page and refresh html table
    exit;
}
###
###  GET THE DATA TO DISPLAY
###
$res = $db->query("SELECT  DATE_FORMAT(s.sales_date, '%b %d, %Y') as date
                         , CONCAT(u.firstname, ' ', u.lastname) as name
                         , CONCAT_WS(', ', u.country, u.city, u.address) as address
                         , FORMAT(SUM(d.quantity * p.price), 2) as total
                         , s.id as salesid
                    FROM sales s
                         INNER JOIN users u ON s.user_id = u.id
                         INNER JOIN details d ON s.id = d.sales_id
                         INNER JOIN products p ON d.product_id = p.id
                    WHERE s.confirmed = 0 
                    GROUP BY s.id
                    ");
                    
$tdata = '';
foreach ($res as $r) {
    $tdata .= "<tr><td>{$r['date']}</td>
                   <td>{$r['name']}</td>
                   <td>{$r['address']}</td>
                   <td class='ra'>{$r['total']}</td>
                   <td><input type='checkbox' name='confirm[{$r['salesid']}]' value='1'></td>
              ";
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Example</title>
<style type='text/css'>
    body, table {
        font-family: verdana, sans-serif;
        font-size: 10pt;
    }
    table {
        border-collapse: collapse;
        width: 80%;
        margin: 0 auto;
    }
    #header {
        background-color: indigo;
        color: white;
        margin-bottom: 32px;
        padding: 16px;
    }
    th {
        padding: 8px;
        background: blue;
        color: white;
    }
    td {
        padding: 4px 8px;
    }
    td.ra {
        text-align: right;
    }
</style>
</head>
<body>
<div id='header'>
    <h1>Example Sales Confirmation</h1>
</div>
<form method='POST'>
    <table border='1'>
        <tr><th>Date</th>
            <th>Name</th>
            <th>Address</th>
            <th>Total</th>
            <th>Confirm</th>
        </tr>
        <?=$tdata?>
        <tr><td colspan='4'>&nbsp;</td>
            <td><button type='submit'>Submit</button></td>
        </tr>
    </table>
</form>
</body>
</html>

[edit] PS It assumes your data looks like this

users
    id        ----+
    firstname     |
    lastname      |
    address       |
    city          |
    country       |
                  |
                  |     sales                                                            products
                  |         id         -----+                                  +--------     id
                  +----<    user_id         |                                  |             price
                            sales_date      |                                  |
                            confirmed       |                                  |
                                            |           details                |
                                            |               id                 |
                                            +----------<    sales_id           |
                                                            product_id >-------+
                                                            quantity

 

Edited by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.