How to reuse SQL query with a different row affected depending on button clicked

Question

So a have the below piece of code which i am trying to cut down the top section of code into just having one SQL query which is being ran with a different database record being affected each time (the id just needs to be changed each time).

Any help is appreciated, or links to sources where I can learn how to do this myself as I don't seem to be able to google the right thing :(

<!DOCTYPE html>
<html>
    <head>
    <?php 
        require_once 'db.php';

        if(isset($_POST['cyanxerox'])){
            $sth = $conn->prepare("UPDATE supplies SET quantity = quantity + 1 WHERE Id=1");
            $sth->execute();
            header('Location: index.php');
            die("Posted, now redirecting");

        }
        if(isset($_POST['magentaxerox'])){
            $sth = $conn->prepare("UPDATE supplies SET quantity = quantity + 1 WHERE Id=2");
            $sth->execute();
            header('Location: index.php');
            die("Posted, now redirecting");

        }
        if(isset($_POST['blackxerox'])){
            $sth = $conn->prepare("UPDATE supplies SET quantity = quantity + 1 WHERE Id=3");
            $sth->execute();
            header('Location: index.php');
            die("Posted, now redirecting");

        }
        if(isset($_POST['yellowxerox'])){
            $sth = $conn->prepare("UPDATE supplies SET quantity = quantity + 1 WHERE Id=4");
            $sth->execute();
            header('Location: index.php');
            die("Posted, now redirecting");

        }

    ?>
    <title>Homepage</title>ss" href="style/
    <link rel="stylesheet" type="text/cmain.css">
</head>
<body>
    <h1>ICT Support Printer Supplies Inventory</h1>
    <form method="POST" action="index.php">
        <input type="submit" name="cyanxerox" value="Cyan Xerox"/>
    </form>
    <form method="POST" action="index.php">
        <input type="submit" name="magentaxerox" value="Magenta Xerox"/>
    </form>
    <form method="POST" action="index.php">
        <input type="submit" name="blackxerox" value="Black Xerox"/>
    </form>
    <form method="POST" action="index.php">
        <input type="submit" name="yellowxerox" value="Yellow Xerox"/>
    </form>

Is it possible for more than one of the $_POST variables to be set? — Barmar
– Barmar, Commented Sep 15, 2017 at 22:03

score 1 · Accepted Answer · 2017-09-15 22:36:49Z

Try to process prepared statements in a complete way, e.g. by using proper validations and exception handling. You can achieve this only if you're reading the documentation of each PHP function you are using. Especially of the ones regarding database access operations and especially the "Return Values" part of the documentation.

You need only one form with four submit buttons. Each button contains the corresponding Id value. All buttons have the same name (I choosed "xerox").

I also added the three <meta> tags which should be present in the <head> of all your web pages.

Note that you have a falsely placed string near the <title> tag.

Good luck!

<?php
require_once 'db.php';

if (isset($_POST['xerox'])) {
    $xeroxId = $_POST['xerox'];

    try {
        // The sql statement - it will be prepared.
        $sql = 'UPDATE supplies 
                SET quantity = quantity + 1 
                WHERE Id = :Id';

        /*
         * Prepare and validate the sql statement.
         * If the database server cannot successfully prepare the statement, PDO::prepare() 
         * returns FALSE or emits PDOException (depending on error handling settings).
         */
        $statement = $conn->prepare($sql);
        if (!$statement) {
            throw new UnexpectedValueException('The sql statement could not be prepared!');
        }

        // Bind and validate the binding of the input parameter.
        $bound = $statement->bindValue(':Id', $xeroxId, PDO::PARAM_INT);
        if (!$bound) {
            throw new UnexpectedValueException('An input parameter could not be bound!');
        }

        /*
         * Execute the prepared statement.
         * PDOStatement::execute returns TRUE on success or FALSE on failure.
         */
        $executed = $statement->execute();
        if (!$executed) {
            throw new UnexpectedValueException('The prepared statement could not be executed!');
        }

        /*
         * If the form resides in index.php, then you don't need to do redirect,
         * but just to print a success message.
         */
        // header('Location: index.php');
        // exit();

        $message = 'Data successfully updated';
    } catch (PDOException $exc) {
        echo $exc->getMessage();
        // Only in development phase !!!
        // echo '<pre>' . print_r($exc, TRUE) . '</pre>';
        exit();
    } catch (Exception $exc) {
        echo $exc->getMessage();
        // Only in development phase !!!
        // echo '<pre>' . print_r($exc, TRUE) . '</pre>';
        exit();
    }
}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags *must* come first in the head -->

        <title>Homepage</title>

        <link rel="stylesheet" type="text/cmain.css">
    </head>
    <body>

        <?php
        if (isset($message)) {
            ?>
            <div class="post-message">
                <?php echo $message; ?>
            </div>
            <?php
        }
        ?>

        <h1>ICT Support Printer Supplies Inventory</h1>
        <form action="index.php" method="POST">
            <button type="submit" name="xerox" value="1">
                Cyan Xerox
            </button>
            <button type="submit" name="xerox" value="2">
                Magenta Xerox
            </button>
            <button type="submit" name="xerox" value="3">
                Black Xerox
            </button>
            <button type="submit" name="xerox" value="4">
                Yellow Xerox
            </button>
        </form>

    </body>
</html>

Hi, thank you for your response and putting time into explaining it and linking documents that i can read/learn from :) ill let you know how i get on with it!
@Jeff You are welcome. Feel free to ask us if you have any unclarities.

Collectives™ on Stack Overflow

How to reuse SQL query with a different row affected depending on button clicked

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related