stuartnico Posted November 28, 2007 Share Posted November 28, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/79165-solved-copy-between-tables-and-updating-or-inserting/ Share on other sites More sharing options...
phpjabbers Posted December 27, 2007 Share Posted December 27, 2007 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' Quote Link to comment https://forums.phpfreaks.com/topic/79165-solved-copy-between-tables-and-updating-or-inserting/#findComment-424029 Share on other sites More sharing options...
stuartnico Posted December 27, 2007 Author Share Posted December 27, 2007 I managed to get it resolved in the end. Thanks for your help. Quote Link to comment https://forums.phpfreaks.com/topic/79165-solved-copy-between-tables-and-updating-or-inserting/#findComment-424039 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.