[Nikas]

What's wrong with this ?
I have a set of SQL query inside .sql file. and php code to runs it.

PHP code that runs it.

PHP Code:

$fp = fopen('schema/mysql.sql', 'r');

    $schema = fread($fp, filesize('schema/mysql.sql'));

    fclose($fp);

    

    $sqls = explode("\n", $schema);

    foreach ($sqls as $sql)

    {

        $pos = strpos($sql, 'phpdesk_');

        $table = substr($sql, $pos);

        $pos = strpos($table, '`');

        $table = substr($table, 0, $pos);



        if($db->query($sql))

        {

            if ( preg_match("/CREATE TABLE (\S+) \(/", $sql ))

            {

                echo 'Table Created Successfully: '.$table."<br />\n";

            }

        }

        else

        {

            echo 'Cannot Create Table: '.$table."<br />\n";

        }

    } 

SQL Query

DROP TABLE IF EXISTS `phpdesk_admin`
CREATE TABLE `phpdesk_admin` (
  `id` int(255) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  `pass` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(255) NOT NULL DEFAULT '',
  `notify_pm` int(1) NOT NULL DEFAULT '1',
  `notify_response` int(1) NOT NULL DEFAULT '1',
  `notify_ticket` int(1) NOT NULL DEFAULT '1',
  `tppage` int(255) NOT NULL DEFAULT '25',
  `signature` mediumtext NOT NULL,
  PRIMARY KEY  (`id`)
) 

It gives me this error.

Code:

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 '' at line 1Cannot Create Table: phpdesk_admin
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 '`id` int(255) NOT NULL default '0',' at line 1Cannot Create Table:
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 '`name` varchar(255) NOT NULL default '',' at line 1Cannot Create Table:
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 '`pass` varchar(255) NOT NULL default '',' at line 1Cannot Create Table:
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 '`email` varchar(255) NOT NULL default '',' at line 1Cannot Create Table:
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 '`notify_pm` int(1) NOT NULL default '1',' at line 1Cannot Create Table:
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 '`notify_response` int(1) NOT NULL default '1',' at line 1Cannot Create Table:
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 '`notify_ticket` int(1) NOT NULL default '1',' at line 1Cannot Create Table:
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 '`tppage` int(255) NOT NULL default '25',' at line 1Cannot Create Table:
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 '`signature` mediumtext NOT NULL,' at line 1Cannot Create Table:
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 'PRIMARY KEY (`id`)' at line 1Cannot Create Table: PRIMARY KEY (
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 ')' at line 1Cannot Create Table:

I copied the query over to phpmyadmin and run it without any problem.

However, this query doesn't looks like there's problem.

DROP TABLE IF EXISTS `phpdesk_admin`
CREATE TABLE `phpdesk_admin` (  
`id` int(255) NOT NULL DEFAULT '0',  
`name` varchar(255) NOT NULL DEFAULT '',  
`pass` varchar(255) NOT NULL DEFAULT '',  
`email` varchar(255) NOT NULL DEFAULT '',  
`notify_pm` int(1) NOT NULL DEFAULT '1',  
`notify_response` int(1) NOT NULL DEFAULT '1',  
`notify_ticket` int(1) NOT NULL DEFAULT '1',  
`tppage` int(255) NOT NULL DEFAULT '25',  
`signature` mediumtext NOT NULL,  
PRIMARY KEY  (`id`)) 

It's just one of the query in the sql file. The latter SQL query file works, but it's outdated. Thus, I used another updated SQL file, which is the former. And it doesn't allows me to create.

Any idea?

[UnrealEd]

The reason for the error lies within this line:

PHP Code:

$sqls = explode("\n", $schema); 

This lines thells php to split the content of $schema on each "\n" php finds in the string.

The query you posted is definietely a multiline query, therefore it will be split over several array-items ($sqls is an array). Basically you will be running queries like these:

DROP TABLE IF EXISTS `phpdesk_admin` 

next query:

CREATE TABLE `phpdesk_admin` ( 

next query:

`id` int(255) NOT NULL DEFAULT '0', 

and so on.

As you can see, only the first query is correctly formatted, and correct syntax, and will therefor be run. The others will rresult in an error right away as it's completely invalid syntax.

Instead of splitting on "\n" (newlines), you should be splitting per query.
I'm not 100% sure, but I thought you could use mysqli_query to execute several queries at the same time. That way you could simply pass on the data in the .sql file, and run it on your server.
Or..., and this is probably the best way, you could run the following query:

source 'schema/mysql.sql 

[Nikas]

So in another words, if I change the queries to single line. The query would works fine?

And thanks for your help!

[Nikas]

Alright. I have remove all the empty space and put all to one line for a single query and it works fine now.

Thanks for your detailed explanation.

[job0107]

Originally Posted by Nikas

So in another words, if I change the queries to single line. The query would works fine?

And thanks for your help!

What UrealEd is trying to say lies in the problem with this line of code:

PHP Code:

$sqls = explode("\n", $schema); 

When you use "\n" you are splitting up the data in $schema by line brakes.
The problem with doing that, is the queries can span multiple lines, and you are breaking the queries up into individual lines at every line break.
And your getting the queries one line at a time, when you need to get the whole query. So you need to define away to separate the queries and explode on that value.