SQL/PHP Database Connection to Client Side Display

Question

I've been starting to program in PHP, and I want to create a program that allows any visitor to click a button and have it increment a universal counter. Eg, a user clicks the button, the counter increments by 1, and you can refresh the page and that new number will have "stuck".

My thought was to use a database that would hold the current number of "clicks" and display it, then use a client-side JavaScript button to increment the database's value. I am able to access my database and get the current number of clicks held there statically, but I'm at a loss as to having the counter be interactional. I've tried googling around to see how to do this in JavaScript, and the results have been minimal. Are my goals even achievable in JavaScript? Or should I use a different language to connect my server-side ops with my client-side ones?

// connects to the database using hostname, user, pass, db name
$connect = mysqli_connect('HOSTNAME','USER','PASSWORD','epiz_33276135_ducks');

if (!$connect) {
    echo 'problem connecting to database';
}

//takes the query
$query = "SELECT Count,ID,AnimalName FROM ducks WHERE ID=1";

//connects result adn records it 
$result = mysqli_query( $connect, $query);
$record = mysqli_fetch_assoc( $result);
if (!$result) {
    echo 'smthin weird';
}
echo '<h2>'.$record['Count'].'</h2>';

From my understanding, PHP is for server-side operations, and Javascript is for client-side work. Googling hasn't generated any answers, and I haven't been able to find a way that can edit hte

Either use a HTML form to submit the info to the server, or use JavaScript to trigger an AJAX request (so you don't need to refresh the page). Either way, you need to generate an HTTP request from the browser to the server, so that it will execute a PHP script to update the database — ADyson
– ADyson, Commented Dec 29, 2022 at 1:07

Phil · Accepted Answer · 2022-12-29 01:50:54Z

Typically, you'd have your client-side code make a request to a PHP script that increments the count and responds with the new value. You can either use a form which results in a full page load or use an asynchronous request for a more seamless experience.

On the front-end, you'd use something like this

<button id="increment-counter" type="button">Increment Counter</button>

// Add a "click" event listener to the button
document
  .getElementById("increment-counter")
  .addEventListener("click", async () => {
    // Make a PUT request to your PHP
    // The `method` probably isn't important but a GET request seemed wrong
    const res = await fetch("increment-counter.php", { method: "PUT" });

    // Check for any errors
    if (!res.ok) {
      throw new Error(
        `Increment counter failed: ${res.statusText} - ${await res.text()}`
      );
    }

    // now update the current count in-place
    document.querySelector("h2").textContent = (await res.json()).Count;
  });

On the server-side, something like this (and I'm using PDO because it's more beginner-friendly than MySQLi)

// increment-counter.php
if ($_SERVER['REQUEST_METHOD'] !== 'PUT') {
    // Only allow PUT requests
    http_response_code(405);
    exit;
}

// Connect to your DB
$pdo = new \PDO(
    'mysql:host=HOSTNAME;dbname=epiz_33276135_ducks',
    'USER',
    'PASSWORD',
    [
        \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
        \PDO::ATTR_EMULATE_PREPARES   => false,
    ]
);

$pdo->beginTransaction(); // atomic updates are important
try {
    // Select the current Count
    $count = $pdo
        ->query('SELECT `Count` FROM ducks WHERE ID = 1 FOR UPDATE')
        ->fetchColumn();

    // Update your Count column
    $pdo->exec('UPDATE ducks SET `Count` = `Count` + 1 WHERE ID = 1');

    $pdo->commit();

    // Respond with a JSON object containing the updated count
    header('content-type: application/json');
    echo json_encode(['Count' => $count + 1]);
    exit;
} catch ($err) {
    $pdo->rollBack();
    throw $err;
}

symcbean · Accepted Answer · 2022-12-29 01:13:24Z

Learn one language at a time. PHP in this context writes HTML so you simply need to implement a page transition - i.e. fetch new html from the server....

<?php
$connect = mysqli_connect('HOSTNAME','USER','PASSWORD','epiz_33276135_ducks');

if (!$connect) {
    echo 'problem connecting to database';
}

//takes the query
$query = "SELECT Count,ID,AnimalName FROM ducks WHERE ID=1";

//connects result adn records it 
$result = mysqli_query( $connect, $query);
$record = mysqli_fetch_assoc( $result);
if ($result) {
    $query="UPDATE ducks SET `Count`=`Count`+1";
    mysqli_query( $connect, $query);
} else {
    echo 'smthin weird: ' mysqli_error($result);
}

echo "<h2>$record[Count]</h2>";
echo "<a href='$_SERVER[REQUEST_URI]'>next</a>";

Once you've got this working, have a look at HTML forms.

BTW its bad practice to use reserved words (Count) for attribute names.

Collectives™ on Stack Overflow

SQL/PHP Database Connection to Client Side Display

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related