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

[dioti]

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();
                  ?>

 

[Barand]

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.

[dioti]

@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();                    
                  ?>

 

[Barand]

3 minutes ago, dioti said:

or tell me how to put the condition to my query

You use a WHERE clause, just like you have in your UPDATE query.

There is an SQL tutorial link in my signature.

[dioti]

@Barand ooh true, sorry it's pretty late here so i forgot about WHERE clause, but where should i add it though ? sorry for my questions, it's just that this is the only function left and i want to finish this so i can get back to javascript and python again.

[Barand]

Just now, dioti said:

but where should i add it though ?

I have no idea. Your continued use of "SELECT * " completely obfuscates your table structures.

Also, that "solution" that you found is far from optimal.

[Barand]

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 November 2, 2020 by Barand