Add multiple values to table mysql

Question

I am creating a MySQL database and it is storing stats from a game. Right now I am just setting it up and I need to add 80 players with a column for each one that has there goals, assists, etc...

here is my code:

INSERT INTO `finalproject`.`Bremners` (
   `stud_id`, 
   `stud_name`, 
   `stud_goal`, 
   `stud_assist`,
   `stud_attendance`, 
   `stud_goalie`
) VALUES (
   '1', 
   'test', 
   '0', 
   '0', 
   '0', 
   '0'
);

But that only inserts one player named "test". Does anyone know how to modify this code so that it adds 80 players each one with a different name? (I making the website with php so if it can be done through php that works to)

Thanks

Take a look at this.

Menno
– Menno

2013-05-10 13:47:27 +00:00
Commented May 10, 2013 at 13:47 — Menno
– Menno, Commented May 10, 2013 at 13:47

Suthan Bala · Accepted Answer · 2013-05-10 13:53:09Z

3

Well, you can start off by creating a PHP array of players, then loop through that array and use mysql to add each of those players into the database.

$players = array("John", "Jack", "Josh"..."80th Player");

foreach($players as $player):
$player = mysql_real_escape_string($player);
mysql_query("INSERT INTO `finalproject`.`Bremners` (`stud_id`, `stud_name`, `stud_goal`, `stud_assist`,
`stud_attendance`, `stud_goalie`) VALUES ('1', $player, '0', '0', '0', '0')")
endforeach;

I wouldn't use the mysql_.. to insert into database but just to give you the idea I used it.

edited May 10, 2013 at 13:53

answered May 10, 2013 at 13:46

Suthan Bala

3,3095 gold badges41 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Menno Over a year ago

Hmmm, and execute 80 queries instead of 1? Seems like a waste to me.

Adder Over a year ago

The real waste if you ever want to re-use the game is typing in 80 players as SQL, instead of having an interface where you input each player as you like, or the player registers.

Voitcus Over a year ago

@Adder I think he's inserting these rows for test purposes. He might not have created any interface yet. However, if there are only 80 rows using the PHP loop and send 80 queries is not as bad idea, as this solution is simple and has to be done (I suppose) only once.

Stephan · Accepted Answer · 2013-05-10 13:46:49Z

2

Try this

INSERT INTO `finalproject`.`Bremners` (
     `stud_id`, 
     `stud_name`, 
     `stud_goal`, 
     `stud_assist`,
     `stud_attendance`, 
     `stud_goalie`
)VALUES 
('1', 'test', '0', '0', '0', '0'),
('2', 'test1', '0', '0', '0', '0'),
('3', 'test2', '0', '0', '0', '0'),
....
('80', 'test79', '0', '0', '0', '0');

answered May 10, 2013 at 13:46

Stephan

8,1103 gold badges40 silver badges42 bronze badges

4 Comments

Voitcus Over a year ago

If all stud_* columns have default values, probably , '0', '0', '0', '0' would not be necessary.

mconlin Over a year ago

Some thoughts that might make your life better use an auto increment column for stud_id so that you can let the db manage that id for you by using insert.. values(null, ..) the db will increment the id.

Stephan Over a year ago

@Voitcus i agree it would be much simpler :)

Stephan Over a year ago

@mconlin yes that's also a valid point with your advice and the one from Voitcus you can reach something like INSERT INTO table (name) VALUES ('test'),('test1')...

Collectives™ on Stack Overflow

Add multiple values to table mysql

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related