SQL query not working in php but working in phpmyadmin

Question

I am messing around with OOP mysqli. And I stumbled upon this problem:

There was an error running the query [You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TABLE IF NOT EXISTS engine.users ( ID INT(11) NOT NULL, USERNAME ' at line 1]

This is the PHP code:

    function InstallDB($db){
    $sql = 
    "CREATE DATABASE IF NOT EXISTS `$this->dbname`; ".
    "CREATE TABLE IF NOT EXISTS `$this->dbname`.`$this->users` ( ".
    "`ID` INT(11) NOT NULL, ".
    "`USERNAME` varchar(60) NOT NULL,".
    "`PASSWORD` varchar(60) NOT NULL,".
    "`EMAIL` varchar(100) NOT NULL,".
    "`IMAGE` varchar(250) NULL,".
    "`LEVEL` INT(11) NOT NULL DEFAULT '1'".
    ") ENGINE=InnoDB  DEFAULT CHARSET=utf8;".

    "CREATE TABLE IF NOT EXISTS `$this->dbname`.`$this->settings` (".
    "`DOMAIN` varchar(60) NOT NULL,".
    "`SLOGAN` varchar(255) NOT NULL,".
    "`EMAIL` varchar(100) NOT NULL".
    ") ENGINE=InnoDB  DEFAULT CHARSET=utf8;";

    if(!$result = $db->query($sql)){
        die('SQL['.$sql.']<br>There was an error running the query [' . $db->error . ']<br>');
    }

    echo "Installed!<br>";
}

Tried different quote marks, no luck. I also echoed the $sql, copied it and executed in phpmyadmin, it worked perfectly. But still it won't do anything inside of PHP. Thanks.

Why all the concatenation? Simplify your code. PHP allows for line breaks in strings. use them. — John Conde, Apr 18 '17 at 19:38
I think the problem with the multiple command in one query, because the error message appeared from second row. — Peter, Apr 18 '17 at 19:42
@JohnConde this is only the result of trying multiple things :S — Morsus, Apr 18 '17 at 19:48

score 1 · Accepted Answer · answered Apr 18 '17 at 19:43

1

Use multi_query instead query, because you would like to multiple query not only one. As you can see the issue appeared right away after first query.

http://php.net/manual/en/mysqli.multi-query.php

answered Apr 18 '17 at 19:43

Peter

748
6
20

Yep, that worked. I was thinking about this, I even googled but all I could find was transactions witch I didn't want to use. Thank you. – Morsus Apr 18 '17 at 19:50
could you accept my answer as a right answer please? – Peter Apr 18 '17 at 19:55
1

Sure, there was a 2min cooldown so I couldn't do it right away. Thank you again. – Morsus Apr 18 '17 at 20:22

SQL query not working in php but working in phpmyadmin

1 Answers1