Updating and inserting one table with another tables data

[Hickwithstick]

SQL version is 5.1.56

 

I tried to google this and only ended up confusing myself completly so if somone would please help I would really appreciate it.

 

What I am trying to accomplish is to take the text data from one table and insert it into another table in a diffrent colum and I would also like it to insert new data whenever the fist table changes, such as when a new member joins my site. the data is text in a column called "Nickname" and I would also like to insert the ID number from a column called "ID" into the second table where the colums are called "user_id" and "username"

to make it easier to read I want to take "profiles" "ID"  "Nickname" and insert them into the table "bx_user" "user_id" "username" each time there is another row created in the profile table also adding it to the bx_user table if possible

 

Thanks in advance

[Hickwithstick]

Ok I have been trying to figure this out and so far I have the variables declared but I really have no idea how to save them, from what I could find with google an insert command is what I needed to use but I get this error Parse error: syntax error, unexpected T_STRING in /home/nawacl/public_html/Singleparentmeetings/test5.php on line 15  from this file

 

<?php

 

$con = mysql_connect("localhost","nawacl_rich","Berrysweet83");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

 

mysql_select_db("nawacl_singleparent", $con);

 

$result = mysql_query("SELECT username, user_id FROM bx_users");

 

while($row = mysql_fetch_array($result))

 

UPDATE Profiles ID=($row[userid], NickName=$row[username]) ;  <--------------------  This is line #15

 

mysql_close($con);

?>

[awjudd]

You need to use the actual database commands to run the update.

 

<?php

$con = mysql_connect("localhost","nawacl_rich","Berrysweet83");
if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("nawacl_singleparent", $con);

$result = mysql_query("SELECT username, user_id FROM bx_users");

while($row = mysql_fetch_array($result))
{
mysql_query("UPDATE Profiles SET ID=".$row['userid'].", NickName='".$row['username']."'") ;  // <--------------------  This is line #15
}
mysql_close($con);
?>

 

~awjudd

[Hickwithstick]

Thank you very much awjudd,  The error is gone but it fails to update the Profiles columns in the data base.

 

I'm not sure if the "UPDATE" command is actually what I need to use or if "INSERT" would be the one because there are more rows in the Profiles table than there are in the BX_users table.  I know the script above is reversed I did that intentionally because right now there are more rows in the bx_users table (30) and only 1 in the Profiles table so if it will make that one match to start with then I can change the script and only 1 record will be added at a time when someone signs up from then on

[awjudd]

Does the row already exist, or is it a new row?

 

If it already exists, then you need  you need to UPDATE it.  Otherwise you need to INSERT it.

 

I just noticed that your UPDATE is missing a WHERE clause which means that every row will be updated each time.  You probably want to fix that.

 

~awjudd

[Hickwithstick]

Just to make sure I am clear on the Definitions of the terms, there is a table called "Profiles" which contains many rows of data that consist of my members usernames, sex, date of birth etc... the rows are the individual user names and data correct? Just like the "Table" itself. 

 

If that is the case then no the rows dont exist, not all of them anyway. For example right now my Profiles table has I row in it with the user name of John but my bx_users table has rows consisting of John, James, susy, Alice, etc.. making a total of 30 rows

 

The what I am trying to accomplish is that I have a website powered by a CMS (Dolphin) and I integrated a forum (phpbb3) into it but when I intigrated it the signup page for the forum is bypassed so when a member tries to use the forum and the script checks to see if they are a registered user on the bx_users table it returns an error that they can't use the forum board because they arent registered. The only registration page is when they join the site, and the CMS saves all that data to the Profiles table so I just need to copy it to the bx_users table.  I intend to add this scrpt to the bottom of the signup page script so that it only runs when a new user joins, but since there will allways be 1 more row in the Profiles table than there is in the bx_users table with the new information caused from the sign up but there will also be several rows of identical data in both theTables allready  Hopefully thats clear and wasnt just me rabling on 

[Hickwithstick]

and as I tried to explain in my original post, I can't just copy the table because the column's have diffrent names such as username=NickName  and user_id=ID between the 2 tables

[Hickwithstick]

OK I have still been trying to make this work, I am working on it and not just expecting soone to do it for me, and so far I have figured this much out but now I get an unexpected end  on the line containing the ?>

<?php

$con = mysql_connect("localhost","UserName","User Password");
if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("nawacl_singleparent", $con);

mysql_query("INSERT INTO Profiles(ID, NickName, Password) SELECT (user_id, username, user_password) FROM bx_users WHERE ID = > 1)

mysql_close($con);

?> 

 

Anyone care to help me some4 more ?

[El Chupacodra]

That unexpected end is just a missing semicolon after this:

....WHERE ID = > 1)