Test if number exist in database, create new one if it exists

Question

I have a hurtle I just can't figure out.

I want my code to:

Create a random number
Test if it exist in the database
If it exist, create a new random number og do number 2. again.
If it don't exist, create it in the database.

I've tried the following code, which I found in a similar problem on Stackoverflow, but I just can't figure this out:

$key = true;

while($key){

    $unique_number_new = rand(1000,9999);
    $result = mysql_query("SELECT * FROM brugere WHERE medlemsnummer = $unique_number_new");

    if(mysql_num_rows($result) > 0) {   
        $key = false;   
        // Allready exists

    } else {    
        $key = true;
        // Create in database
    }

        echo "Code done! - " . $unique_number_new;
}

The problem is, it gives me multiple numbers (see https://www.dance4fun.dk/test_folder/number_test.php)

How can I create a code that does the 4 above mentioned, steps?

Hope you can help me out with this!

*"The problem is, it gives me multiple numbers"* - Isn't that exactly what you want to do in Step 3 of your intended design? What exactly is the problem? — David, Jun 28 '18 at 10:56
The problem is, I want to end up with one number, that doesn't exist in the database, and that number is then created. When I get more numbers I cant be sure which ones doesn't exist and which ones does. — Rasmus Kjeldsen, Jun 28 '18 at 10:59
@RasmusKjeldsen: So you just want to print the number only if it wasn't found in the database? Then move the `echo` statement into the `else` block? — David, Jun 28 '18 at 11:00
@IsThisJavascript when I move the $unique_number_new outside the while i get A LOT of lines, can i prevent that somehow? (See https://www.dance4fun.dk/test_folder/number_test.php) — Rasmus Kjeldsen, Jun 28 '18 at 11:01
@David exactly. And IF it exists, I want the code to test a new number. makes sense? — Rasmus Kjeldsen, Jun 28 '18 at 11:02
@RasmusKjeldsen: Yes, it makes sense. What's not clear is what the problem is. Your code appears to be doing *exactly* what you want it to do, but you just don't want to print every number to the output. So don't print every number to the output. Only print the last one which wasn't found in the DB. Which would mean printing only in the `else` block instead of every loop iteration. — David, Jun 28 '18 at 11:03
I will try that. I'm wondering whether, the $key in the else block should be false instead of true, so it closes the while? — Rasmus Kjeldsen, Jun 28 '18 at 11:05

IncredibleHat · Accepted Answer · 2018-06-28T13:37:32.130

A reason why your code didnt work:

You had 'true' set as the not found result, so if it didnt find anything, it would continue looping.
You had the echo showing you the code no matter what.

This is a way to achieve what you listed out:

do {
    $unique_num = mt_rand(1000,9999);
    // NOTE: changed mysql_query to use mysqli! (prepared statement not required here)
    $exists = mysqli_fetch_row(
                  mysqli_query(
                      $connection,
                      "SELECT COUNT(*) FROM brugere WHERE medlemsnummer = $unique_num"
                  )
              )[0];// [0] means grab column result
} while($exists);

echo "Code done! - " . $unique_num;

This will always run the code once (do), and will repeat if the random number was found in the database. Once it is not found in the database, you then have a random number in a variable to use as your whim after the while loop finishes.

For these things, its a good idea to add a fail-safe:

$c = 0;
do {
   $c++;
   /// other code
} while ($exists and $c < 1000);

Thank you! Working just fine now. – Rasmus Kjeldsen Jun 29 '18 at 06:29 — Rasmus Kjeldsen, Jun 29 '18 at 06:29

Test if number exist in database, create new one if it exists

1 Answers1