0

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

2
Contributors
5
Replies
6
Views
7 Years
Discussion Span
Last Post by pritaeas
0

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();
            }
        }
    }

Edited by K0ns3rv: n/a

0

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

0

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]");

?

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.