Jump to content

foreach to insert multiple rows...


Kristoff1875

Recommended Posts

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!
Link to comment
Share on other sites

 

 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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Kristoff1875
Link to comment
Share on other sites

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 by Jacques1
Link to comment
Share on other sites

“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 by Jacques1
Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

@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 by jazzman1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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 by jazzman1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by jazzman1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.