SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Confused how to write this UPDATE

    OK, I'm confused how to write an update for this simple app I'm developing locally to learn more about db interactions.

    I have an "edit" page, which is populated from the db and lists up to 9 user links, so something like:

    <input type="text" name="link1" value="http://www.yahoo.com">
    <input type="text" name="link2" value="http://www.google.com">
    <input type="text" name="link3" value="http://www.amazon.com">
    <input type="text" name="link4" value="">
    <input type="text" name="link5" value="">
    <input type="text" name="link6" value="">
    <input type="text" name="link7" value="">
    <input type="text" name="link8" value="">
    <input type="text" name="link9" value="">
    <input type="submit" name="submitted" value="update">

    My links table is just 2 columns and looks like:

    Code:
    user_id 	linkurl
    2	        http://www.yahoo.com
    2	        http://www.google.com 	 	 
    2	        http://www.amazon.com
    How do I go about writing an UPDATE query for, say, editing the 3rd link and adding a 4th? I have it stuck in my head that I need 9 link columns in my db.

    I also have on this same page a section for the user's personal info and I can update that easily with a query like:

    Code:
    $query = "UPDATE users
              SET first_name='$firstname', last_name='$lastname', email='$email', state='$state'"
    
              if($newpass1){
                  $query .= ", pass=md5('$newpass1')";
              }
    						
              $query .= " WHERE user_id = {$_SESSION['user_id']}";

  2. #2
    SitePoint Evangelist
    Join Date
    Aug 2007
    Posts
    566
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you have got the basis...
    Now, you cannot update and insert in 1 operation.
    If you happens to pass each links in your form, what I usually do when there is not a lot of data, is to delete all, then inserts.
    Otherwise, you have to
    1) get the form data
    2)do a select to check if it exists
    2.1) if it exists, ignore it
    2.2) if it does not exists, do an insert

    In that view, if you drop every records first
    Code:
    delete from links
    then you can do a series of insert which each links.
    Of course, this means that all the links that are different from the one you pass in your form are deleted...

    Something else you could do, is to put an unique index on the link address.
    Then, you simply do inserts like
    http://dev.mysql.com/doc/refman/5.0/...duplicate.html
    Code:
    insert into links (url) values ('http://www.whatever.com') on duplicate key update url='http://www.whatever.com'
    which can be translated to "try to do this insert, but if it fails because the value exists, do an update"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tripy View Post
    Something else you could do, is to put an unique index on the link address.
    Wouldn't this mean that no two users could have the same link? Only one user could have a link to http://www.yahoo.com for example?

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    make a compound unique index that includes the user id.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The links table was defined: PRIMARY KEY(user_id, linkurl)

    So I need to add: UNIQUE (user_id, linkurl) ?

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    primary key and unique are almost the same, the differences being that PRIMARY KEY does not allow any part of the key to be null and there can only be one PRIMARY KEY on a table.

    so the answer to your question is no. in fact, you don't need any additional index to use INSERT ... ON DUPLICATE KEY UPDATE
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  7. #7
    SitePoint Zealot
    Join Date
    Dec 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the replies. I'm getting pretty confused on this end, I've been changing so much stuff, I'm having a hard time remembering what I've even changed, lol. I don't think one of my tables is right, so I'm going to be toasting it and STARTING OVER. I'm going to start a new thread for that momentarily, I hope you can follow-up in there.


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
  •