How to check if MySQL table is empty or not when I reload page PHP

Question

I work on a CRUD PHP page and everything works perfectly. The data that I take is from JSON url (https://jsonplaceholder.typicode.com/todos/).

How can I check and populate MySQL table if is empty every time when i load my index page.

This my PHP code how I connect to database. And function to inset JSON data to MySQL(I'm not sure that works correctly) :

class Database
{
    private $db_host;
    private $db_name;
    private $db_username;
    private $db_password;

    public function dbConnection()
    {
        $this->db_host = 'localhost';
        $this->db_name = 'items';
        $this->db_username = 'root';
        $this->db_password = '';
        try {
            $conn = new PDO('mysql:host=' . $this->db_host . ';dbname=' . $this->db_name, $this->db_username, $this->db_password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $conn;
        } catch (PDOException $e) {
            echo "Connection error " . $e->getMessage();
            exit;
        }
    }
}

Function getDati() that works correctly and returns array with 200 objects

function getDati()
{
    $url = 'https://jsonplaceholder.typicode.com/todos/';
    $cURL = curl_init();
    curl_setopt($cURL, CURLOPT_URL, $url);
    curl_setopt($cURL, CURLOPT_HTTPGET, true);
    curl_setopt($cURL, CURLOPT_RETURNTRANSFER, true);

    curl_setopt($cURL, CURLOPT_HTTPHEADER, array(
        'Content-Type: application/json',
        'Accept: application/json'
    ));

    $result = curl_exec($cURL);
    curl_close($cURL);

    $all_items = json_decode($result);

    return $all_items;
}

function intoDB()
{
    $database = new Database;
    $db = $database->dbConnection();
    $all_items = getDati();

    $sql = "SELECT count(*) FROM posts ";
    $result = $db->prepare($sql);
    $result->execute();
    $number_of_rows = $result->fetchColumn();
    if ($number_of_rows <= 0) {
        foreach ($all_items as $k => $item) {

            $sql = "INSERT INTO posts (id, userId, title, completed) VALUES (?,?,?,?)";
            $stmt = $db->prepare($sql);
            $stmt->execute([$item->id, $item->userId, $item->title, $item->completed]);
        }
    }
}

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Documents</title>
    <link rel="stylesheet" href="./style.css">
    <script src="https://kit.fontawesome.com/12883fd222.js" crossorigin="anonymous"></script>
    <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
    <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
</head>

<?php
require_once 'process.php';
intoDB();  // this is where i call the function.

It should be IF that will check if table is full or empty but I donk know how to do that.

When I recall intoDB() nothing happens. MySQL table remain empty. I want to check every time when I reload or open index page that my MySQL table is populated and if it is empty to populate it. Any suggestion how to do that? Thanks in advance!

add a try catch also for the execute to check if there is a problem, — nbk, Feb 16 '20 at 16:25
Any suggestion how can i populate MySQL when i reload my page? — , Feb 16 '20 at 16:59
It really shouldn't be the responsibility of your page to check the database on every single load, though. If your page relies on a table, have something else that runs during deployment that ensures that your database is populated before you start up your server. — Mike 'Pomax' Kamermans, Feb 16 '20 at 19:02

score 0 · Answer 1 · answered Feb 16 '20 at 19:13

When selecting count(*), one row is returned, which contains the count so the response will be something like ['count(*)' => 0].

$number_of_rows = $result->fetchColumn(); // This contains 1 row, with the column indicating the count and so your condition will never be executed

Rather alias the column SELECT count(*) as total_posts FROM posts

$row = $stmt->fetch(PDO::FETCH_ASSOC); // Fetch the first row
$total_posts = $row['total_posts']; // Get the column containing the result of the count
// Run your condition $total_posts

Checkout this answer

I don't get it. How it should be like in my case? – Feb 16 '20 at 19:21 — , Feb 16 '20 at 19:21

How to check if MySQL table is empty or not when I reload page PHP

1 Answers1