Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Transfering data from Users to Links

Quote Reply
Transfering data from Users to Links
Hi -

I need to transfer data from my User Table to the Links table. Basically, I want to take the email address in the Users table and put it in a new field in the links table called Inquiry_Email for each link the the user has. Does anyone know the SQL syntax I would use to do this in the SQL Monitor? Any help would be greatly appreciated!

-jw



Quote Reply
Re: Transfering data from Users to Links In reply to
Not a real good idea...you should keep all user data in the Users table. Doing what you want to do will cause integrity problems AND it will be more difficult for users to update their information.

Regards,

Eliot Lee Wink
http://anthrotech.com/
Quote Reply
Re: Transfering data from Users to Links In reply to
Ok, Eliot is partially correct.

If the "Inquiry_Email" is an attribute of the USER then it needs to be in the Users table. If it's an attribute of the Link (each link a user has may have a different Inquiry_Email address), then it's ok to store in the Link record. What you want to do is "seed" the database with the existing information, but allow it to be changed on a link by link basis, then you can do it.

This is not an easy thing to do with SQL, since you'd need to join the Links records to the Users records on the Username field, then update Links.Inquiry_Email with the Users.Email field. It's easy to have a complex query like this go awry, if it's possible to do at all the way you want it (MySQL is not a very robust complex query language, it's design is to be simple and straight forward).

It would be safer to do it as a short script, that would read through the users table, and update all links for that user.

If you don't have many links, your script could do it by starting out by selecting DISTINCT Username from Links, then iterating through that list -- only users who have links would be checked. If you have a lot of links, you'd need to do it in stages. But, I've found it to be often times better to do it "safely" than issue a query that does a scramble on you your database.

You might want to re-think this in light of the Contact_Email field being added back in as a default field.



PUGDOGŪ Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Plugins:http://LinkSQL.com/plugin
Quote Reply
Re: Transfering data from Users to Links In reply to
Thanks very much for your insights and comments Pugdog and Eliot. Eliot the reason I need to do this is because the user might have one contact email address, but on our directory our users often have several links, and each of these individual links often has its own email address for people to respond to, that often isn't the same as the user email address. So, I'm trying to get the current User email into the links table in a field called Inq_Email, and then notify the users that they can login to modify this email address for each of their links if necessary. In short, this field is specific to the link and not the user.

At any rate, thanks very much for giving me the insights. Sometimes things are really simple, and other times as Pugdog noted, it can get complicated and potentially cause problems.

-jw

Quote Reply
Re: Transfering data from Users to Links In reply to
Well, I would suggest putting the Inquiry_Email column in the Users table, and then add another column called WhichEmail (ENUM, 'Inquiry_Email','Contact Email', Default = 'Contact Email').

Then you can have your users select which email they want to use for their "links"...

Regards,

Eliot Lee Wink
http://anthrotech.com/
Quote Reply
Re: Transfering data from Users to Links In reply to
Thanks Eliot, and sorry I didn't explain our situation very clearly. One of our users, for example, has 3 links and each of those three links has a different email address specific to the information in that link, so in that case it's better for our situation to have an email address specifically for each link, in addition to the user's email address. If the case was one email address for the user and one for the links, then I completely agree with your suggestion. Thanks!

-jw