Member Avatar for 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

  • 2 Contributors
  • 5 Replies
  • 152 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for 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();
            }
        }
    }
Edited by K0ns3rv because: n/a
Member Avatar for pritaeas

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

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

?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.