SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    easy mysql question

    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?

  2. #2
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use the rand() function.

    e.g.,

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

    // my funky cool mysql code using the various number :O 
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  3. #3
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Evangelist Waffles's Avatar
    Join Date
    Nov 2005
    Posts
    435
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could juse use an auto incrementing primary key?

  5. #5
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  6. #6
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  7. #7
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)?
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  8. #8
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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)?
    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.

  9. #9
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Psuedo code, untested:

    Code:
    $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!
    Last edited by Immerse; Jul 2, 2006 at 14:17.

  10. #10
    Pandora Tipem's Avatar
    Join Date
    Feb 2006
    Posts
    450
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's an example of using auto_increment:

    PHP Code:
    CREATE TABLE animals (
         
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
         
    name CHAR(30NOT NULL,
         
    PRIMARY KEY (id)
     ); 
    This would create a structure like so:

    Code:
    +----+---------+
    | 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.
    Tipem no fancy sig, yup
    Need Neopets Help? Try Neopets
    fun adoptable eggs

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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):
    Code:
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 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):
    Code:
    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.
    PHP Code:
    $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:
    PHP Code:
        $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

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that works for one of them.

    But I need 4 things to be unique.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    so declare a unique index on them

    can you show me your the CREATE TABLE statement and which columns have to be unique?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, it sounds more like you want separate unique indexs rather than a combination

    run this query please:
    Code:
    SHOW CREATE TABLE yourtablename
    also, what does bullid mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what was printed out by phpMyAdmin

    Code:
    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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Addict betrollwin.com's Avatar
    Join Date
    Nov 2005
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    heh... here comes a new thread


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •