I have an interesting problem....

DarkJackalX6

I just need to transfer a user's email address from one Table to the exact same field in another table without affecting the user's data and making sure the email is with the right user (as in in the row with the matching username)

What I want to do is check $username1 and $username2 and if they are the same I want it to copy $Email1 to $Email2 in the right row… So it would be like this

Username | Email
Chris | Chris@email.co
Joe | Joe@email.co
Monroe | Monroe@email.co

And it would insert the data into $username2 without affecting any other data on that table as well as be in that users row…

The reason why they all can’t be in one table is because Table1’s values are used in collecting xml data, parsing them and storing them in Table2….The only thing that xml doesn't supply is the Email, which is stored in Table1 upon registration…Given how often the xml feed changes, before the information is updated the script is configured to drop all information in Table 2. The xml feed I am using cannot be changed so it’s a problem I am trying to work around when dynamically displaying all the user data…

The Mysql structure is as follows:

DATABASE
TABLE1 {
Field1
Username1
Email1
}
TABLE2 {
Field2
Username2
Email2 (Empty)
}

I don’t normally ask for help but I’ve never had a problem that I couldn’t just fix... Since I cannot restructure my database I am out of ideas… Not only that but I have never had to query multiple tables before so I took my best whack at it to just toy around with it and see if I can do it…. I don’t know if this will help much but here is my “test” attempt…

<?
mysql_connect("localhost","username","password");
mysql_select_db("databaseName") or die("Unable to select database");
$result = mysql_query("select * from Table1");
$result2 = mysql_query("select * from Table2");
while($r=mysql_fetch_array($result))
{	
$var1=$r["Field1"];
while($r2=mysql_fetch_array($result2))
{
$var2=$r2["Field2"];
if($var2==$var1) echo "WORKS GREAT!";
}
}
?>

leatherback

hm.. I wonder whether you are actually doing yourself a favour by inserting this data into another table.. If you have a matching field anyway -username- why not just join the two tables at query-time?

select FIELD, FIELD, FIELD, from table2 left join table1 on table2.username = table1.username

If you really have serious problems with this idea..
- Select all email addresses & usernames from table 1.
Then, for each result record: Update table2 set email = $email where username=$username

Not a nice way of doing it, but it will work, as long as usernames are unique

DarkJackalX6

I don't have a problem with joining them at quary time but I am very interested in your second idea. Thats exactly what I was trying to figure out how to go about doing.. Can you by any chance show me a sample script or string that way I can directly see the initial structure of what I am trying to accomplish?...It will aid me for future reference

NogDog

While having the same data items in multiple tables raises warning flags for me with regard to database design, assuming you stick with this design, perhaps you could simply do:

REPLACE INTO table2 (Field2, username2, email2) SELECT Field1, username1, email1 FROM table1

leatherback

NogDog wrote:
While having the same data items in multiple tables raises warning flags for me with regard to database design, assuming you stick with this design, perhaps you could simply do:
REPLACE INTO table2 (Field2, username2, email2) SELECT Field1, username1, email1 FROM table1

😃 I knew there was a better way to do that 😃

JVassie

NogDog wrote:
While having the same data items in multiple tables raises warning flags for me with regard to database design, assuming you stick with this design, perhaps you could simply do:
REPLACE INTO table2 (Field2, username2, email2) SELECT Field1, username1, email1 FROM table1

Just a quick one NogDog, whats your stance on using relationships in tables, for example a Foreigh ID in one table being the Unique ID in another? As they are both duplicates. 😕

Just wanted to try and understand the best way to do it, as i assault the mountain of php :p

leatherback

Nope. They are not duplicates as such. They are in both tables with a function: To link tables. As such they are not 'Duplicate information'. This in contrast to using email, names etc in both tables. Those are prone to duplication, changes etc. and therefor very poor field to use as a linking field.

You would normally have a table with user_details
and a table with something -say orders- which is JOINed to users using userID and userNR (I try to keep the ID for the tables where it is the primary key, and nr in tables where it is a foraig key)

NogDog

I will clarify my statement as, "...having the same data items in multiple tables, if they are not foreign keys establishing relationships to primary keys, raises warning flags for me..."

Here's a fairly succinct article describing the basics of database normalization. Googling on "database normalization" will lead you to many others, some of which may actually be readable and useful. 🙂

JVassie

Thanks guys, just wanted to clear that up, because I wasn't sure myself what is considered good and bad practice in terms of database design, common sense tells me what you said was the case, however it was just to clarify.