Php mysql insert multiple rows from array

PHP
1

Hello forums, I’m trying to copy a table data from one database into another. (vbulletin posts to wordpress comments) I have this:

mysql_connect('localhost','root','');
$q = mysql_query("SELECT * FROM vbulletin.post WHERE threadid = $vb_id");
while ($r = mysql_fetch_assoc($q)){
$sql=array('user' => $r['username'], 'text' => $r['pagetext']); //more colum data here I just cut it for simplicity's sake

	}

Question: how can I insert the array to the WP database without calling an insert query inside the while loop Thanks

2

not the best, but probably will help


while ($r = mysql_fetch_array($q)) {
    $user = $r['username'];
    $text = $r['pagetext'];

    $insert_str .= "('$user','$text'),";
}

$insert_str = substr_replace($insert_str, '', -1, 1);

$q_insert_wp = "INSERT INTO WP_TABLE VALUES" . $insert_str;
mysql_query($q_insert_wp) or die(mysql_error());
3

leelong, you forgot extremely important thing - escaping values using mysql_real_escape_string()
and yes, not the best approach. you don’t have to assign single variables when you can use a loop over array, or even implode() function

stonedeft, you can use just one INSERT SELECT mysql query.
dunno what do you mean “without calling an insert query” though. what’s wrong with calling it inside the while loop?

4

I’m pretty much concern with the server load, when I call the insert query inside the loop it execute the insert query on every row items. Example if I have 1500 rows then it will call “insert” 1500 times w/c is too much…

$q = mysql_query(SELECT * FROM vbulletin.post WHERE threadid = 12);
  while($r = mysql_fetch_assoc($q)){

    mysql_query(INSERT INTO wordpress.wp_comments )//blah blah blah

}

So I’m thinking maybe put all the results query in an array and call the insert query. I think implode is a good one.

Also leelong doesn’t need to escape the string as the data comes straight from the vbulletin databse w/c is already escaped.

5

Using the implode function …I googled this from stackoverflow.com


$sql = array();
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['text']).'", '.$row['category_id'].')';
}
mysql_real_query('INSERT INTO table (text, category) VALUES '.implode(',', $sql));

Cheers

6

I’m pretty much concern with the server load

Is it single, not repeated task? So, don’t worry for the server load

I’m thinking maybe put all the results query in an array and call the insert query.

your query may exceed max_allowed_packet

the data comes straight from the vbulletin databse w/c is already escaped.

No, it is not.
Many people don’t understand escaping well.
You’re not doing escaping for the database. But for the query only.
After query got executed all escaping disappears.
You have to always escape any data goes to the query.

Anyway you don’t need any PHP code for your task
As I said above, single INSERT SELECT query will do everything
something like

INSERT into wordpress.wp_comments (field1, field2) SELECT field1, field2 FROM vbulletin.post

that’s all
just name fields respectively

7

I tested both versions couples of 10 times.

Version 1 without array and implode():–


for ($i = 0; $i <= 500000; $i++) {
    $user = 'username string';
    $insert_str_con .= "('$user'),";
}

$insert_str = substr_replace($insert_str_con, '', -1, 1);

Average memory usage 10MB - 15MB.

Version 2 with array and implode:–


for ($i = 0; $i <= 500000; $i++) {
    $user = 'username string';
    $insert_str_arr[] = "('$user')";
}

$insert_str = implode(',',$insert_str_arr);

Average memory usage 80MB - 100MB.

The array implode() version always consume about a whopping 10 times more resource. But both accomplish exactly the same thing.

I might be wrong, since I used ONLY “Windows Task Manager”(Ctrl + Alt + Delete) to monitor memory usage.

Your call?

8

I meant to implode $r array.
You’re adding word “array” to your string, not array itself.

9

ooop…sorry i mistakenly copied range(‘A’,‘Z’) to the test.

now change to pure string.

same result of course.

10

You did a good job.
Using array is redundant if we need a string as a result.
It does matter only for huge amounts of data, of course.

11

the best approach to what TS want is single INSERT query, but since he was asking how to insert datas inside a loop, that was all i have in my mindset…

12

Hmm I wonder why the implode version is causing too much memory, so it’s definetly a no go. Thanks leelong

@Shrapnel_N5
Thanks mysql-real-escape_string noted

cheers!

13

This one is just perfect WOW, however it won’t work for me as I have to truncate the data first, like stripping bbcodes , replacing smiles, removing quote tags and images etc etc … before it goes to wordpress

thanks anyway thread bookmarked for future reference

14

I wonder why the implode version is causing too much memory

Because it uses memory twice - for array and then for string. And array itself needs some overhead.

15

Not checked, but you should get the idea :


// Connect
$db = new PDO('mysql:host=localhost;dbname=vbulletin', 'root', '');

// Prepare select statement
$sth = $db->prepare('SELECT * FROM vbulletin.post WHERE threadid = :id');

// Bind vars to select statement
$sth->bindParam(':id', $vb_id, PDO::PARAM_INT);

// Prepare insert statement
$insert = $db->prepare('INSERT INTO wordpress.WP_TABLE VALUES(:user, :text)');

// Execute select statement
$sth->execute();

// Fetch data
while($row = $sth->fetch() !== false){
  // Bind vars to insert statement
  $insert->bindParam(':user', $row['user'], PDO::PARAM_INT);
  $insert->bindParam(':text', $row['text'], PDO::PARAM_STR);
  // Execute insert
  $insert->execute();
}

A second PDO object may be needed if the two tables are on different servers.

16

PDO hmmm thats new to me …

17

Prepared statements help to protect you from most of the SQL Injection problems that you might face.

Here is a good page that demonstrates the types of injection attacks that are commonly used.

SQL Injection Attacks by Example

18

Oh I see, isn’t mysql_real_escape_string not enough to combat sql injections?

19

No, that isn’t true. Prepared statements and variable binding just makes it so that its virtually impossible to forgot to cleanse incoming user accessible input. if you know what you are doing then there is no need to hit the database twice using prepared statements. Its just many new to programming in PHP/MySQL either don’t know or forgot to cleanse potentially dangerous user input. So by using prepared statements and binding one can eliminate a potential disaster due to forgetting to cleanse user controlled input. Binding cleanses input without any additional work of the developer so long as placeholders are being used effectively and one is not creating their own hard coded clauses from user input without placeholders.

20

Speaking of SQL injection, do not forget that dynamic SQL is not limited to literals, but identificators or even operators sometime being dynamic too. And both techniques has nothing to do with it. Dynamic order by is the fresh example.
Just bear in mind that using prepared statements/escaping is not a synonym for “100% safety”.