mysql - Get info from MariaDB in PHP

Question

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.

question from:https://stackoverflow.com/questions/65904087/get-info-from-mariadb-in-php

Answer 1

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