how to compare the value in the same table in a different column

Question

so i have a verification table consists of key_id, key_verification, confirm_key, and key_status

this is the sql process for client_verifycodez.php where i want to compare the value of key_verification and confirm_key if they are the same

the update sql is needed so that when client enter the key fro a form, it will enter into db as confirm_key. so by that i want to compare the value of confirm_key with the already existing key_verification

    $sql1 = "UPDATE verification SET confirm_key = '".$confirm_key."' WHERE key_id ='".$id."'";
    mysql_query($sql1);

    $sql = ("SELECT * FROM verification WHERE key_verification = confirm_key");
    $query = mysql_query($sql) or die ("Error: " . mysql_error());
    $check = mysql_fetch_array($query);

    if($check==true)
    {
    echo "<center>";
    echo "Your key is invalid!";
    echo "<br>";
    echo "<a href=client_verifycodez.php>Back </a>";
    echo "</center>";
    }
    else
    {
    header("Location: home.php");
    }

so what i need is how do i compare so that when key_verification and confirm_key is equals, it will go to home.php or else alert. i think i have the problem with the sql.

can anyone perhaps help me? thank you

This looks strange. If your query is true, what means that the keys match, it says your key is invalid. Was that intentional? — Realitätsverlust, Jan 17 '14 at 06:58
yes, ok, but this doesnt change the sense ... if you query contains something, $check becomes true because a filled variable is almost always true. And if its true, you output that the code is invalid, even if its valid ... i dont get it. Maybe im too tired, but for me, this condition looks wrong. — Realitätsverlust, Jan 17 '14 at 07:08

score 1 · Accepted Answer · edited May 25 '19 at 14:01

You can't do like this, use sub-query,

"SELECT * FROM verification WHERE key_id =". $id ." AND key_verification
 = (SELECT confirm_key FROM verification WHERE key_id =". $id .")"

Note: Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 0 · Answer 2 · answered Jan 17 '14 at 07:00

The mysql_num_rows() function will return the number of existing rows count

please use mysqli extension always

<?php

$sql = ("SELECT * FROM verification WHERE key_verification = confirm_key");

$query = mysql_query($sql) or die ("Error: " . mysql_error());

//get the number of result rows
$num_rows = mysql_num_rows($query);

//get the details
$check = mysql_fetch_array($query);

if($query)
{
    if($num_rows>0)
    {
    echo "<center>";
    echo "Your key is invalid!";
    echo "<br>";
    echo "<a href=client_verifycodez.php>Back </a>";
    echo "</center>";
    }
    else
    {
    header("Location: home.php");
    }
}

how to compare the value in the same table in a different column

2 Answers2