MySQL | how to insert same row multiple times??

[Antiflot96]

Hey guys, im working on a revcms shop and i stumbled upon an issue.
Basically i have a furnipack shop where i need help with how i can insert same row like 10 times.
This is because i have a row for inserting for example a throne, and i want them to get 10 thrones, how do i do that? THis is the current row i use:

mysql_query("INSERT INTO `items` (`id`,`user_id`, `room_id`, `base_item`, `extra_data`) VALUES (NULL,'" . $_SESSION['user']['id'] . "','0','202','');

Picture of shop:

 

[MovieGuy]

mysql_query("INSERT INTO `items` (`id`,`user_id`, `room_id`, `base_item`, `extra_data`) VALUES 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202',''), 
(NULL,'" . $_SESSION['user']['id'] . "','0','202','')");

 

[streamhotel]

A primary key can't be NULL.

 

[Boraida]

A primary key can't be NULL.

How do you know it is? Hmm

You don't have to fill in an id when it already auto increments.

 

[streamhotel]

How do you know it is? Hmm

You don't have to fill in an id when it already auto increments.

I assume it is because mostly Habbo retro's using 'id' as a PRIMARY KEY in their database so specifying column 'id' with rows NULL isn't necessary I guess because it's using auto increment like you said.

 

[MovieGuy]

I assume it is because mostly Habbo retro's using 'id' as a PRIMARY KEY in their database so specifying column 'id' with rows NULL isn't necessary I guess because it's using auto increment like you said.

Yeah he could just keep it empty and remove id since it should be auto increment, but I just gave him an example from his example, so he just gets how it's done

 

[academic]

Hi Antiflot96,

First of all, I would recommend using the MySQLi library over the MySQL functions in PHP. As I'm sure everyone knows, most functions of the older library are pretty much deprecated. Not only is MySQLi much more equipped to dealing with issues like SQL injection and such, where MySQL isn't. Anyway...

You can accomplish this task quite easily.

<?php
// Create a new MySQLi object.
$mysqli = new mysqli("localhost", "root", "password", "database");

// Create a statement object.
$stmt = $mysqli->prepare("INSERT INTO items (user_id, room_id, base_item, extra_data) VALUES (?, ?, ?, ?)");
// For each question mark, put in the four variables.
$stmt->bind_param("iiis", $userid, $roomid, $baseitem, $extradata);

// Set the values for the placeholders above.
$userid = 12;
$roomid = 0;
$baseitem = 202;
$extradata = "";

// Create a loop that will loop for 10 iterations.
for($i = 0; $i < 10; $i++)
{
	$stmt->execute();
}

// Close our statement object.
$stmt->close();
// Close our connection object.
$mysqli->close();

// Done!
?>

In the code above, I have documented all lines, to make it as easy as possible to understand. Also, when you have a AUTO_INCREMENT column, you can drop the name in your INSERT INTO query, as your ID field (primary key) will work its value out automatically. I mean, it's AUTO_INCREMENT after all?

- academic