Jump to content

Recommended Posts

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.

  • 5 weeks later...

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'

 

 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.