CODEWITHSUNDEEP
phpmysqlsqlmariadb
Hackrrr
Hackrrr

Reputation: 338

How to fix SQL error with dropping a table in PHP?

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)

Upvotes: 0

Views: 747

Answers (3)

spencer7593
spencer7593

Reputation: 108400

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

Upvotes: 2

GMB
GMB

Reputation: 222482

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;

Upvotes: 1

Sourabh Swarnkar
Sourabh Swarnkar

Reputation: 131

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

Upvotes: 0

Related Questions