mysqli_fetch_array and if-else condition for validation

Question

Something is wrong with my php,

I'm doing an account validation where if the data exist it will display "There is data" and else "No data"...

When I enter the first 'row' reference_id and submit, it shows "There is data" which is correct but when I entered the second to the last 'row' reference_id it shows "No data" even though it exist in my Database!

Database:

reference_id (varchar 250)
status (varchar250)

PHP

if (isset($_POST['submit_valid'])) {

    if (!empty($_POST['reference_id'])) 
    {

        $query = mysqli_query($con, "SELECT * FROM client_record");
        $result = mysqli_fetch_array($query);

            if ($result['reference_id'] == $_POST['reference_id'])
            {
               echo"<script type='text/javascript'> alert('There is data'); window.location.href='next_page.php';  </script>";         
            }

            if ($result['reference_id'] !== $_POST['reference_id']) {
                echo"<script type='text/javascript'> alert('No data.'); window.location.href='this_page.php';  </script>";
            }
    } 
}

I am not sure if it's the mysqli_fetch_array fault or the if-else condition is wrong?

if you guys know the problem please help me?

Answer 1

Your query execution currently only looks at the first row. A fetch needs to be looped to iterate over all rows. e.g.

$query = mysqli_query($con, "SELECT * FROM client_record");
$result = mysqli_fetch_array($query);

should be

$query = mysqli_query($con, "SELECT * FROM client_record");
while($result = mysqli_fetch_array($query)) {

but this is inefficient. When looking for a specific record use a where clause. Parameterized queries also will prevent SQL injections, and quoting issues. The i in the bind_param is for an integer, if your id is a string use s.

$prepared = mysqli_prepare($con, "SELECT * FROM client_record where reference_id = ?");
mysqli_stmt_bind_param($prepared, 'i', $_POST['reference_id']);
mysqli_stmt_execute($prepared);
mysqli_stmt_store_result($prepared);
while (mysqli_stmt_fetch($prepared)) {

Answer 2

$query = mysqli_query($con, "SELECT * FROM client_record");
$result = mysqli_fetch_array($query);

This will give you the first row from the table.

Add a WHERE reference_id = :refid clause?!

Then bind the refid parameter, so as to avoid SQL injection.

Answer 3

Lapiz, the problem is actually with the comparison operator:

($result['reference_id'] == $_POST['reference_id'])

This will check the first reference_id from the returned set in array.

The best way to tackle this would be to use if (in_array(5, $result)) where 5 is the needle and $result is the array haystack.

Because all you are doing is to check if the reference exists in the returned data set .

This is also good design practices, to collect results and avoid multiple reference queries each time, hit the database once and query the result set.

If its a multidemnsional array loop through the set:

foreach($result as $resultItem)
{
    if(in_array("reference_id", $resultItem, true))
    {
        echo "There is Data";
    }
}

Good Luck .