SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to pull info out of MySQL at the same time it is entered?

    We have a web form. People fill it out and submit it. PHP sends the info to the MySQL database. The first field in the MySQL database is an "id" field that is generated automatically by MySQL. At the same time an email is sent to the visitor. I want to include the ID number that is generated in MySQL in the email as a reference number. What's the best way to get access to this number from inside the PHP script? Do I have to do another query to get it? One query to insert the info (and MySQL generates the id number) and another to then get that number? Any insight or advice would be greatly appreciated. Thank you.

  2. #2
    SitePoint Wizard silver trophy Jelena's Avatar
    Join Date
    Feb 2005
    Location
    Universum, 3rd Corner
    Posts
    3,000
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT LAST_INSERT_ID()
    should do the trick
    -- Jelena --

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect. Thank you very much. One last thing. How do I get just the number? Right now I have this:

    $query = "select last_insert_id()";

    $result = @mysql_query($query) or trigger_error("Query: $query\n <br />MySQL Error: " . mysql_error());

    $requestid = $result;

    However the value of $request id is now: Resource id #5

    I want it to just be 5. How do I get just the number and not the Resource id #. Thanks again!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    caution: using an auto_increment number might compromise your application

    if i got an email saying "to redeem your prize, go to http://example.com/redeem.php?id=42" what's to stop me from changing that number and visiting again?

    suggestion: use something more unique than an auto_increment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    caution: using an auto_increment number might compromise your application

    if i got an email saying "to redeem your prize, go to http://example.com/redeem.php?id=42" what's to stop me from changing that number and visiting again?

    suggestion: use something more unique than an auto_increment
    OK, I see your point. So what would you suggest? Generating a random number at the time of the entry into the database? I thought about this, but then thought, if I'm generating a random number isn't there always the possibility, no matter how slight, that the same number could be generated again at some point? What would you suggest as "more unique than an auto_increment"? Thanks!

  6. #6
    SitePoint Zealot fatnic's Avatar
    Join Date
    Aug 2006
    Location
    Lincoln, UK
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Once the number is generated check the database to see if it's already in it. If not, bingo!

  7. #7
    SitePoint Wizard silver trophy
    Join Date
    Mar 2006
    Posts
    6,132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    another possibility would be to create a token based on the id. run an update query after the insert, using the id to guarantee uniqueness but adding some random characters to it, to make the token difficult to predict.

    PHP Code:
    $token $last_insert_id '-' lotsa_random_characters();
    $sql "update sometable set mytoken = '$token' where id = $last_insert_id"
    however, you are still exposing the id. this may or may not be an issue, but you should consider the implications before doing it.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, yes, of course. Thank you again.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    consider using a guid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Jul 2006
    Location
    Kansas City, MO
    Posts
    280
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You all make things difficult. After your insert query simply do:

    PHP Code:
    $id mysql_insert_id(); 
    That'll get the last auto_increment ID which was created. Generating your own unique-ID for user retrieval with some sort of time limit applied to it would still be a better choice.


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
  •