PHP sql while rows>0

Question

What im trying to do here is generate a random number. Now i want to validate if the randomnumber exists in the database and if it existed it should generate another random number and validate it again. I tried using if (mysql_num_rows>0) but its not the right way.
Someone pls help me on this. Thanks!

PHP CODE:

  $randomnumber= rand(1000,9999);
  $sql = "SELECT * FROM table WHERE column= '$randomnumber'";
  $result = mysql_query($sql) or die(mysql_error());
  $numrows = mysql_num_rows($result);
  //while loop

WHAT I DID:

$randomnumber= rand(1000,9999);
$sql = "SELECT * FROM table WHERE column= '$randomnumber'";
$result = mysql_query($sql) or die(mysql_error());
$numrows = mysql_num_rows($result);
if ($numrows>0)
{
$randomnumber =rand(1000,9999);
$sql = "SELECT * FROM table WHERE column= '$randomnumber'";
$result = mysql_query($sql) or die(mysql_error());
}

you are missing " double quotes in query? And why are you saying it is not the right way? — ɹɐqʞɐ zoɹǝɟ
– ɹɐqʞɐ zoɹǝɟ, Commented Mar 23, 2014 at 13:37
tnx for that.. but whats happening is it will only generate random number once after validating what i need is continues generation of random number until the mysql_num_rows =0 — zxc
– zxc, Commented Mar 23, 2014 at 13:40

Rimble · Accepted Answer · 2014-03-23 13:53:20Z

$generated = false;

while($generated == false)
{
    $randomnumber= rand(1000,9999);
    $sql = "SELECT column FROM table WHERE column= '$randomnumber'";
    $result = mysql_query($sql) or die(mysql_error());
    $numrows = mysql_num_rows($result);

    if($numrows == 0)
    { 

        $sql_insert = "INSERT INTO table (column) VALUES ('". $randomnumber ."')";

        if($result = mysql_query($sql_insert))
        {
            $generated = true;
            echo 'Random number generated and inserted into table: ' . $randomnumber;
        }
    }
}

This script runs until a random number is not found. It then inserts it into the table if it does not exist. You can also run a version of this without the inserting if you already have a table filled with random numbers.

$found= false;

while($found== false)
{
    $randomnumber= rand(1000,9999);
    $sql = "SELECT column FROM table WHERE column= '$randomnumber'";
    $result = mysql_query($sql) or die(mysql_error());
    $numrows = mysql_num_rows($result);

    if($numrows >= 1)
    { 
        $found= true;
        echo 'Random number found in table! Random number matched was: ' . $randomnumber . '</br>';
    }else
    {
        echo 'No match for ' . $randomnumber . '</br>';
    }
}

tjati · Accepted Answer · 2014-03-23 13:41:18Z

1

I think, a better approach would be to fill the database will all possible random number values and set an is_free-flag. The others columns should be set to NULL.

And then you use a query with the RAND()-function:

SELECT id FROM table WHERE is_free = 1 ORDER BY RAND() LIMIT 1;

This method will always use only one run until all rows are used (is_free = 0).

Your current way may cause, that you have to run your code practical endless.

answered Mar 23, 2014 at 13:41

tjati

6,1166 gold badges47 silver badges62 bronze badges

3 Comments

Shomz Over a year ago

+1 And not to mention the advantage of running one query vs. many queries.

zxc Over a year ago

is there another way to do it on php?

tjati Over a year ago

What do you mean? You use PHP and MySQL, so take the best of both ;)

Collectives™ on Stack Overflow

PHP sql while rows>0

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related