foreach to insert multiple rows...

[Kristoff1875]

Hi guys. I currently have the following:

 

$UserID1

$TeamID1

$Points1

 

Where the value goes from 1 to 24, I need to insert all of these in to their own rows in a database how would I be best off doing that? Would I be able to use a foreach statement whereby the number increases? Or is there another way that doesn't involve doing 24 inserts?

 

Thanks in advance!

[Jacques1]

Hi,

 

you have 72 numbered variables? Why? There's this thing called array, you know?

 

Put the values into a proper array, then loop through the array and do one INSERT query (or better: a prepared statement) per dataset.

[Ch0cu3r]

 

 currently have the following:

 

$UserID1

$TeamID1

$Points1

 

Where the value goes from 1 to 24

You mean to say you have those variables and they are numbered 1 through to 24? eg $UserID1, $UserID2 ... $UserID24 etc

If thats the case you should not doing that, you should be using arrays. Naming variables like that is bad practice.

 

Can you tells use how those variables are defined? We can help you to convert those variables to an array if you are unsure

 

 

 

ould I be able to use a foreach statement whereby the number increases? Or is there another way that doesn't involve doing 24 inserts?

An insert query can insert more than one record at a time. example structure of a multi-insert query

INSERT INTO table_name (... cols ...) VALUES (... value set 1...), ( ... value set 2...), //etc.. 

As you see each set of values for each row are wrapped in parenthesise. 

 

And, Yes you'd use a loop to build query.

[Kristoff1875]

I'm getting the data from a form and using this to get the variables:

foreach($_POST as $key => $value){
  $$key = $value;
}

I knew of the security issues, but this is an admin area only accessible by me, but Is that bad practice?

[Jacques1]

Ugh. This is like the evil twin of the register_globals brainfart that PHP used to have in the dark ages until they finally fixed their security.

 

Do you realize that this allows any user to set or overwrite any variable in the application?

 

 

 

this is an admin area only accessible by me

 

Given the code, I highly doubt that. 

[Kristoff1875]

Yes, I do realise that... Nobody else has access to it.

[Jacques1]

Again: I doubt that. Given this gigantic security risk (in the admin area!), what makes you think the rest of your security is absolutely perfect?

 

I don't even get the point of this weird variable import. The values are all in the $_POST array already. What's the point of stuffing them into separate variables?

[Kristoff1875]

It's something that was suggested to me last night for gathering all of the post data, normally I'd have assigned all of the data to variables manually.

[Jacques1]

Well, it simply makes no sense. How is $x better than $_POST['x']? Sure, it saves you 9 characters. But it clutters the global namespace and kills the security.

 

If you absolutely definitely must import the variables, at least use extract() with the overwrite flag set to EXTR_SKIP.

[Kristoff1875]

If i'm getting

$_POST['UserID1'] $_POST['TeamID1'] $_POST['Points1'] $_POST['UserID2'] $_POST['TeamID2'] $_POST['Points2']

I'm completely confused of how to go from that to 

INSERT INTO table_name (... cols ...) VALUES (... value set 1...), ( ... value set 2...)

Where each numerical value is posted to it's own row without specifically assigning each one of the values individually.

Edited April 9, 2014 by Kristoff1875

[Jacques1]

Instead of numbering the values, put them into an array. PHP has a special syntax for this:

<input name="whatever[0][user_id]">
<input name="whatever[0][team_id]">
<input name="whatever[0][points]">
 
<input name="whatever[1][user_id]">
<input name="whatever[1][team_id]">
<input name="whatever[1][points]">
 
etc.

Then your $_POST array will be filled with nice subarrays consisting of a user_id, a team_id and points. Putting this into the database is trivial now:

 

 

<?php
 
foreach ($_POST['whatever'] as $whatever)
{
query('
INSERT INTO
something
SET
user_id = ' . escape($whatever['user_id']) . ',
team_id = ' . escape($whatever['team_id']) . ',
points = ' . escape($whatever['points']) . ', 
');
}

Yes, I do recommend making separate queries instead of messing with one big INSERT query.

Edited April 9, 2014 by Jacques1

[Barand]

Yes, I do recommend making separate queries instead of messing with one big INSERT query.

 

Can't imagine why anyone would want a single, efficient query when they can kill server performance with dozens of single ones.

 

[Kristoff1875]

I thought that too. I've got it working now how Jacques1 suggested, so thanks Jacques1.

 

However it's using multiple inserts rather than a single one.

[Jacques1]

“Kill server performance” because of 23 trivial queries which he probably only does once in a while? C'mon. 

 

Micro-optimizations like this are just silly. What matters is security and simplicitly, and this can be achieved best by inserting the rows one by one with a prepared statement.

Edited April 9, 2014 by Jacques1

[jazzman1]

So, according the sql docs (feature ID F641) inserting multiple data of rows at a time is optional in SQL and MySQL server supports that, which is nice.

 

 

INSERT INTO tablename
VALUES (0,’zero’) , (1,’one’) , (2,’two’)

which may be read as a shorthand for

INSERT INTO tablename VALUES (0,’zero’);
INSERT INTO tablename VALUES (1,’one’);
INSERT INTO tablename VALUES (2,’two’);

I think the performance should be the same when you loop the whole bunch of insert statements or just looping only their values.But I want to mention few things about insert statement.

It is always a good practice to name all columns into which you are inserting values because:
1) The INSERT statement is much more descriptive
2) You can verify that you are providing the values in the proper order based on the column names
3) You have better data independence. The order in which the columns are defined in the table does not affect your INSERT statement.
 

