SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Feb 2004
    Location
    UK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy INSERT executing before SELECT

    I have the following frustrating problem.

    I have a register.php script which processes the registration form.
    To avoid duplicate usernames I check the database before inserting a new record. Something along the lines...

    if ($username_taken=$DB->query_firstrecord("SELECT username FROM table WHERE username='$username'){
    echo "Username taken";
    }else{
    $DB->query("INSERT into table etc ...);
    }

    What happens sometimes is that it FIRST inserts the $username record into the database AND THEN it reports that the $username is taken - basically the INSERT query runs BEFORE the SELECT query.
    User tries all sorts of usernames and everytime it inserts a new record yet reports that the username is already taken.

    I have tried using SELECT HIGH_PRIORITY .... , but that doesn't solve the problem either.

    I am really desparate to get this issue solved.

    Thank you

  2. #2
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Looks to me like the problem could be in your $DB class, because there's very little wrong with the logic or the SQL

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    how about declaring a unique constraint on the username,
    so that you don't have to do the SELECT at all,
    just go ahead and do the INSERT, and cut your processing
    cycles in half
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Feb 2004
    Location
    UK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    that is what I did in the end.

    I believe the original problem has something to do with table locking.

    From the online MYSQL documentation:

    "Table updates normally are considered to be more important than table retrievals, so they are given higher priority. This should ensure that updates to a table are not 'starved' even if there is heavy SELECT activity for the table"

    That is the reason why the INSERT (update) executes before the SELECT.

    http://dev.mysql.com/doc/mysql/en/Table_locking.html


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
  •