SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Joining two accounts in the searches table

    I get an error when I execute this query:

    UPDATE searches SET author = '$newAuthor' where author = '$oldAuthor'


    I believe this is because $oldAuthor has some of the exact same keyword searches stored in the table as $newAuthor. How do I go about combining the searches of $newAuthor and $oldAuthor so that only unique searches are combined?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sorry, my copy of Microsoft® CrystalBall© is currently down, so i can't see the error message you got

    mind telling us what it was?

    oh, and show the actual query sent to mysql, not the php template query -- i want to see the sql with those php variables substituted with actual values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you've got that query in a php variable, say $query, then do

    Code PHP:
     
    echo $query;
     
    die();

    just after you assign $query. I suspect you probably haven't wrapped those php variables in quotes properly in the actual evaluated query.

    When queries generated by php aren't working correctly, 9 times out of 10 there is a problem with the evaluated query - either missing/mismatched quotes, syntax error, variables with incorrect values etc etc.

    If you echo out the query the error usually becomes pretty obvious.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that. Here it is:

    A fatal MySQL error occured.
    Query: UPDATE searches SET author = '11659' where author = '7487'
    Error: (1062) Duplicate entry '11659-anger-keyword' for key 'author'
    11659 = author of search
    anger = the actual keyword the author searched on
    keyword = the type of search as opposed to a zipcode search
    Convert your dollars into silver coins. www.convert2silver.com

  5. #5
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well there you go.

    pretty obvious

    Duplicate entry '11659-anger-keyword' for key 'author'

  6. #6
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What is obvious to you isn't obvious to me. If you look at my original question it states that I knew the general cause of the problem but came here to seek how to tweak my query so the duplicate entry error doesn't happen. Is there a graceful way of handling this situation without getting an error message? Getting an error stops the rest of the .php page from running.

    Thank you.
    Convert your dollars into silver coins. www.convert2silver.com

  7. #7
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then if you don't understand the error message, why on earth did you not post it in your op? If you posted it originally, I'm sure you would have got a solution/explanation a lot quicker.

    I skipped school the day they taught mind reading and so I couldn't tell what the error message was either.

  8. #8
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for trying.
    Convert your dollars into silver coins. www.convert2silver.com

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Is there a graceful way of handling this situation without getting an error message?
    yes, there is

    but before i know what to do,could you please explain what you want to happen whenever a duplicate condition occurs

    in your update query are two variable values...

    UPDATE searches SET author = '$newAuthor' where author = '$oldAuthor'

    what are the circumstances that choose the values for these variables? where do they come from?

    i.e. why would you change an old author to a new author that already exists?

    and how does searching come into play here? if someone does a keyword search for anger, what initiates the need to run an update?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This whole process is to join duplicate user accounts that have been created. If a user changes their email address, instead of updating it in their profile, they will often just create a whole new account. Then they have searches they have conducted under the old account and new account. I go into my backend admin tool and find these duplicate accounts. So I check the creation dates and last login dates to determine which account to keep and which to merge. Sometimes the newest created account is not always the best to save because their login dates show the older account was actually logged into most recently. Yeah, I can't explain why they do that...weird eh?

    Once I figure out which account to keep as the main one, I grab the user ID from the other one and plug it into my query. It then goes through several tables transferring the author of the searches, favorites, leads, etc. to the account I want to keep. I'm getting this error message because some of these people are searching on various keywords, like "anger" from BOTH of their accounts.

    To answer your question, when joining these duplicate accounts, I would like it to notice a keyword already exists, skip it, then move onto the other keywords stored in the searches table. Right now, when it comes across a duplicate, it fails the whole script and the rest of the search terms do not get merged.

    As far as how searching comes into play, the website tracks everything the user looks for. So if they don't find any testimonials on "anger", the website will email them in the future if a testimonial is posted that matches their keyword. It's all about creating an incentive to keep the users coming back to the site over and over again. So far its working great and there are over 15,000 users.

    I hope this helps Rudy. Let me know what else you need.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    nice explanation, but it didn't help

    i still don't see how that update statement will cause a duplicate key

    maybe you could do a SHOW CREATE TABLE for the searches table, so that i can see the key

    also, you didn't say what should happen when a duplicate is created

    it almost sounds like you might want to consider UPDATE IGNORE ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks again Rudy, the "ignore" part did the trick. It no longer gives an error if it comes across a search word that already exists in one of the accounts. Once this query is done, I run another one that totally deletes the unwanted author out of the users, searches, leads, favorites and testimonial tables.
    Convert your dollars into silver coins. www.convert2silver.com


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
  •