Read query from file

[K0ns3rv]

Hello fellow daniwebians.

So I've encountered this problem with querys in PHP.
In the system I am working on at the moment we've created an install file which tests the db connection, creates the db structure e.t.c. But when creating the database the query fails.
The query it self i stored in a .sql file on the server and is read and executed with the following piece of code.

$connect = connect();
    if ( $connect )
    {
        $file = fopen('../SQL/TimeManagement.sql','r');

        $query =  fread($file,  filesize("../SQL/TimeManagement.sql"));
        $query = str_replace(array("\r\n","\r","\n"),"",$query);
        mysql_query($query);
        fclose($file);
    }

But for some reason the query fails. The strange thing is that if I just dump the query and copies it into Navicat it works.

This is where it fails

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 'DROP TABLE IF EXISTS `News`;CREATE TABLE `News` ( `NewsID` int(11) NOT NULL AUT' at line 1

Cheers
//K0ns3rv

[pritaeas]

mysql_query does not support multiple queries. You can use mysqli_multi_query instead.

[K0ns3rv]

mysql_query does not support multiple queries. You can use mysqli_multi_query instead.

I realised this, but I used a different method then mysqli_multi_query.

if ( $connect )
    {
        $file = fopen('../SQL/TimeManagement.sql','r');

        $data =  fread($file,  filesize("../SQL/TimeManagement.sql"));
        $data = str_replace(array("\r\n","\r","\n")," ",$data);

        $queries = preg_split("/;+(?=([^'|^\\\']*['|\\\'][^'|^\\\']*['|\\\'])*[^'|^\\\']*[^'|^\\\']$)/", $data);
        //$queries = explode(";",$data);
        foreach ($queries as $query )
        {
            if (strlen(trim($query)) > 0)
            {
                mysql_query($query);
                $toReturn['error'] .= mysql_error();
            }
        }
    }

[pritaeas]

Also possible of course. The value of this is that you can easily detect a faulty statement, without breaking the entire file.

[K0ns3rv]

This worked with our database structured so it's fine :)
Btw you don't happen to know what settings have to be changed to use

$file = fopen("file.txt","[B]w[/B]");

?

[pritaeas]

You need write permissions on the file. Depends on your system.