easy mysql question

[betrollwin.com]

A script generates a random number. I want to check and see if that number already exists in a table (so that i dont have duplicate id #s). How would i go about doing that?

[Tipem]

You can use the rand() function.

e.g.,

$avariousnumber = rand(1,50); // generate a random # between 1-50

// my funky cool mysql code using the various number :O 

[betrollwin.com]

Yea, I already have a random number. I want to check if that number is already in that database, and if it is run the random number script again until i get a number that isnt already in teh database.

[Waffles]

You could juse use an auto incrementing primary key?

[Tipem]

You could juse use an auto incrementing primary key?

Yes, if you ran a script like that, it could potentially loop and loop until your server dies. If you are trying to uniquely identify each row, just have an "id" column as your primary key that is auto incrementing

[betrollwin.com]

I don't think you guys are getting it. This is what happens:

1) person signs up, get random number from 1 - 10000000
2) second person signs up with same random number script and therefor has a chance of getting the same number.

I need the code to check and see if person 2 has the same number any of the rest of the people (ex 1-5000).

I know you need to use:

SELECT `rndID` FROM `tablename` WHERE `rndID` = $numgenerated

But what from there?

[Tipem]

Have you already uniquely identified each row with an auto increasing integer? Is this 'rndID' column is just another column or what uniquely identifies each row (i.e., is the 'rdnID' the ACTUAL user id)?

[betrollwin.com]

Have you already uniquely identified each row with an auto increasing integer? Is this 'rndID' column is just another column or what uniquely identifies each row (i.e., is the 'rdnID' the ACTUAL user id)?

yes. There is no auto_incrementing column.

I would think that cycling 1 by 1 through each thing would waste a LOT of time since im hoping for 100,000+ registries.

[Immerse]

Psuedo code, untested:

$unique = false;
while(!$unique)
{
    $numgenerated = rand(0, 100000);
    $query = "SELECT COUNT(*) FROM `tablename` WHERE `rndID` = $numgenerated";
    $rs = mysql_query($sql);
    $row = mysql_fetch_row($rs);
    if($row[0]==0)
    {
        $unique = true;
    }
}

Although I must say that I agree with the other guys, let the database assign a unique ID based on an auto-incrementing value. Saves unnecessary processing and queries!

[Tipem]

Here's an example of using auto_increment:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 ); 

This would create a structure like so:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Basically, if you have an auto_increment'ing column, the number will automatically increase and you won't have to worry about random numbers and looping scripts.

[r937]

can i jump in here in support of the original poster?

let's say you start with an empty table

run the script, generate the random number, insert the row into the database

so far so good, yes?

okay, from now on, when you run the script, you have the possibility of a "collision" with an already-existing row, right?

so you set up a little loop (pseudocode):

do until no database error {
   generate a random number
   issue the INSERT statement
   get status code of insert operation
}

in other words, you let the database check for dupes, and if there is no dupe, it goes ahead and inserts the row and returns a "successful" status code

so if you're generating random numbers in the range 1 - 10000000 (ten million), then that little loop will actually loop more than once or twice how often?

okay, say you have already managed to insert 1 million rows, that still gives you ten-to-one odds that the next random number you generate won't already exist

is this more of what you were looking for?

[betrollwin.com]

can i jump in here in support of the original poster?

let's say you start with an empty table

run the script, generate the random number, insert the row into the database

so far so good, yes?

okay, from now on, when you run the script, you have the possibility of a "collision" with an already-existing row, right?

so you set up a little loop (pseudocode):

do until no database error {
   generate a random number
   issue the INSERT statement
   get status code of insert operation
}

in other words, you let the database check for dupes, and if there is no dupe, it goes ahead and inserts the row and returns a "successful" status code

so if you're generating random numbers in the range 1 - 10000000 (ten million), then that little loop will actually loop more than once or twice how often?

okay, say you have already managed to insert 1 million rows, that still gives you ten-to-one odds that the next random number you generate won't already exist

is this more of what you were looking for?

Yes, that was what I meant.

I used the code given by Immerse and deved it a bit. Now looks like this.

$unique = false;
while(!$unique){
    $random_number = rand( );
    $query = mysql_query("SELECT `ticket` FROM `users` WHERE `ticket` = $random_number");
    $row = mysql_fetch_row($query);
    if($row[0]==0){
        $unique = true;
    }

} 

Yet when I tried to apply the same concept to email, it doesnt work:

    $query = mysql_query("SELECT `email` FROM `users` WHERE `email` = '$email'");
    $row = mysql_fetch_row($query);
    echo $row[0]; //prints out "username@email.com
    if($row[0]!= 0){

        $dupe = false;
        $errormsg .= "Email $email is already in our database<br>";
    } 

The funny thing is that even though the echo prints out that $row[0] has a value other than zero, the script does not go through the if statement.

If anyone can explain why, I'd be grateful

[r937]

i'm sorry, i guess i should have explained more

you do not need to do a SELECT to see if it already exists

make sure the column has a unique constraint on it (being the primary key is sufficient for this)

then just do the INSERT, the database does the checking

is this not clear?

[betrollwin.com]

that works for one of them.

But I need 4 things to be unique.

[r937]

so declare a unique index on them

can you show me your the CREATE TABLE statement and which columns have to be unique?

[betrollwin.com]

It would be something like

name varchar
email varchar - want unique
pass varchar
ticket int - want unique
profid int - want unique
bullid int - want unique
emailval tinyint
val tinyint

[r937]

okay, it sounds more like you want separate unique indexs rather than a combination

run this query please:

SHOW CREATE TABLE yourtablename

also, what does bullid mean?

[betrollwin.com]

This is what was printed out by phpMyAdmin

CREATE TABLE `users` (\n  `email` varchar(255) NOT NULL default '',\n  `name` varchar(255) NOT NULL default '',\n  `pass` varchar(255) NOT NULL default '',\n  `bullID` int(255) NOT NULL default '0',\n  `profID` int(255) NOT NULL default '0',\n  `ticket` int(255) NOT NULL default '0',\n  `emailval` tinyint(4) NOT NULL default '0',\n  `val` tinyint(4) NOT NULL default '0'\n) ENGINE=MyISAM DEFAULT CHARSET=latin1

bullID is bulletin id which, like profId (profile id), is a number provided by the user.

[r937]

so there are no indexes on that table?

in that case,

alter table users add primary key (email);
alter table users add unique (bullID);
alter table users add unique (profID);
alter table users add unique (ticket);

now you won't have to look any of those up yourself, the database will prevent you from entering any row which duplicates any value in those columns

[betrollwin.com]

Great. Now it doesn't allow dupes

But how do I make it print out a nice message, and not
Duplicate entry 'testemail@g.com' for key 1

[r937]

just intercept the database return code

(sorry, i only know how to do that in coldfusion -- i only pop into the php forum to answer database-related questions)

[betrollwin.com]

heh... here comes a new thread