mysql table creation with PDO fails

Question

I'm rewriting my php code for mysql database access (now using a class definitions). While I had the old code (using old style functions) working I struggle to get the code for creating tables right - I think the problem relates in particular to the right definition of the sql statement, pls see code below. The new aspect (for me) compared to the old code is that the sql table creation command now must include the name of a database different than the master_database for which the PDO connection is set up (within the class constructor).

I looked at http://dev.mysql.com/doc/refman/5.6/en/create-table.html (I use mysql v5.6) - the part starting with "The table name can be specified as db_name.tbl_name to ..." but I cant get the sql syntax right. If I'm right the sql syntax is wrong what should be the right syntax in the code below for $sql (using the construction with various variables)?

Thnx for your help in advance.

Simplified code:

<?php
define('DB_HOST', '***');   
define('DB_NAME', 'MASTER_DATABASE'); //different name
define('DB_USER', 'root');
define('DB_PASS', '*****');

//class definitions
class Database {

private $_db = null; //databasehandler

public function __construct() {

    try {
        $this->_db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASS, array( 
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
        ));
        $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->_db->query('SET CHARACTER SET utf8');
    } catch (PDOException $e) {
        exit('Error while connecting to database.'.$e->getMessage());
    }
}

private function printErrorMessage($message) {
    echo $message;
}


public function createDBTable($dbname,$dbtable,$tablestructure) {
    try {
        $sql = "CREATE TABLE `".$dbname."'.'".$dbtable.$tablestructure; //NOT CORRECT YET
        $success = $this->_db->exec($sql);
        return ($success > 0) ? true:false;
    }
    catch(PDOException $e){          
        $this->printErrorMessage($e->getMessage());
    }
}
}//class


//code using class defs
$dbname = 'Mydatabase'; //just an existing database
$dbtable = 'Persons';
$tablestructure = '(FirstName CHAR(30),LastName CHAR(30),Age INT)';

$mydb = new Database();

if($mydb->createDBTable($dbname,$dbtable,$tablestructure)){
echo 'table creation  success';
}
else{
echo 'table creation failure';
}
?>

You must open _and_ close each dotted component with backticks. You have opened with a backtick, but closed with a single quote. — Michael Berkowski, Sep 23 '13 at 13:49
What's the point in having `createDBTable()` function? to save yourself *two words* when calling it? You just moved them into function name, mind you. I'll never in my life understand the reasons for having this kind of functions, which actual purpose s just to *concatenate arguments* — Your Common Sense, Sep 23 '13 at 13:50
`($success > 0) ? true:false;` statements also above my understanding. — Your Common Sense, Sep 23 '13 at 13:51
`$this->printErrorMessage($e->getMessage()); ` - another pointless and harmful statement — Your Common Sense, Sep 23 '13 at 13:53
Speaking of the question itself, PDO has nothing to do with SQL syntax. If you cant get the sql syntax right, **why not to ask on the very SQL syntax then?** What's the point in all this PDO stuff? — Your Common Sense, Sep 23 '13 at 13:56

score 0 · Accepted Answer · answered Sep 23 '13 at 14:31

0

You can replace you create table line as:- $sql = "CREATE TABLE ".$dbname.".".$dbtable."".$tablestructure; //NOT CORRECT YET

Also instead of:- $success = $this->_db->exec($sql);

Use the below lines:- $stmt = $this->_db->prepare($sql); $success = $stmt->execute();

Without the above 2 lines, the $succes variable will have value of 0 and although the table can be created, it would echo table creation failed. Hope this helps.

answered Sep 23 '13 at 14:31

theark

339
1
3
11

thnx for all comments; @theark: your solution works and I used it (dont understand why it was downvoted...) – Joppo Sep 23 '13 at 14:58
Glad that it worked. This has been downvoted as 'Your Common Sense' was trying to explain that this is actually a problem with the SQL syntax, its nothing to do with PDO. – theark Sep 23 '13 at 18:19

mysql table creation with PDO fails

1 Answers1