Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57

    Unanswered: Insert a blank row

    Hi there!
    I wish to insert a blank row in a MySQL table and get as result the id of this row.
    Purpose of this operation is to make later an update of this row with collected data.

    How should I write the query?

    Many thanks in advance for your help!

    Best regards,

    Gerry

  2. #2
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57
    Problem solved !!!

    Code:
    //connect to db
    mysql_query("INSERT INTO tbl_name VALUES ()");
    $inserted_id = mysql_insert_id();
    print "&number=".$inserted_id;
    Last edited by Germaris; 02-27-08 at 10:51.

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Hmm, sounds like you need to employ some lazy db queries, instead of running a query twice (potentially).
    Interesting though: in what sort of application/reasoning are you doing this? I can see what you're getting back (the id) but where is it being used that stops you from entering the information at the same time?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Germaris
    Purpose of this operation is to make later an update of this row with collected data.
    that's just plain silly

    why not just eliminate this "insert blank row" nonsense and do the insert using the collected data later
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I could understand it...

    Perhaps there is foreign key information that needs to be stored now and you don't have the information to fill in the master record..?

    Can't think of a real life situation, but I can appreciate the sentiment.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to appreciate the horror of this requirement you need to read this in conjunction with http://www.dbforums.com/showthread.php?t=1627763

    the autonumber value is to be used for an ordernumber quoted to the customer.

    its the age old problem of coercing the surrogate key into having meaning outside the system.




    it'll end in tears.....

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    it'll end in tears.....
    quote of the week!!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I love this "order number" stuff. I saw another post elsewhere on a different forum, looking suspiciously the same , asking how to perform exactly this. At which point we noted that an order DOESN'T become an order until the final button "checkout" or "processing payment" is pressed, at which point everything in your shopping basket then becomes an order, and gets assigned a number, and entered into all it's relevant tables.
    Giving someone an order number before they've ordered seems a bit like nonsense...

    However, if you're dealing with "quotes" that's slightly different, but I think should probably maintain the same path. What's the point in giving someone a quote number until they're formalised their quote. (i.e. logged out, saved as "quote"). Again, it doesn't get inserted into the table and given a number until it's completed.

    I hate to think how many blank records get created using this methodology, but it makes me shudder to think I have to use ALLOW NULL on all my columns

  9. #9
    Join Date
    Mar 2008
    Location
    Windhoek Namibia
    Posts
    13
    Not a good idea .. Lots of "blank" records .. lots of unintended null fields yes .. but most importantly .,. any multi user system now can get spurious writes to the same ID.

    A better solution is to create a separate capture table that you store a username, timestamp or session # in and then allow for the capture and fiddling. When you programatically decide that you have the correct data .. write it to that master table that has all the pack drill , checks and balances.. Housekeep the capture table regularly with deletes..

    Good luck
    Wayne Philip - openaxon.com

Posting Permissions

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