PHP script doesn't dump the SQL but phpMyAdmin does

Question

I try to dump my data thought php script but without command line. SO I create my .sql file with this script and then I try with my script:

$link = mysql_connect($host, $user, $pass);
mysql_select_db($name, $link);
$sqlFile = 'sql.sql';
$fsize = filesize($sqlFile);
$fopen = fopen($sqlFile, 'r');
$fread = fread($fopen, $fsize);
fclose($fopen);
mysql_query($fread) or die(mysql_error());
mysql_close();

When I try to dump data I got a error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE `ps_access` (`id_profile` int(10) unsigned NOT NULL,`id_tab` int(10' at line 1

But when I paste the sql in phpMyAdmin sql input tab theres no problem with this sql code:

DROP TABLE IF EXISTS `ps_access`;
CREATE TABLE `ps_access` (
`id_profile` int(10) unsigned NOT NULL,
`id_tab` int(10) unsigned NOT NULL,
`view` int(11) NOT NULL,
`add` int(11) NOT NULL,
`edit` int(11) NOT NULL,
`delete` int(11) NOT NULL,
PRIMARY KEY (`id_profile`,`id_tab`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

So where is my error in php script and how to fix it to dump the data?

Avarage SQL file is ~800KB

Best regards, George!

Does it work if you remove the first line `DROP TABLE IF EXISTS...` — okyanet, Jun 18 '12 at 01:04

score 1 · Answer 1 · answered Jun 18 '12 at 01:06

1

If your average sql file is about 800Kb and what you have shown, is part of it, you are having multiple sql statements in your string (apart from the two you have shown...) and you can only run a single sql query through the (deprecated...) mysql_query function.

answered Jun 18 '12 at 01:06

jeroen

91,079
21
114
132

Yes, its a part of all because I think You know how much is 800kb sql :) So any ideas how to dump the data? Regards! – Jun 18 '12 at 01:23

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

The error happens because mysql_query doesn't support multiple queries:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

(source)

Theoretically you could use explode + a foreach loop to send each query separately, but in practice this may not work as expected. See: Loading .sql files from within PHP

edited May 23 '17 at 12:12

Community

1
1

answered Jun 18 '12 at 01:06

Mahn

16,261
16
62
78

So If I believe in your words - There is no way to dump the data thought php or ...? – Jun 18 '12 at 01:24
1

You can if you use a driver that supports multiple queries at once, such as PDO or mysqli (as opposed to the deprecated mysql_* functions) E.g: http://stackoverflow.com/a/7178917/1329367 – Mahn Jun 18 '12 at 01:27
Hi, I do that with mysqli_multi_query() but the function may have some limit of length and I explode the file to few parts and then everything works fine :) Thank you! – Jun 18 '12 at 01:39

PHP script doesn't dump the SQL but phpMyAdmin does

2 Answers2