Copying rows from another table in MySQL

Hey,

I need to make a query to pull the fields id and game from table A and insert them as media_id and game_id in table B, but only if the field game is not empty. I also need to insert 'news' into the field media_type in table B.

How can I achieve this?

Thanks,
Connor Beaton

 

<?PHP

$sql = mysql_query("SELECT * FROM `Table A` WHERE `game` != ''");
while($row = mysql_fetch_assoc($sql)){
mysql_query("INSERT INTO `Table B` (`media_id`, `game_id`, `media_type`) VALUES ('" . $row['id'] . "', '" . $row['game'] . "', 'news')");
}

?>

Code (markup):

 

Thanks mate, you're a legend. Worked a charm.

 

Glad to help!

Let me know if you need anything more.

 

You can also run this as a single query as long as 'news' doesn't need to be unique per insert.

INSERT INTO table_b (media_id, game_id, media_type) SELECT (id, game, 'news') FROM table_a WHERE game != '';

 

I'd rather do that ^ instead , so you don't have to send multiple queries to the DB. It's probably better optimized aswell.