Import MySQL file in PHP

Question

I have a MySQL file which I want to import via PHP 5.

In the name of user friendliness the user should not use tools like PHPmyadmin etc. Just hit a button and the file will get imported.

I have already created code to upload the file to a location on the server.

The file looks like this:

INSERT INTO products VALUES ('', '0', '10', '', '1', 'be34112', '4536.jpg', '','','','0');
SET @master_id = LAST_INSERT_ID();
INSERT INTO products_description VALUES ('', '1', @master_id, '1', 'Kjole', '', 'beskrivelse', '2000', '25', 'kjole.xml', '', '', '');
INSERT INTO products_to_categories VALUES ('',@master_id,'5');
INSERT INTO products VALUES ('', @master_id, '10', '12', '1', 'be34112', '4536.jpg', '200','','','0');
SET @variant_id = LAST_INSERT_ID();
INSERT INTO products_description VALUES ('', '1', @variant_id, '1', 'Kjole', '', 'beskrivelse', '2000', '25', 'kjole.xml', '', '', '');
INSERT INTO options_to_products VALUES ('', @variant_id, '1', '1');
INSERT INTO options_to_products VALUES ('', @variant_id, '', '2');
INSERT INTO products VALUES ('', @master_id, '20', '17', '1', 'be34113', '4537.jpg', '200','','','0');
SET @variant_id = LAST_INSERT_ID();
INSERT INTO products_description VALUES ('', '1', @variant_id, '1', 'Kjole', '', 'beskrivelse æøå ÆØÅ & íjj´¨¨¨¨fdfd""', '3000', '25', 'kjole.xml', '', '', '');
INSERT INTO options_to_products VALUES ('', @variant_id, '1', '');
INSERT INTO options_to_products VALUES ('', @variant_id, '', '4');

This code is not from strangers. It is code my system generates. Reason why I do it this way is irrelevant for the question :) — Cudos, Apr 05 '10 at 09:01
Does it have to produce a "sql batch" file then? Couldn't it store the queries in a format that is easier to parse/separate again? E.g. via var_export()? Or -if the file can potentially become big- in a format that allows you to read the statements one by one (or in chunks)? — VolkerK, Apr 05 '10 at 09:09
With very big files, I would tend to try and split the files myself. I asked [a similar question](https://stackoverflow.com/questions/1883079/best-practice-import-mysql-file-in-php-split-queries) a while back and got pretty good results. — Pekka, Apr 05 '10 at 09:01

score 3 · Accepted Answer · answered Apr 05 '10 at 08:57

3

Load the file into a string and then pass it to mysqli::multi_query().

Standard disclaimers regarding untrusted sources for data/queries/executable-code et cetera apply.

answered Apr 05 '10 at 08:57

Amber

507,862
82
626
550

score 1 · Answer 2 · answered Apr 05 '10 at 08:59

1

you can pass it to mysqli_multi_query

answered Apr 05 '10 at 08:59

knittl

246,190
53
318
364

Import MySQL file in PHP

2 Answers2