Database insert

Yii 1.1 General Discussion
1

Is there a way to insert large amount of data into the database without using foreach(I mean without inserting a line at a time)?

2

You can create script file of sql and execute shell command

exec("mysql - u user_name -p your_password database_name < file_name.sql");

3

some kind like SELECT INTO in SQL ??

4

I get an array of data from another site(about 1500 rows)in about 2 sec but when I want to insert the rows into my database(foreach row insert row data) it took me more than 30 min to insert(it is possible that apache crashed but it is set to crash on more than 10 min which is also a long time for the insert).

5

Make cron service witch will synchronize data. if you are getting a lot of data. make insert only part of it, not all of it.

Sorry for my English :)

6

You should try and create a very big statement, for every row of data you need to insert you will need to add it to the sql statement, then when you’ve went through all of the rows you should execute the entire query in 1 command,

I’ve done this for a project of mine and executing a sql insert statement with about 3000 records takes 2 seconds.

7

hi!

Plz EdoFre could you share with us how you did it?

maybe this can help

regards

8

Sure, the way it works is you go through your array or file and add every line to a query string, After you’ve gone through all the data you execute the query string and insert all the records in one big statement. The general layout of the process is like this:

Prepare the string for elements to be added in the beginning


private $query = "INSERT INTO user(first_name, last_name, email) VALUES ";

Then loop through your data and add all the entry’s to the query string ( for the example’s sake I’ve got an array containing all the users I want to add called $users )




foreach($users as $user)

{

    $this->query .= "('".$user['first_name']."', '".$user['last_name']."', '".$user['email']."'),";

}

After that has been done you will want to execute the query containing all the items, Note that I use the substr function to remove the last comma in the query!


Yii::app()->db->createCommand(substr($this->query, 0, -1))->query();

I hope this helps you.