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
– okyanet, Commented Jun 18, 2012 at 1:04

Community · Accepted Answer · 2017-05-23 12:12:26Z

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, 2017 at 12:12

CommunityBot

11 silver badge

answered Jun 18, 2012 at 1:06

Mahn

16.7k17 gold badges66 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user546774 Over a year ago

So If I believe in your words - There is no way to dump the data thought php or ...?

Mahn Over a year ago

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: stackoverflow.com/a/7178917/1329367

user546774 Over a year ago

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!

jeroen · Accepted Answer · 2012-06-18 01:06:11Z

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, 2012 at 1:06

jeroen

91.8k22 gold badges118 silver badges132 bronze badges

1 Comment

user546774 Over a year ago

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!

Collectives™ on Stack Overflow

PHP script doesn't dump the SQL but phpMyAdmin does

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related