[SOLVED] Copy between tables and updating or inserting.

[stuartnico]

I've been working a small piece of code which takes certain columns of information from one table and puts it into another table if not thier and if thier then update. I not sure on what process to go about this, first I wrote a small script and it worked fine for just copying, but if data already existed then I hit a brick wall.

 

So I started to readup and came across joins and wondered if this was maybe an easier option and created a new script which again produces the results to cross reference the two tables now, I'm stuck on how to get it to decide on its next action and how to do it. Can anyone please help me on this.

 

I pick things up fairly quick, but very much the kind of person who prefers to see it done and then learn from that.

 

Script.1

 

$query = "SELECT `table1`.`userid`,`table1`.`username`,`table1`.`password`,`table1`.`email` FROM `table1` ORDER BY `table1`.`userid` ASC";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
mysql_query("INSERT INTO `table2`(`UID`,`email`,`username`,`pwd`,`emailverified`) VALUES ('$row[0]','$row[3]','$row[1]','$row[2]','yes')");
}

 

On this one it copies the required data across, but I need to add a else statement as some users may already be in table two so it needs to update those that are thier and insert those that are not. This is where I'm getting stuck.

 

 

Script.2

 

$mysql_query("SELECT
`table1`.`userid` AS `vbid`,
`table1`.`username` AS `vbuname`,
`table1`.`password` AS `vbpass`,
`table1`.`email` AS `vbemail`,
`table2`.`UID` AS `suid`,
`table2`.`username` AS `suname`,
`table2`.`pwd` AS `spass`,
`table2`.`email` AS `semail`
FROM
`table2`
Right Join `table1` ON `table2`.`UID` = `table1`.`userid`
ORDER BY
`vbid` ASC");

if ('$table1.userid' == '$table2.UID'){

mysql_query("UPDATE `table2`(`UID`,`email`,`username`,`pwd`,`emailverified`) VALUES (`$vbid`,`$vbemail`,`$vbuname`,`$vbpass`,'yes')");
  
}else{

	mysql_query("INSERT INTO `table2`(`UID`,`email`,`username`,`pwd`,`emailverified`) VALUES (`$vbid`,`$vbemail`,`$vbuname`,`$vbpass`,'yes')");

}

 

On this one I get a single line inserted into table 2 but with none of the data.

 

Thankyou in advance from anyones help.

[phpjabbers]

There is no such thing as Update ['table'] values (....)

 

see Update syntax in MySQL http://dev.mysql.com/doc/refman/5.0/en/update.html

 

your query showld be like this

 

UPDATE `table2`

SET

`UID` = `$vbid`,

`email` = `$vbemail`,

`username`= `$vbuname`,

`pwd` = `$vbpass`,

`emailverified` = 'yes'

 

 

[stuartnico]

I managed to get it resolved in the end. Thanks for your help.