Bdtjtk

I have created a web you can upload and download files - everything works perfect. But now, I want to create a init file, that delete old records in database and create a new tables in it.

So I write this:

$command = "

IF OBJECT_ID(`".$database.".Users`) IS NOT NULL 

    DROP TABLE ".$database.".Users; 

IF OBJECT_ID(`".$database.".Uploads`) IS NOT NULL 

    DROP TABLE ".$database.".Uploads; 



CREATE TABLE `Users` (

  `Id` int(11) NOT NULL,

  `User` text NOT NULL,

  `Password` text NOT NULL,

  `Permission` int(11) NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;



ALTER TABLE `Users` ADD PRIMARY KEY (`Id`);

ALTER TABLE `Users` MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=0;



CREATE TABLE `Uploads` (

  `Id` int(11) NOT NULL,

  `Name` text NOT NULL,

  `User` text NOT NULL,

  `Comment` text NOT NULL,

  `Path` text NOT NULL,

  `Permission` int(11) NOT NULL,

  `Date` text NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;



ALTER TABLE `Uploads` ADD PRIMARY KEY (`Id`);

ALTER TABLE `Uploads` MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=0;

";

$result = mysqli_query($conn, $command) or die(mysqli_error($conn));

I think, that code is right (but obviously not). When I run it, SQL throws an error:

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 'IF OBJECT_ID(db.Users) IS NOT NULL DROP TABLE db.User' at line 1**.

This means that it don’t have a problem with connection to SQL database.

I tried instead of IF OBJECT_ID use IF NOT EXISTS, but it doesn't works too. Can anybody tell me if multi-line SQL command is this problem or if it is something else?

Note: I use 5.5.37 version of MariaDB (if it helps)

IF is not a valid SQL statement in MySQL / MariaDB.

The IF OBJECT_ID(...) statement in the question appears to be a Transact-SQL (Microsoft SQL Server) construct.

The equivalent functionality in MySQL would be achieved with

DROP TABLE IF EXISTS foo.mytable ;

(I expect this would work in MariaDB 5.5, but I haven't verified.)

Note that if the table doesn't exist, the execution of the statement will raise a warning. (A warning message, not an error message.)

The mysqli_query function runs a single statement. To run multiple statements, we can use mysqli_multi_query function, documented here:

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

never used OBJECT_ID but what you want seem to be easily doable with
"drop table if exists users;"

As far as concerns, OBJECT_ID does not exist in mysql, only in mssql. Searching for OBJECT_ID mysql 8.0 reference manual does not retun anything meaningful. Even if it existed, your syntax for IF block does not look good : you want IF...THEN...END.

To fix the error, you can replace this :

IF OBJECT_ID(`".$database.".Users`) IS NOT NULL 

DROP TABLE ".$database.".Users; 

IF OBJECT_ID(`".$database.".Uploads`) IS NOT NULL 

DROP TABLE ".$database.".Uploads; 

With :

DROP TABLE IF EXISTS ".$database.".Users;

DROP TABLE IF EXISTS ".$database.".Uploads;