Joining two accounts in the searches table

[busboy]

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!

[r937]

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

[aidos]

If you've got that query in a php variable, say $query, then do

 
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.

[busboy]

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

[aidos]

well there you go.

pretty obvious

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

[busboy]

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.

[aidos]

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.

[busboy]

Thank you for trying.

[r937]

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?

[busboy]

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!

[r937]

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 ...

[busboy]

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.