Introduction

Whatever we see on our website, it is either saved within HTML or being fetched from a table. The HTML has a script file attached with it which calls backend, where SQL codes are written. These SQL codes interact with database. In this article we will learn how to use WHERE condition in PHP. There are different ways to pass WHERE conditions to the PHP APIs, we will showcase the one that can be used in all types of CRUD operations here.

Syntax

A WHERE condition in SQL is used during READ, UPDATE and DELETE operation something like this:

SELECT * FROM <table name>WHERE <field 1> = '{<local variable 1>}'and <field 2> = '{<local variable 2>}'

How to use WHERE condition in PHP

To use a WHERE condition in PHP, you need to first fetch the values that you will pass in the condition.
Let us take an example where you will get username and password from the frontend and you will check if the user exist in the table.

function onLogin(){
$servername = "localhost";
$username = "root";
$dbname = "testDB";
// Create connection
$conn = new mysqli($servername, $username,"", $dbname);
// Check connection
    if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
    }
    $obj = json_decode($_POST["data"]);
    $username = $obj->username;
    $password = $obj->password;
    $sql = "SELECT * FROM login WHERE username = '{$username}'and password = '{$password}'";
    if($result = $conn -> query($sql)){
        if($result->num_rows > 0) {
            $dataArray[0] = 'Login Successful';
            echo json_encode($dataArray);
            }
        else{
            $dataArray[0] = 'Invalid Credentials';
            echo json_encode($dataArray);
        }
    }
    else{
    $dataArray[0] = 'Invalid Credentials';
    echo json_encode($dataArray);
    }
}

Sending WHERE condition to PHP from frontend

From frontend we can send all the required WHERE conditions altogether in an object and the same can be parsed in the backend and used as per the use case:

onSignin: function (oEvent) {
    var that = this;
    var username = this.byId("idu_admin").getValue();
    var password = this.byId("idp_admin").getValue();
    if (username && password) {
        // Read and Bind data and then call print
        $.ajax({
            url: that.uri,
            type: "POST",
            data: {
                method: "onLogin",
                data: JSON.stringify({
                    username: username,
                    password: password
                })
            },
            dataType: "json",
            success: function (dataLog) {
                if (dataLog[0] !== "Invalid Credentials") {
                    that.oRouter.navTo("Home", true);
                } else {
                    MessageBox.error("Invalid Credentials, Try again!");
                }
            },
            error: function (request, error) {
                MessageBox.error("Server Issue, Kindly contact Admin!");
            }
        });
    } else {
        MessageBox.error("Enter both username and password to continue!");
    }
}