[RESOLVED] insert into... select - inserting data from one table to another

Martsparts

Hello all ye mighty php folk,

I have a rather puzzling issue, I'm trying to copy data from one table into another. Basically i have a list of players in one table, and would like to select a few players from the player table to form a main team. The example below is of the player called Rooney:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?PHP

$user_name = "nuts";
$password = "nuts";
$database = "nuts";
$server = "nuts";

mysql_connect($server, $user_name, $password) or die ('Error: ' . mysqlerror());
mysql_select_db($database);

$query="INSERT INTO `nuts`.`Main` (`Player`, `Team`, `Position`, `Last season`)
SELECT  `Player`, `Team`, `Position`, `Last season`
FROM `nuts`.`Player`
WHERE `Player` LIKE `Rooney`";

mysql_query($query) or die ('Error updating database');

echo "Database updated";

?>

</body>
</html>

This doesnt seem to work though, can anyone help? Thanks in advance.

Mart

leatherback

Rather than copying yuor data, your'd be better of creating a table 'team_members' with 2 colums: team_id and member_id. you can there store for each team which person is a member. This is better way, as you will not need to update 2 tables in case one of your members changes name, or, even worse, you get to the new season and each members'record need to be updated. (Naturally, you can also create a table in which you store the players' position for a certain season, if you think this website will run for a couple of seasons).

Read up on database normalization if you want to learn more about this.

bradgrafelman

Welcome to PHPBuilder! When posting PHP code, please use the board's [noparse]

..

[/noparse] bbcode tags as they make your code much easier to read and analyze.

Martsparts wrote:
I'm trying to copy data from one table into another.

Don't. 🙂

Why duplicate data? What happens when Mr. Rooney changes his name or transfers teams? You've got to search all over your database for records to update... or worse, miss some records and have data get out of sync.

Before you get any further, you should stop and read some articles on database normalization. For example, in your example scenario above, I would have three tables - players, teams, team_players (or team_members or something of that nature).

players - contains all the info about the player (including a PRIMARY key, such as an AUTO_INCREMENT column).
team - contains all the info about a team (similar to players above)
team_players - table with two columns, both foreign keys; one for a row in players and one for a row in team.

Example data:

players (id, player_name, player_hometown):
(123, 'Rooney', 'Croxteth, Liverpool, England')

teams (id, team_name, team_manager):
(456, 'Manchester United', 'Alex Ferguson')

team_players (player_id, team_id):
(123, 456)

EDIT: Woops.. looks like leatherback types faster. :p

leatherback

bradgrafelman;10985397 wrote:
EDIT: Woops.. looks like leatherback types faster. :p

Nah, you just have a more informative post 😃

Martsparts

Many thanks chaps, helped me a great deal.

Mart