Updating table with values from other table, in PHP

[Lios]

Not sure if this is the right forum to ask, but here goes...

 

I have two tables in the same database, table1 and table2. Table1 contains 'member_id' and several other columns (not interesting) and is updated regularly by an external program. Table2 also contains the column 'member_id' and several others columns (also not interesting).

 

What i need is to update table2 with the member_ids from table1 by executing a php script. So if table1 has new member_ids, a row is added to table2 with that id and when an id has been deleted from table1, the row with that id from table2 will also be deleted. The other way around is not needed, as rows are never manually added or deleted from table2.

 

Can anyone help me with a piece of code to do this?

[MatthewJ]

Too early for code  , but maybe this will work.

 

Query both tables for all rows.

 

Loop through each result from the table that will contain the new rows. Check to see if those rows exist in the table that needs updated. If they do, go to the next record, if not, do an insert into the database and move to the next record.

 

Hope that helps

[Lios]

It sounds like a solution, but unfortunatly i have no idea how to implement this. I'm used to making queries in sql, but not with php and i can't get it to work. I know it's early again, but could you (or anyone else) give a little coding tip?

[Lios]

If have this now... The select goes fine, but nothing happens with the insert. What am i doing wrong?

 

<?php

$dbhost = 'host';

$dbname = 'dbname;

$dbuser = 'dbuser';

$dbpass = 'dppassword';

$connection = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

mysql_select_db($dbname);

 

$query = "SELECT roster_members.member_id, gear_check.gear_member_id ".

"FROM roster_members LEFT JOIN gear_check ".

"ON roster_members.member_id = gear_check.gear_member_id";

 

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

 

// Print out the contents of each row into a table

 

while($row = mysql_fetch_array($result)){

$member_id = $row['member_id'];

$gear_member_id = $row['gear_member_id'];

echo $member_id. " - " . $gear_member_id;

if ($row['gear_member_id'] == NULL ) {

"INSERT INTO gear_check (gear_member_id) VALUES ($member_id)";

}

echo "<br />";

}

 

mysql_close($connection);

?>

[Lios]

Still no luck. I suppose it would work if i could use an if statement on each record (if member_id is null: delete, if gear_member_id is null: insert), but i just don't know how.

[Lios]

Anyone?

[BlueSkyIS]

this is not likely to ever be true:

 

if ($row['gear_member_id'] == NULL ) {