Robert Robert - 2 months ago 12
MySQL Question

Not Creating Table with PDO

I have a sql command that I am trying to execute using PDO and it executes true, but the table is not created. I have no idea what I am doing wrong. I looked around the internet to see if anyone else has had this problem, but I didnt really seem to find anything that fixes it. Here is the SQL Command

CREATE TABLE table_230
(
id VARCHAR(255),
cost VARCHAR(255),
title VARCHAR(255) NOT NULL,
package_quantity VARCHAR(255) NOT NULL,
package_cost VARCHAR(255) NOT NULL,
asin VARCHAR(15) NOT NULL,
rank VARCHAR(20) NOT NULL,
category VARCHAR(255) NOT NULL,
bb_price VARCHAR(255) NOT NULL,
seller_type VARCHAR(255) NOT NULL,
size_tier VARCHAR(255) NOT NULL,
amazon_fee VARCHAR(255) NOT NULL,
roi VARCHAR(255) NOT NULL,
parent_asin VARCHAR(255) NOT NULL,
complete TINYINT(1) DEFAULT 0
)


The command runs in PHPMyAdmin when I attempt to execute it, but it does not work when I use PDO. The code that I use looks like this:

$create = $this->dbConnection->prepare("
CREATE TABLE IF NOT EXISTS table_" . $tableId ."
(
id VARCHAR(255),
cost VARCHAR(255),
title VARCHAR(255) NOT NULL,
package_quantity VARCHAR(255) NOT NULL,
package_cost VARCHAR(255) NOT NULL,
asin VARCHAR(15) NOT NULL,
rank VARCHAR(20) NOT NULL,
category VARCHAR(255) NOT NULL,
bb_price VARCHAR(255) NOT NULL,
seller_type VARCHAR(255) NOT NULL,
size_tier VARCHAR(255) NOT NULL,
amazon_fee VARCHAR(255) NOT NULL,
roi VARCHAR(255) NOT NULL,
parent_asin VARCHAR(255) NOT NULL,
complete TINYINT(1) DEFAULT 0
)");
if($create->execute() === true)
{
return true;
}


It always returns true. If someone could help me out, I would really appreciate it!

Answer

Set up your connection first:

$mysql_host = "servername";
$mysql_database = "dbname";
$mysql_user = "username";
$mysql_password = "pass";


try{
$conn = new PDO(
"mysql:dbname=" .   $mysql_database .
";host=" .          $mysql_host     . 
";charset=utf8"         ,
$mysql_user             ,  
$mysql_password         , 
array(                  //
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
    )
);

}catch(PDOException $e){
echo $e->getCode();

}

Then query:

$tableId=$conn->lastInsertId();
$create = $conn->prepare("
    CREATE TABLE IF NOT EXISTS table_" . $tableId ."
    (
        id VARCHAR(255),
        cost VARCHAR(255),
        title VARCHAR(255) NOT NULL,
        package_quantity VARCHAR(255) NOT NULL,
        package_cost VARCHAR(255) NOT NULL,
        asin VARCHAR(15) NOT NULL,
        rank VARCHAR(20) NOT NULL,
        category VARCHAR(255) NOT NULL,
        bb_price VARCHAR(255) NOT NULL,
        seller_type VARCHAR(255) NOT NULL,
        size_tier VARCHAR(255) NOT NULL,
        amazon_fee VARCHAR(255) NOT NULL,
        roi VARCHAR(255) NOT NULL,
        parent_asin VARCHAR(255) NOT NULL,
        complete TINYINT(1) DEFAULT 0
    )");
if($create->execute() === true)
{
return true;
}

This code works. Are you sure your $tableId variable is having expected value? you should var_dump($tableId) to see what's in it!

Comments