Get info from MariaDB in PHP

user_15083173 0
Viewed 354

I have a code in PHP just to store a value in a simple database with MariaDB.

This is my table:

create table test(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(255) UNIQUE,
    date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
    ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

This is the PHP code:

<meta http-equiv="refresh" content="6;http://10.15.34.194/form.html">

<?php
$host = "localhost";
$db_name = "XXX";
$username = "XXX";
$password = "XXX";
$connection = null;

try{
    $connection = new PDO("mysql:host=" . $host . ";dbname=" . $db_name, $username, $password);
    $connection->exec("set names utf8");
}catch(PDOException $exception){
    echo "Connection error: " . $exception->getMessage();
}

function saveData($name){
    global $connection;
    $query = "INSERT INTO test(name) VALUES( :name )";
    $callToDb = $connection->prepare( $query );
    $name=htmlspecialchars(strip_tags($name));
    $callToDb->bindParam(":name", $name);

    if (!$callToDb->execute()) {
        $affected_rows = $callToDb->rowCount();

        if ($affected_rows == 0) {
            printf('<h1> register: ' . $name . ' ALREADY IN TABLE </h1>');
        }
    } else { 
        printf('<h1> SAVING RESULT ' . $name .  '</h1>'); 
    }
}


if( isset($_POST['submit'])){
    $name = htmlentities($_POST['name']);
    $result = saveData($name);
    echo $result;
}
else{
    echo '<h3 style="text-align:center;">ERROR</h3>';
}
?>

I just would like to show in this printf the timestamp from database when the value was stored.

printf('<h1> register: ' . $name . ' ALREADY IN TABLE </h1>');

For now what I tried is just to register a value that when it is repeated, only shows the name and warning that is alrady in the table. But i would also like to show the timestamp that is being generated in my table (and stored in clumn "date").

I tried creating a second query but code is getting broken.

I would like to create a printf similar to this one:

printf('<h1> register: ' . $name . ' ALREADY IN TABLE </h1>' '<h1> timestamp : ' . $date . '</h1>);

Not sure how I can create a second dabatase connection and how to recreate a new query with $name variable to get the timestamp for the repeated variable and include both $name variable and $date.

mariadb linux mysql php
2 Answers

You can use try {} catch () {} approach:

function saveData($connection, $name){
    //global $connection;
    
    $query = "INSERT INTO test(name) VALUES( :name )";
    $callToDb = $connection->prepare( $query );
    $name=htmlspecialchars(strip_tags($name));
    $callToDb->bindParam(":name", $name);

    try {
        $callToDb->execute();
        printf('<h1> register: ' . $name . ' </h1>');
    } catch (PDOException $e) {
        $error = $e->errorInfo;
        printf('<h1> SAVING RESULT ' . $error[2] .  '</h1>'); 
    }
}

share PHP code

user_33993560

With this query you can select it from the database.


<?php
function saveData($connection, $name){
    $query = "INSERT INTO test(name) VALUES( :name )";
    $callToDb = $connection->prepare( $query );
    $name=htmlspecialchars(strip_tags($name));
    $callToDb->bindParam(":name", $name);

    try {
        $callToDb->execute();
        printf('<h1> register: ' . $name . ' </h1>');
    } catch (PDOException $e) {
        $error = $e->errorInfo;
        printf('<h1> SAVING RESULT ' . $error[2] .  '</h1>'); 
    }
    $query = "SELECT date FROM test WHERE name = ?";
    $result = $connection->prepare($query);
    try {
    $result->execute([$name]);
    $date = $result->fetch();
    printf('<h1> on ' . $date . '</h1>');
    }catch(PDOException $e){
        $error = $e->errorInfo;
        printf('<h1> SAVING RESULT ' . $error[2] .  '</h1>'); 
    }
}
//just to test the connection
$name = 'Gerrit';
$result = saveData($connection, $name);

My PHP Code / Tests

Credits: @Slava Rozhnev (He wrote much of this code. Just intended to answer the question better, not to steal it or something)

user_120779750
Related