SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have 2 tables:
    --------
    trips:

    id
    week
    location
    theme

    days:

    id
    tid
    day
    daycontent
    ----------------

    Users specify in form1 how many days the trip lasts and they fill out the fields week,location,theme, and are then directed to form after submitting.

    In form 2 the form generates the amount of fields for the days specified in form1.

    For the insert statement regarding the table days i use:
    -----------
    $day_length = sizeof($daycontent);

    if($day_length>0){

    $insert_str = "";
    $tid=$id;

    for($i=1; $i <= $day_length; $i++){
    if($i > 1)
    $insert_str .= ",";
    $insert_str .= "( $tid , $i , '$daycontent[$i]' )";
    }

    mysql_query("INSERT INTO days (tid,day,daycontent) VALUES " .
    $insert_str) or die("insert failed");

    -----------

    The form and inserting days works fine, but a user should be able to change the amount of days for a trip on form2
    So if a trip currently is currently 4 days and a user wants to add 3 days to make it 7 then I would need to have the first 4 days updated and the last 3 days inserted, since days 1 to 4 are already in the database.

    How can I accomplish this?

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    By making use of MySQL's REPLACE command, which is basically a "INSERT if it doesn't exist, otherwise UPDATE the existing entry", you should be able to increase a trip's length by just REPLACE'ing a number of 'day' entries equal to the new trip length. Shortening a trip should be fairly straightforward too, as you can just DELETE where 'day' is greater that the number of days specified for the trip.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm having difficulty getting the replace command to work as I would like.

    I tried this: (and other things)
    mysql_query("REPLACE days (rid,day,daycontent) VALUES " .
    $insert_str) or die("update failed");

    But obviously this will result in double entries since rid,day and daycontent are not unique values.
    The only value that is unique in the day table is ID (autoincrement).

    How would I be able to use the REPLACE command in this case?

    Regards,

    JamesBond

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure that I know the answer to your question. But, ...

    I think that REPLACE is a bit of a dangerous beast when mixed in with mySQL's lack of referential-integrity. Read this http://www.bitmechanic.com/mail-arch...1998/0458.html
    On Tue, 19 May 1998 08:59:02 -0400 (EDT) Eric Prud'hommeaux <eric@w3.org> wrote:

    [...]

    > mysql> REPLACE INTO ffc_initial (emailaddr, submitted) VALUES
    > ('promote@webmarquee.com',NOW());
    > Query OK, 2 rows affected (0.00 sec)
    >
    > mysql> select * from ffc_initial;
    > +----+--------------------------+---------------------+
    > | id | emailaddr | submitted |
    > +----+--------------------------+---------------------+
    > | 1 | jay@olddominion.net | 1998-05-19 01:00:33 |
    > | 2 | dietpepsi@webmarquee.com | 1998-05-19 01:01:11 |
    > | 4 | promote@webmarquee.com | 1998-05-19 08:44:55 |
    > +----+--------------------------+---------------------+
    > 3 rows in set (0.00 sec)

    > What I don't get is why the id gets changed from 3 to 4. I've tried using
    > a null for the id and gotten the same results. I have to do an extra
    > SELECT id from ffc_initial where emailaddr='promote@webmarquee.com' to
    > maintain the same id. This sort of defeats the purpose of have a REPLACE
    > when you care to maintain an external key. Anybody know why this is?
    [...]
    > I presume REPLACE works by seeing if your new entry conflicts with any of
    > the unique fields in your previous entries and replaces the old one if it
    > does. INSERT defers to the pre-existing entry while REPLACE defers to the
    > one you are asking to add.

    Replace _deletes_ the original entry and then creates a new one. This is why
    the "auto_increment"ed counter goes to 4, rather than remaining at 3.

    'insert' always creates a new entry. (except it if fails due to a duplicate
    key).
    'replace' always removes the existing entry and then creates a new entry
    'update' allows you to change an existing entry

    ------------------------------------------------------------------------------
    Alistair MacDonald Postmaster/Internet Services Developer,
    A.MacDonald@uel.ac.uk University of East London. +44 181 590 7000 x4564

    -----------------------------------------------------------
    Think of the consequences if the primary key of the table in the example is a foreign key in a related table. Not pretty!

    As to your specific problem, Mr Bond, I think it might be solved if you were to do away with the (I presume) unnecessary column "id" in table "days". Instead if your primary key where defined as the composite of the columns "tid, day" then I think your replace would work.

    If you need to read the documentation about creating a composite primary key - http://www.mysql.com/doc/C/R/CREATE_TABLE.html

  5. #5
    SitePoint Addict jamesbond's Avatar
    Join Date
    Feb 2001
    Location
    The Netherlands
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for help

    I ended up not using the REPLACE command, I solved it by using the "UPDATE" command for the existing days and the "INSERT" command for the extra days.


    Regards,

    JamesBond

  6. #6
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That and REPLACE is non-standard SQL which makes it harder to switch database management systems.


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
  •