Button writing to MYSQL-CodePudding

I have a html simple button that I am trying to connect to a MYSQL database, such that when the user presses it a 1 will be written to a table. The site is wordpress using elementor and the code will be called using a shortcode widget in elementor. I plan to use the php code snippet plugin, which allows you to enter your php and then generates a shortcode that one can call to execute the code.

So far, the button code just looks like:

<button style="background-color: red;" type="button">
Shutdown
</button>

The MYSQL table is very simple as well, there are only two columns one of which is just a function for timestamps. I would just be writing a 1 to a column called "command" which is of integer type.

The db info is below host : 'localhost', user : 'myuser', password : 'mypassword', database : 'motordata' table: 'MotorON'

How can I accomplish this? It seems I could use JavaScript or PHP. For a database insert using node.js I was using the code below, but is it viable for this application? I am seeing that PHP post function may also be a route.

var con = mysql.createConnection({
host     : 'localhost',
user     : 'myuser',
password : 'mypassword',
database : 'mydb'

//database connection
con.connect(function(err) {
    if (err) throw err;
    console.log("Connected!");

var sql = 'INSERT INTO beta (temperature, pressure) VALUES (' temp ', ' press ')';
con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");

CodePudding user response：

Here is an extremely simple PHP script that would accomplish what you're looking for:

<?php

//if the submit button is clicked, follow logic to insert record
if (isset($_POST['submit'])) {

    $insert_value = 1;

    //make a database connection
    $conn = mysqli_connect('localhost', 'myuser', 'mypassword', 'motordata');    

    //prepared statement to insert the record
    $stmt_insert_record = $conn->prepare("INSERT INTO MotorOn (command) VALUES (?)");
    $stmt_insert_record->bind_param("i", $insert_value);
    $stmt_insert_record->execute();
    $stmt_insert_record->close();
}
?>

<!-- Form that will submit to the document's address ie. the same page-->
<form method="post">
    <input type="submit" value="Shutdown" style="background-color: red;">
</form>

CodePudding user response：

The first answer by @Andrew led me in the right direction; however, it is not functional in its current form. It should be noted that while I was making the code modifications I went ahead and added an extra column called "extra" to my db table.

HTML button code:

<form method="post">
    <input name="submit123" type="submit" value="Shutdown" style="background-color: red;">
</form>

PHP code:

<?php
//if the submit button is clicked, follow logic to insert record
if (isset($_POST['submit123'])) {
    echo "button pressed";
    //make a database connection
    $conn = new mysqli('localhost', 'myuser', 'mypass', 'mydb');    
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
    // set parameters and execute
    $command = 1;
    $extra = 1;
    $stmt = $conn->prepare("INSERT INTO motoron2 (command, extra) VALUES (?,?)");
    $stmt->bind_param("ii", $command, $extra);

    $stmt->execute();
    echo "New records created successfully";

    $stmt->close();
    $conn->close();
    
}
?>

Because I am using a wordpress site and a code snippet plugin, which puts the php code into the theme's function.php, I have to add the php code into the code below. Then I will reference it in my webpage with the shortcode [phpshutdownkey]

function shutdownkey( $atts ) {
    //if the submit button is clicked, follow logic to insert record
    //insert php code from above here
}
add_shortcode('phpshutdownkey', 'shutdownkey');