How do I check if data in a column already exists and to check the second column of that row to equal a specific number

Question

everyone!

I am having trouble with creating a MySQL statement.

Example:

I have one column named 'keys', and another column named 'isUsed', and the 'keys' column consists of many different keys in this format: xxxx-xxxx-xxxx, and I want to check if that key has already been used or not.

This is the format of the table:

     Key          |   isUsed
xxxx-xxxx-xxxx    |      0

I have already made it so it checks if the key exists by using this code:

$arrExistKey = mysqli_query($resDBCon, "SELECT Key FROM keys WHERE Key = '$strKey'");
$intKeys = mysqli_num_rows($arrExistKey);

if ($intKeys != 1) {
    sendError('The beta-key does not exist!');
}

So I have checked if the key exists, now I'm just trying to figure out how to check if it has already been used.

If anyone can help me out, that would be great! Thank you.

If you could better explain how you want this to work and the usage of isUsed in terms of your project we can better assist you. Also please note that your code is vulnerable to SQL injection. You should also be testing if `$intKeys == 0` to determine that there are now results. If (god forbid) something/someone screws up (glitch or manually inserted row for testing), your code will still function as expected. — ctwheels, Aug 20 '17 at 18:21
Does this answer your question? [How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL)](https://stackoverflow.com/questions/22252904/how-to-check-if-a-row-exists-in-mysql-i-e-check-if-an-email-exists-in-mysql) — Dharman, May 01 '20 at 15:37

score 0 · Answer 1 · answered Aug 20 '17 at 16:38

One solution would be to modify your SQL statement to

"SELECT Key, isUsed FROM keys WHERE Key = '$strKey'"

Now you can check if the key exists using your solution, followed by the mysqli_fetch_array function to retrieve the value for isUsed.

Also please check if $strKey does not contain illegal characters, before inserting it into the SQL statement. Depending on the rest of your code, it could be vulnerable to SQL injection.

Good luck with the implementation!

score 0 · Answer 2 · answered Aug 20 '17 at 16:38

This may be what you need:

// SELECT THE KEY YOU NEED
$result = mysqli_query($resDBCon, "SELECT * FROM keys WHERE Key = '$strKey'");
// LOOP ALL RESULTS AND ENTERING IN THE WHILE MEANS THAT THE KEY EXISTS
while($row = mysqli_fetch_array($result) {

   // HERE YOU CHECK IF KEY WAS USED
   if($row['isUsed'] == 1)
       echo 'Key was used';
}

score 0 · Accepted Answer · answered Aug 20 '17 at 16:47

0

How about just encoding the logic in the SQL?

$arrExistKey = mysqli_query($resDBCon, "SELECT Key FROM keys WHERE Key = '$strKey' AND isused = 1");

answered Aug 20 '17 at 16:47

Gordon Linoff

1,242,037
58
646
786

Wow, that was more simple than I thought it was. Thank you anyway, it worked great! – ChrisBoyd Aug 20 '17 at 19:54

Mr Pro Pop · Answer 4 · 2017-08-20T16:38:56.163

-1

Here is what you need!

    <?php

$arrExistKey = mysqli_query($resDBCon, "SELECT Key FROM keys WHERE Key = '$strKey'");
$intKeys     = mysqli_num_rows($arrExistKey);


$sql = mysqli_query($resDBCon, "SELECT * FROM keys WHERE Key = '$strKey'");
while ($row = mysql_fetch_array($sql)) {

    $used = $row['isUsed'];

}


if ($intKeys != 1) {
    sendError('The beta-key does not exist!');
} else {


    if ($used == 1) {
        echo "the key has already been used recently!";
    } else {
        //action
    }
}

?>

Good luck!

edited Aug 20 '17 at 16:38

answered Aug 20 '17 at 16:13

Mr Pro Pop

666
5
19

This is absolutely incorrect, you should not have a second query as a sort of try-catch exception. You don't make workaround like this you fix the actual problem. – MinistryOfChaps Aug 20 '17 at 16:21
@MinistryofChaps I am not sure about it, but, anyway, edited it. So, is it now fine? – Mr Pro Pop Aug 20 '17 at 16:45
No, what OP has is perfect, one query can be used to check if the key exists and he can modify it to get and check the isUsed value. – MinistryOfChaps Aug 20 '17 at 19:20

How do I check if data in a column already exists and to check the second column of that row to equal a specific number

4 Answers4