Searching multiple tables for a unique number

[dougmcc1]

I want to generate a random number and check to see if it exists in the rnum field of all my tables (4 of them). If it exists in any of them, then I want to generate a new random number over and over until a unique one is generated. This code works when a single table name is used in place of the wildcard.

$row['rcnt']=1;
while($row['rcnt']>0)
{
$random_number = rand(1,10000);
$result = mysql_query("SELECT COUNT(rnum) as rcnt FROM * WHERE rnum='".$random_number."'") or die(mysql_error());
$row = mysql_fetch_array($result);
}

With the wildcard I get an error of "check your mysql syntax near '* ...

Just for laughs, I also tried this (for 2 tables):

$row['rcnt']=1;
$row2['rcnt2']=1;
while($row['rcnt']>0&&$row2['rcnt2']>0)
{
$random_number = rand(1,10000);
$result = mysql_query("SELECT COUNT(rnum) as rcnt FROM table1 WHERE rnum='".$random_number."'") or die(mysql_error());
$row = mysql_fetch_array($result);
$result2 = mysql_query("SELECT COUNT(rnum) as rcnt2 FROM table2 WHERE rnum='".$random_number."'") or die(mysql_error());
$row2 = mysql_fetch_array($result2);
}

Also, I was hoping this would work (commas):

$result = mysql_query("SELECT COUNT(rnum) as rcnt FROM table1,table2,table3,table4 WHERE rnum='".$random_number."'") or die(mysql_error());

Thanks in advance.

[LordShryku]

ugh...sounds tedious...

what I would do had I ever gotten shoved into this predicament...

$random_number = rand(1,10000); 

$result = mysql_query("SELECT count(*) FROM table1 WHERE rnum = ".$random_number);
$row = mysql_fetch_row($result);
if($row[0] > 0) {
    echo "number found";
}
else {
    $result = mysql_query("SELECT count(*) FROM table2 WHERE rnum = ".$random_number);
    $row = mysql_fetch_row($result);
    if($row[0] > 0) {
        echo "number found";
    }
    else {
        $result = mysql_query("SELECT count(*) FROM table3 WHERE rnum = ".$random_number);
        $row = mysql_fetch_row($result);
        if($row[0] > 0) {
            echo "number found";
        }
        else {
            $result = mysql_query("SELECT count(*) FROM table4 WHERE rnum = ".$random_number);
            $row = mysql_fetch_row($result);
            if($row[0] > 0) {
                echo "number found";
            }
            else {
                "number not found";
            }
        }
    }
}

Of course, this really sucks when you need to do it over and over...

[dougmcc1]

See it needs to generate a new random number if the number is found.

I'm thinking it needs a while loop:

WHILE number exists in another table, keep generating new numbers until unique number is generated.

Why doesn't the wildcard work?? Damnit. 🙂

[ultraslacker]

You shouldn't need to do this, really. I suspect that you haven't an adequate database design, especially considering your final example query...

[dougmcc1]

So what would you suggest as an adequate database design from what I have described?

[ultraslacker]

Post your schema.

[dougmcc1]

It's just four tables. Do I need foreign keys?

[ultraslacker]

I can't say - you havent described the database yet.

[LordShryku]

There should never really be a need to do this. It really sounds like bad database design, but considering if someone else made the database, and I was stuck supporting it, I would do a seperate query on them all.

[dougmcc1]

Hey thanks for sticking with me. Yeah it was bad design. All 4 tables had the exact same fields so I combined them all into 1 table and then added 3 more fields (1 for each table I deleted).

Thanks for mentioning my bad design. Boy that was a lot easier than trying to code around it 🙂