How to select a column for a MySQL table and compare it with a PHP variable

Question

I am trying to compare a MySQL table column which I have imported to my script and compare it with a PHP value which I have defined.

I am trying to make an if loop that checks if any of the values in the column are equal to the variable.

// Connect to database containing order information

$servername = "server";
$username = "user";
$password = "pass";

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

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

// define variables and set to empty values
$name = $ordernumber = "";

// Load up data from the form

$ordernumber = ($_POST['order_number']);

// Get SQL info
$sql = "SELECT order_number FROM p_orders;";
if ($conn->query($sql) === TRUE)
{
    echo "Checked Orders.....";
}
else
{
    echo "Failed to check orders, please contact Support for assistance" . $conn->error;
}

// Checking Script 

if ($ordernumber === $orders)
{
    echo "Order Number Found.... Let's Select a Seat";
}
else
{
    echo "Your Order was not found, either you did not order a reservation ticket, have not waited 3 days or you entered the number wrong. If issues persist then please contact Support."
    };

Why don't you just add WHERE order_number='$ordernumber' to your MySQL query. Then chk the num_rows returned - if it's 1 then that order number exists; 0 it doesn't. — user3741598, May 12 '16 at 16:16
or try fetching order_number from database via mysqli_fetch_assoc — Aniket Singh, May 12 '16 at 16:17
@PeterDarmis please stop proposing things at random. You are simply wrong. Using a prepared statement here have absolutely no influence on the matter at hand. — Félix Adriyel Gagnon-Grenier, May 12 '16 at 17:24
@FélixGagnon-Grenier http://stackoverflow.com/questions/6301598/mysql-prepared-statement-vs-normal-query-gains-losses , http://stackoverflow.com/questions/5108414/mysqli-query-vs-prepare — , May 12 '16 at 17:27
@FélixGagnon-Grenier what you write is written also in those posts additionally is written to use prepared statements. — , May 12 '16 at 17:28

score 2 · Answer 1 · edited May 23 '17 at 11:44

The end part of the script should be like this...

$stmt =  $mysqli->stmt_init();
if ($stmt->prepare('SELECT order_number FROM p_orders WHERE orderID = ?')) {
    $stmt->bind_param('s',$_POST['order_number']); // i if order number is int
    $stmt->execute();
    $stmt->bind_result($order_number);
    $stmt->fetch();
    if (!empty($order_number))
        echo "Order Number Found.... Let's Select a Seat";
    }else {
        echo "Your Order was not found...";
    }    
    $stmt->close();
}
$mysqli->close();

...note that the query now looks for only the records that match and note the use of prepared statement to make safe the post variable from SQL Injection.

The reason to collect only the matching items from SQL is otherwise, if you have a million records, the database would return all of them and then PHP will need to loop through them (this can cause maximum execution, memory and other errors). Instead databases where built to look things up like this - note an index on this field would be good and also use of a "youtube style" id is recommended, which is why I've assumed the use of a string for it's instead of a number as the variable minght imply - and it's not the "id" which is good for a number of reasons... I've added a link to explain "youtube style" id which I'll not go into detail here but there is a lot of win in using that :)

UPDATED based on...

http://php.net/manual/en/mysqli-stmt.prepare.php
MySQL prepared statement vs normal query. Gains & Losses
https://www.youtube.com/watch?v=gocwRvLhDf8 (Will YouTube Ever Run Out Of Video IDs?)

Use [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php). Don't make a mess of things by escaping manually. — tadman, May 12 '16 at 16:27
tadman this is a very good point, only so much I could think of in my quick reply! — Barry, May 12 '16 at 16:27
It should be habit if you use `mysqli` on a regular basis because it's actually less code and the chance of making a mistake is way lower. — tadman, May 12 '16 at 16:29
yeah I know, but I use an ORM and don't often code at this level :) — Barry, May 12 '16 at 16:29
ORM is really the only way to roll if you do this regularly, it's true, but some people insist on roughing it. — tadman, May 12 '16 at 16:31
@Barry please read this and improve your answer http://stackoverflow.com/questions/6301598/mysql-prepared-statement-vs-normal-query-gains-losses — , May 12 '16 at 17:35

score 0 · Answer 2 · 2016-05-12T17:06:53.190

Preferably use a WHERE clause searching for the order id and mysqli prepared statement, like below.

$mysqli = new mysqli("localhost", "my_user", "my_password", "my_db");
/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$name = "";       
// Load up data from the form  
$ordernumber = $_POST['order_number'];  

/* create a prepared statement */
if ($stmt = $mysqli->prepare("SELECT COUNT(*) FROM p_orders WHERE orderID=?")) { 

    /* bind parameters for markers */
    $stmt->bind_param("i", $ordernumber ); // "i" if order number is integer, "s" if string

    /* execute query */
    $stmt->execute();

    /* bind result variables */
    $stmt->bind_result($counter);

    /* fetch value */
    $stmt->fetch();

        if ($counter>0) { // if order id is in array or id's
            echo "Order Number Found.... Let's Select a Seat";    
        } else {
            echo "Your Order was not found, either you did not order a reservation ticket, have not waited 3 days or you entered the number wrong. If issues persist then please contact Support."
        }

    /* close statement */
    $stmt->close();
}

/* close connection */
$mysqli->close();

How to select a column for a MySQL table and compare it with a PHP variable

2 Answers2