Assigning a random number not stored in two tables

dougmcc1

I have a site that offers yellow page listings listed in a directory. When a user submits an organization to be listed in the directory, they are assigned a random number and the submission gets entered into a pending table in the database where I can authorize or decline it. Here is the code I'm using to assign the random number to the pending page:

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

This is fine and good but then when I want to authorize the page, a new random number is assigned to the listing. I'd like to retain the old random number, as long as it doesn't exist in the authorized listings table.

So I need to modify the above code to check both the pending and the authorized tables to see if the random number exists in either of them. If so, it needs to loop again. But I'm not sure how to make it check the second table.

Thanks.

mrhappiness

don't use two tables for storing the more or less same data

use one table only and have a column called "pending"
in this table you don't need to create a unique number as long as you have some auto_increment primary key

Sxooter

There are several ways of approaching this at a database level that don't include having to put the data from two tables into one. Unfortunately, most won't work on MySQL:

Use a union query:

SELECT COUNT(rnum) as rcnt FROM pending_listings WHERE rnum='123456' UNION
SELECT COUNT(rnum) as rcnt FROM approved_listings WHERE rnum='123456'

Use a view on top of that union so you don't have to use that union all over the place:

create view pending_and_approved_listings as
SELECT COUNT(rnum) as rcnt FROM pending_listings WHERE rnum='123456' UNION
SELECT COUNT(rnum) as rcnt FROM approved_listings WHERE rnum='123456'

but the fastest way to do this, if you don't want to put it all in one table, or maybe even if you do, is to put all the available random numbers in the range in a third table. first create it and populate it with the numbers from 100001 to 988889, then use a delete query or something like it (I can't remember MySQL's goofy left jon syntax used to replace in() so I'll leave that as an exercise for you.

delete from available_listings where rnum in(select rnum from approved_listings);
delete from available_listings where rnum in(select rnum from pending_listings);

then each time you need a new one, you can grab it from the available_listings:

select count(*) from available_listings;
$max = result from above...
$rnd = random(1,$max);
select rnum for update from available_listings limit $rnd,1;
$todel = rnum from above
delete from available_listings where rnum=$todel;

and then go ahead and insert it.

This is a general methodology. I use postgresql so my SQL may be using features MySQL doesn't support, but for the where in() stuff there is definitely a section of the manual that covers replacing that with a join of some kind.

Sxooter

I just realized that MySQL doesn't support a select for update on myisam tables since those don't support row level locking. Has it been implemented for innodb types, or do you have to use a different statement to lock those rows individually? anyone, anyone? Ferris? Ferris? 🙂

dougmcc1

Someone jump in and tell me I can just do something like this:

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

Sxooter

That might just work. Just add the proper names in the where clause:

SELECT 
  COUNT(rnum) as rcnt 
FROM 
  pending_listings p,
  authorized_listings a 
WHERE 
  a.rnum='".$random_number."' 
OR 
  p..rnum='".$random_number."'