How to Compare Two Values from Database and Post in PHP?

When working with databases in PHP, comparing two values and posting the result involves querying the database to retrieve the values, performing a comparison operation, and then using the results as needed. This process typically includes connecting to the database, executing a query to fetch the values, comparing them using PHP logic, and posting the outcome to a web page or another system. This approach is fundamental for tasks like validation, data synchronization, and dynamic content updates.

Prerequisite

• A local or remote server running PHP.
• A MySQL database.
• Basic knowledge of PHP and SQL.
• A database table with some sample data to compare.

Approach

We will use a single approach where the comparison is performed within the SQL query, and the result is returned to PHP. This approach is efficient and leverages the power of SQL to handle comparisons directly in the database.

Steps to Compare Two Values from Database and Post in PHP

Step 1: Set Up Your Environment

Install a Local Server:

• Install a local server stack like XAMPP, WAMP, or MAMP, which includes Apache, MySQL, and PHP.
• Download XAMPP (Recommended for simplicity).
  

Step 2: Create the Project Directory

• First, locate your xmapp directory (installation directory of xampp).
• Make the ,following directory in xampp/htdocs/

Project Structure:

• Place the compare_values_project directory in the htdocs folder if you're using XAMPP.

Step 3: Setting Up the Database

• Open the control panel of your chosen server stack (e.g., XAMPP Control Panel).
• Start Apache and MySQL services (By clicking on start button).

• Open phpMyAdmin, Open a web browser and go to http://localhost/phpmyadmin/.

• Create a New Database.
• Click on "New" in the left sidebar.
• Enter a database name (compare_vaule_project).
• Click "Create".

• First click on New on left sidebar then a new window is open as shown in figue then provide your database name in input field as shown in figure then finally click on create button.
• Create the Database Table, We'll create a game_scores table to store sample data. Use the init.sql file to initialize the database.

//xampp/htdocs/compare_values_project/db/init.sql

CREATE TABLE game_scores (
    game_id INT PRIMARY KEY,
    player1_score INT,
    player2_score INT
);

INSERT INTO game_scores (game_id, player1_score, player2_score) VALUES
(1, 50, 45),
(2, 60, 60),
(3, 30, 40);

Step 4: Creating Database in phpMyAdmin

Import the SQL File:

• Select the newly created database from the left sidebar.
• Click on the "Import" tab.
• Choose the init.sql file from your project directory (/compare_values_project/db/init.sql).
• Click "Go" to import the file and create the table with sample data.

Step 5: Connecting to the Database

• Open the index.php file in a text editor.
• Update the database connection credentials.

<?php
// xampp/htdocs/compare_values_project/public/index.php
$servername = "localhost";
$username = "root"; // Default username for XAMPP
$password = ""; // Default password for XAMPP
$dbname = "compare_values_project";//database name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

Step 6: Writing the SQL Query

• SQL Query to Compare Values, Write an SQL query to compare the player1_score and player2_score for each game.

<?php
// Continue in /xampp/htdocs/compare_values_project/public/index.php

// SQL query to compare values
$sql = "SELECT
            game_id,
            CASE
                WHEN player1_score > player2_score THEN 'Player 1 wins'
                WHEN player1_score < player2_score THEN 'Player 2 wins'
                ELSE 'It\'s a tie'
            END as game_result
        FROM game_scores";

$result = $conn->query($sql);
?>

Step 7: Fetching and Displaying Results

• Fetch Results and Display, Fetch the results from the database and display them in an HTML table.

<?php
// Continue in /xampp/htdocs/compare_values_project/public/index.php

if ($result->num_rows > 0) {
    echo "<table border='1'>";
    echo "<tr><th>Game ID</th><th>Result</th></tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["game_id"] .
            "</td><td>" . $row["game_result"] . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

Step 8: Complete PHP Script

We combine all the steps into a single PHP script (index.php). This script connects to the database, executes the SQL query, fetches the results, and displays them in an HTML table.

<?php
// /xampp/htdocs/compare_values_project/public/index.php
$servername = "localhost";
$username = "root"; // Default XAMPP username
$password = ""; // Default XAMPP password
$dbname = "compare_values_project";//database name

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// SQL query to compare values
$sql = "SELECT
            game_id,
            CASE
                WHEN player1_score > player2_score THEN 'Player 1 wins'
                WHEN player1_score < player2_score THEN 'Player 2 wins'
                ELSE 'It\'s a tie'
            END as game_result
        FROM game_scores";

$result = $conn->query($sql);
if ($result->num_rows > 0) {
    echo "<table border='1'>";
    echo "<tr><th>Game ID</th><th>Result</th></tr>";
    while ($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["game_id"] . "</td><td>"
            . $row["game_result"] . "</td></tr>";
    }
    echo "</table>";
} else {
    echo "0 results";
}

$conn->close();
?>

Step 9: Access the Project in Browser

• Open a web browser.
• Go to http://localhost/compare_values_project/public/index.php.

kartikjaat5765

Follow

Improve

Article Tags :

• Web Technologies
• PHP