HTML button to run SQL command

Question

I'm a bit of a noob at php/sql (well all languages in general), I am trying to get an sql command to run on the click of a html button.

The sql command that I am trying to run is UPDATE supplies SET quantity = quantity + 1 WHERE Id=1

This is what I have at the moment:

dp.php

<?php
$servername = "localhost";
$dbname = "tonor";
$username = "root";
$password = "password";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }
?>

index.php

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

        if(isset($_POST['data'])){
            $sth = $conn->prepare("UPDATE supplies SET quantity = quantity + 1 WHERE Id=1");
            $sth->execute();
        }
    ?>
    <title>Homepage</title>
    <link rel="stylesheet" type="text/css" href="style/main.css">
</head>
<body>
    <h1>testpage</h1>
    <form method="POST" action="index.php">
        <input type="submit" name="data" value="1"/>
    </form>

From the code it seems that you don't understand that PHP runs in the server while HTML is processed in the browse, that is, first the server process the PHP code and then send the result to the browser. The classic way of making something like what you want is via POST in a two-step process, search how to process POST request in PHP. — Alberto Martinez
– Alberto Martinez, Commented Sep 1, 2017 at 21:36

Stark · Accepted Answer · 2017-09-01 21:44:23Z

2

You should try this out, but you need to read, and practice further.

PDO manual: http://php.net/manual/en/book.pdo.php

It's a lot to look at, but just take a look at their examples to get an idea of how it works.

Read this as well to make it easier: https://code.tutsplus.com/tutorials/why-you-should-be-using-phps-pdo-for-database-access--net-12059

PHP is a server side language.

I any case, the following should be enough to get you started.

 <?php
    $servername = "localhost";
    $dbname = "databasename";
    $username = "username";
    $password = "password";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    } catch(PDOException $e) {
        echo "Connection failed: " . $e->getMessage();
    }

    return $conn;
?>


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

        if(isset($_POST['data'])){
            $sth = $conn->prepare("UPDATE supplies SET quantity = quantity + 1 WHERE Id=1");
            $sth->execute();
        }
    ?>
    <title>Homepage</title>
    <link rel="stylesheet" type="text/css" href="style/main.css">
</head>
<body>
    <h1>testpage</h1>
    <form method="POST" action="">
        <input type="submit" name="data" value="1"/>
    </form>

</body>
</html>

answered Sep 1, 2017 at 21:44

Stark

59411 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Over a year ago

Hi john, thanks for your reply, it works fine apart from the fact that every time i reload the page it runs the sql query, i searched it on google and all i could seem to find was it saying that i need to if(isset($_POST['data'])){ SQLQUERY HERE } Which was already done by you, is this just me missing something. I have updated the origonal post with the code that i am using now.

Alberto Martinez Over a year ago

When you say reload, you mean pressing F5 or opening the page in a new tab/window? Because if you posted the data and then reload the page with F5 the browser could post the data again (although usually it shows a warning about it)

Eddie Kaka Svensson · Accepted Answer · 2017-09-01 23:39:46Z

1

I am answering on john's post.

The reason it executes every time you reload the page is because the browser resends the post data to the server. You need to use header() to redirect on post. Your code is not perfect but it works. You should organize it. Maybe place all database logic in a class. An example of redirection that works for this example.

<?php 
    if(isset($_POST['data']))
    {
        require 'db.php';
        $sth = $conn->prepare("UPDATE supplies SET quantity = quantity + 1 WHERE Id=1");
        $sth->execute();
        header("Location: {$_SERVER['SERVER_NAME'].$_SERVER['REQUEST_URI']}");
        die("Posted, now redirecting");
    }
?>

answered Sep 1, 2017 at 23:39

Eddie Kaka Svensson

297 bronze badges

Collectives™ on Stack Overflow

HTML button to run SQL command

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related