Edited April 9, 2014 by jazzman1

[jazzman1]

@Jacques, revisiting this thread you're looping php query function multiple times , that's wrong. You need to call a query function once making sql multiple data of rows at a time or just muptiple inserts as Barand (master) means

Edited April 9, 2014 by jazzman1

[Jacques1]

I have no idea what you think is wrong.

 

I suggest making one INSERT query per dataset. This should be done with a prepared statement:

<?php

// I assume we're using PDO
$insert_something_stmt = $database->prepare('
	INSERT INTO
		whatever
	SET
		user_id = :user_id,
		team_id = :team_id,
		points = :points
');

// now the loop
foreach ($_POST['something'] as $something)
{
	$insert_something_stmt->execute(array(
		'user_id' => $something['user_id'],
		'team_id' => $something['team_id'],
		'points' => $something['points'],
	));
}

This is by the far the simplest and most secure solution.

 

Another option would be to assemble one big INSERT query with multiple rows as suggested by Barand. In my opinion, this is a silly micro-optimization.

[jazzman1]

No, I have to disagre, so if execute statement occurred in a loop, the SQL statement would be prepared, executed and released upon every iteration, the statement only prepared once, will give us a huge performance benefit.

[ahaberman25]

You can use implode 

 

foreach($update_data as $field=>$data) {

$update[] = '`' . $field . '` = \'' . $data . '\'';

}

 

mysql_query("UPDATE `admin` SET " . implode(', ', $update) . " WHERE `id` = $session_user_id");

[Jacques1]

No, I have to disagre, so if execute statement occurred in a loop, the SQL statement would be prepared, executed and released upon every iteration, the statement only prepared once, will give us a huge performance benefit.

 

I really wonder what you're reading into my code.

 

I create the prepared statement before the loop. Then I execute it in the loop. So it's one prepared statement which gets executed multiple times. This isn't the first time I'm using PHP.

 

And I repeat: Worrying about 23 simple INSERT queries is just silly. I don't understand why we're spending half of the discussion on this useless triviality. Should the OP ever experience performance issues, it's certainly not because of 23 queries.

[jazzman1]

 

I create the prepared statement before the loop. Then I execute it in the loop. So it's one prepared statement which gets executed multiple times. This isn't the first time I'm using PHP.

 

No. When you called the execute statement more than once a new sql prepared statement is required to be prepared, then executed and released at the end. Anyways.....I also stoped the discussion here.

[Jacques1]

Yeah.

 

I really have no idea how you think prepared statements work or what you're objecting against. The last post doesn't even make sense to me.

 

The execute() method does exactly what it says: It executes the prepared statement. It does not create a new statement, and it does not destroy the current one. You can easily verify that yourself:

<?php

$database_options = array(
    PDO::ATTR_EMULATE_PREPARES => false
    , PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    , PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
$database = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'root', '', $database_options);



show_prepared_statements();

// *One* prepared statement
$stmt = $database->prepare('SELECT 1 = :x');
// ... getting executed 10 times
for ($i = 1; $i <= 10; $i++)
{
    $stmt->execute(array('x' => $i));
}

echo '<br>';
show_prepared_statements();



function show_prepared_statements()
{
    global $database;

    $statements = $database->query("SHOW SESSION STATUS LIKE 'Com_stmt_%'");
    foreach ($statements as $statement)
    {
        echo $statement['Variable_name'] . ': ' . $statement['Value'] . '<br>';
    }
}

Besides the two SHOW SESSION queries, we have 1 prepared statement which gets executed 10 times. Just what we (or at least I) expected.

 

The prepare()methods creates a prepared statement. And then you can execute it as often as you want using the execute() method. I don't know how else I could explain this. I've never met anybody who would doubt this simple mechanism.

[jazzman1]

 

I've never met anybody who would doubt this simple mechanism.

 

I'm glad you've met me @Jacques

 

So, I'm sure you know what a driver is, right? The PDO driver allows us to deal with databases like mysql, firebird and so forth using their resources and all functions you've been mentioned above are actualy database functions or sql statements . So, from here I'm thinking that all prepared, parameterized queries and execute statements are being occured inside DB server, but the server side application just cache their values/status. The easiest way to see what happens is to create a stored procedure to see how the statement would be prepared, executed and released in the loop.

Edited April 10, 2014 by jazzman1

[Jacques1]

It doesn't matter what you think happens. Where's your demonstration? I've posted mine.

 

Just to make sure I understand you correctly: You're saying that PDO makes it impossible to reuse a prepared statement? That it somehow magically closes the statement, recreates it and also fakes the session statistic to confuse us? Um, yeah, that's an interesting theory.

 

Where do you see that in the PDO code? I see exactly two occurences of mysql_stmt_prepare(): Once for PDO::query(), once for PDO::prepare().

 

https://github.com/php/php-src/search?q=dbh-%3Emethods-%3Epreparer&ref=cmdform

 

Please point to the exact code that does what you think.

 

 

 

I'm glad you've met me @Jacques

 

Not sure if I am.

[jazzman1]

I just said how database prepared, executed and destroyd the sql statements. What demonstration you need to see? About performance of looping execute statement and not looping?  

Edited April 10, 2014 by jazzman1