Running CREATE TABLE query throug PHP

2
votes

I'm running the following query throug a PHP script

DROP TABLE IF EXISTS `address`;CREATE TABLE `address` (  `Id`
int(11) NOT NULL auto_increment,   `EntityId` int(11) NOT NULL,  
`sStreet1` varchar(50),   `sStreet2` varchar(50),   `sCity`
varchar(50),   `sLoc` varchar(50),   `sPv` char(2),   `sState`
varchar(50),   `sZip` varchar(10),   `sType` varchar(50),  
`FiscalAddress` tinyint(1) NOT NULL,   PRIMARY KEY  (`Id`) );

I can run this query trough different clients (such as Navicat) without problems, but running this through PHP will output the following

$result = mysql_query($query);
if (!$result) {
    $message  = 'Invalid import content: ' . mysql_error() . "\n\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

this will output the following:

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 'CREATE TABLE address ( Id int(11) NOT NULL AUTO_INCREMENT, EntityId int(11) ' at line 2 Whole query: DROP TABLE IF EXISTS address; CREATE TABLE address ( Id int(11) NOT NULL AUTO_INCREMENT, EntityId int(11) NOT NULL, sStreet1 varchar(50), sStreet2 varchar(50), sCity varchar(50), sLoc varchar(50), sPv char(2), sState varchar(50), sZip varchar(10), sType varchar(50), FiscalAddress tinyint(1) NOT NULL, PRIMARY KEY (Id) );

what am I doing wrong???

2
phpmysql
Switch to the mysqli extension, it is able to run multiple queries (as you do here, but the mysql extension you use is not able to do that) php.net/manual/en/mysqli.quickstart.multiple-statement.php and it is easy to change from your existing code to the new code: php.net/manual/en/mysqli.quickstart.dual-interface.php – hakre
Switch to PDO instead of mysql_*~! – hjpotter92

2 Answers

2
votes

The issue is not the CREATE TABLE statement, but rather that mysql_query() does not support executing multiple statements in one call. Execute the DROP TABLE statement first in a separate mysql_query() call, then execute the CREATE TABLE.

$dt = mysql_query("DROP TABLE IF EXISTS `address`");
if ($dt) {
  // Execute CREATE TABLE separately...
  $ct = mysql_query("CREATE TABLE...........");
  if (!$ct) {
     // Proceed with the rest of your code.
    $message  = 'Invalid import content: ' . mysql_error() . "\n\n";
    $message .= 'Whole query: ' . $query;
    die($message);
  }
}
else {} // DROP TABLE call failed. Handle appropriately.
0
votes

Thank Mihai you have pointed my in the right direction... in this case the correct way is to msqli

this is the full working code:

function runImportQuery($query = '') {

    $mysqli = new mysqli($this->DATABASE_HOST,$this->DATABASE_USER,$this->DATABASE_PASS,$this->DATABASE_DB );

    if ($mysqli->connect_errno) {
            $message = "Connect failed: ". $mysqli->connect_error;
    }else{

        $result = $mysqli->multi_query($query);
            // Check result
            // This shows the actual query sent to MySQL, and the error. Useful for debugging.
            if (!$result) {
                $message  = "Failed to run query: (" . $mysqli->errno . ") " . $mysqli->error ."\n\n\r";
                $message .= 'Whole query: ' . $query."\n\n\r";
                return $message;
            }else{
                $message  = "Import successfully completed!";

            }

    }
    mysqli_close($link);
    return $message;

}