Using PDO to create a table

I'm trying to create a table using PDO, but it's not working:

$db = DB::getPDOConnection();

$sql1 = " DROP TABLE IF EXISTS ?; ";
$sql2 = " CREATE TABLE ? ( " .
           " id INT(11) NOT NULL AUTO_INCREMENT, " .
           " firstname VARCHAR(30) NOT NULL, " .
           " PRIMARY KEY (id)) ";
$stmt1 = $db->prepare($sql1);
$stmt1->bindValue(1, 'testTable', PDO::PARAM_STR);
$stmt2 = $db->prepare($sql2);
$stmt2->bindValue(1, 'testTable', PDO::PARAM_STR);

$result1 = $stmt1->execute();
$result2 = $stmt2->execute();

echo '$result1=' . $result1 . '<br/>'; // results in false
echo '$result2=' . $result2 . '<br/>'; // results in false

Open in new window


No table gets created. Can someone please tell me what I'm doing wrong? Thanks.
elepilAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
This article shows how to create a table.  Read it from top to bottom, and give special attention to the part captioned "Run the CREATE TABLE Query and Handle Errors/Exceptions"  The reason for the top-to-bottom reading is that the error visualization is embedded in several places (sorry, that's just the way PDO works) and you want to trap and display all of the errors when you're debugging a script.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
elepilAuthor Commented:
Ray, you really want me to read through that lengthy article? I'm just asking what I'm doing wrong. Can't you give me a concise answer? I don't care about Errors/Exceptions in this case. I just want to know why my SQL is not creating the table.
0
F PCommented:
$db = DB::getPDOConnection();


... can you

var_dump($db);
exit;

and post the output so we can see if you're even working with a valid connection object please?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

elepilAuthor Commented:
It just says this:

object(PDO)[1]

Open in new window

0
Ray PaseurCommented:
Yes, @elepil, I want you to read the article. especially this part, that I tried to highlight in the earlier post.
...the part captioned "Run the CREATE TABLE Query and Handle Errors/Exceptions"
I can't guess at what is wrong, and neither can var_dump($db); because there are many things that might be wrong and they will never be apparent unless you follow the known good procedures to isolate and visualize the issues.  The article teaches how to do that.  It's not there to waste your time; it's there to show you exactly how to make things work.  All the code samples are tested.  It uses PDO and it creates tables successfully.  It shows how to find errors when they (inevitably) occur.  Most database errors are silent in PHP.  That's unfortunate, but it's the way PHP works, so we need to have some tools to inspect the database response and find out what happened.  It's all in the Article with examples you can cut-and-paste.  It will be helpful to you, I promise.
0
elepilAuthor Commented:
I was hoping someone would notice that if I were to remove the anonymous parameters out of my SQL and hardcoded the table name, it would work. My real question here is why is it failing when anonymous parameters are employed.

To give me a link to a tutorial that teaches me how to create tables is, to me, a bit unproductive. My post is not so much a how-to, but more of a why-the-heck-does-it-not work when I think I'm doing it correctly. I've been a long-time patron of EE, and I've had plenty of experience being helped by people in various ways. You yourself, Ray, have helped me out effectively in the past, but it's when you give a precise direct-to-the-point answer to my problem that I find most beneficial. I've learned from personal experience that links provided to me that discuss a topic in a much broader way than what I need ends up wasting my time.

If this is the best you can do, thanks anyway for your efforts.
0
Ray PaseurCommented:
Well, this is just a guess and I don't have time to research it, but I don't think you can bind the table name, and I've never seen a binding to a string literal -- it's always been a PHP variable in my experience.  You can wrap the code in try / catch blocks to see the exception, something like this:
$tbl  = 'testTable';
$sql1 = " DROP TABLE IF EXISTS ?; ";
$sql2 = " CREATE TABLE ? ( " .
           " id INT(11) NOT NULL AUTO_INCREMENT, " .
           " firstname VARCHAR(30) NOT NULL, " .
           " PRIMARY KEY (id)) ";

try {
$stmt1 = $pdo->prepare($sql1);
var_dump($stmt1);
$stmt1->bindValue(1, $tbl, PDO::PARAM_STR);
var_dump($stmt1);
$result1 = $stmt1->execute();
var_dump($result1);
} catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_WARNING);
}

Open in new window

I can also tell you that it will work correctly if you do it like this, and in this case the bindValue() method appears to be superfluous:
$tbl  = 'testTable';
try {
    $sql3 = " DROP TABLE IF EXISTS $tbl ";
    $stmt3 = $pdo->prepare($sql3);
	var_dump($stmt3);
	$stmt1->bindValue(1, $tbl, PDO::PARAM_STR);
	var_dump($stmt3);
	$result3 = $stmt3->execute();
	var_dump($result3);
} catch(PDOException $exc)
{
    var_dump($exc);
    trigger_error($exc->getMessage(), E_USER_WARNING);
}

Open in new window

But as to why PDO behaves this way, I have no idea.  I didn't write PDO.

HTH, ~Ray
0
elepilAuthor Commented:
Ray, even your sample of putting the table name in a variable still doesn't work. I've actually tried that already before I tried passing literals to the bindValue().

I also had googled this prior to posting, and I was getting mixed comments. All were saying it is possible to bind the table name, but it had to be done in a certain way, like using a prepared statement (which I am), and some were talking about "immediate modes" but that was in Oracle. That's why I'm posting this to hoping to get an unequivocal answer.
0
Ray PaseurCommented:
The example works in the code snippet here.  It was posted above in the $sql3 example, just above.
http://www.experts-exchange.com/viewCodeSnippet.jsp?refID=40862051&rtid=20&icsi=2

Is there a particular reason you want to bind the table name into DROP and CREATE statements?  The usual purpose of binding variables is to let you use external data with some comfort that you will not get SQL injection.

PDO holds out the promise that you could, in theory, swap out the underlying data base engine and not have to change your PHP scripts.  I don't know any experienced software developer who believes that, but some web sites seem to suggest the possibility.  So there may be greater expectations of PDO, beyond what it can really do for you.

If you can post a link to any of your search results that says you can bind a table name, I'll be glad to explore what they're saying.
0
elepilAuthor Commented:
Is there a particular reason you want to bind the table name into DROP and CREATE statements?

SQL is tedious to me and I like streamlining such operations. I like to make a reusable function to create a table which automatically handles the checking of whether the table already exists or not, among others. This is just a precursory thought, but I guess I can get creative with this if I really gave it more thought (e.g. making this function versatile so as to work with different types of databases, shielding me having to always keep track of syntactical differences).

A good example is a DB class I made that handles creation of both a PDO and mysqli database connection for multiple servers, e.g. my local and my two remote sites. The class can detect where it's at, then apply the appropriate login and password information. This class is basically a singleton class, but when passed true as an argument, it can create new connections and creates connections for both PDO and mysqli should I need more than one instance. In actual usage, all that rigmarole can be avoided with the resultant simplicity:

$db = DB::getPDOConnection();
or
$db = DB::getMYSQLIConnection();

Open in new window


And I would never have to worry if my code is running locally, or in one of the other two remote servers, because the code to get a database connection will always be a simple one-liner.
0
elepilAuthor Commented:
Oh Ray, about your code snippet, you're no longer using anonymous parameters. You're just using string handling. I don't know why you even bothered to bindValue().

I'm not looking for workarounds. I'm look for an explanation why my example wouldn't work, and why I'm not getting an error either.
0
F PCommented:
http://php.net/manual/en/pdostatement.closecursor.php

Closure, don't we all need some? Honestly I've seen weird things like this happen before when you stack statements instead of going one at a time without closing the cursor first. I wouldn't be surprised, and please test, if this worked for you:

$db = DB::getPDOConnection();

$testtable = 'testTable';
$stmt1      = $db->prepare("DROP TABLE IF EXISTS ?; ");

$stmt1->execute(array($testtable));

// ... now the next operation

$stmt2 = $db->prepare(" CREATE TABLE ? (  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL ) ;");

$stmt2->execute(array($testtable));

// It might return affected rows for each, but never results.

Open in new window





now if that didn't work, I would write it one of two ways....

// leave the table alone and delete from where 1 before if required, but then do this:
$db = DB::getPDOConnection();

$testtable = 'testTable';
$stmt1      = $db->prepare("CREATE TABLE IF NOT EXISTS ? (  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL ) ; "); 

$stmt1->execute(array($testtable));

Open in new window


or

<?php
$db = DB::getPDOConnection();

$testtable = 'testTable';

/* Create a PDOStatement object */
$stmt1 = $db->prepare("DROP TABLE IF EXISTS ?; ");

/* Create a second PDOStatement object */
$stmt2 = $db->prepare("CREATE TABLE IF NOT EXISTS ? (  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL ) ; ");

/* Execute the first statement */
$stmt->execute(array($testtable));

/* Fetch only the first row from the results or do whatever */
$stmt->fetch();

/* The following call to closeCursor() may be required by some drivers */
$stmt->closeCursor();

/* Now we can execute the second statement */
$stmt2->execute(array($testtable));

Open in new window

0
Ray PaseurCommented:
why my example wouldn't work, and why I'm not getting an error either
Why is there nothing in a hole?  Why did Greece default on its debt?  Why does PHP use silent failures instead of warnings when MySQL fails?  Why is FALSE equal to zero?  

You're not getting an error because PHP does not raise errors in anything like a consistent and well-designed way.  The same can be said for CSS and HTML.  You wrote bad markup but the browser didn't tell you?  Tough luck - you get silent failures and spooky action at a distance.

Some things do not make sense, do not come with explanations; they just "are" and that is the nature of PDO.  FWIW, PDO makes more sense in many ways than a lot of things in PHP.  Why are some of the function argument lists "needle, haystack" and some "haystack, needle?"  Why is there only class inheritance and not object inheritance?   Why is FALSE equal to zero?  

For a better list of PHP oddities than anything I can make up, have a look at this:
http://eev.ee/blog/2012/04/09/php-a-fractal-of-bad-design/

And here is another somewhat humorous take on it:
http://blog.codinghorror.com/the-php-singularity/

The truth is, PHP is a language that grew by topsy from a set of poor assumptions.  As Ovid said, "adde parvum parvo magnus acervus erit" and that is the story of PHP.  There are current efforts in our community to correct some of this.  The PHP-FIG projects are helping.  The PSRs are helping.  Open source projects, through GitHub, are helping.  The elegant simplicity of the Eloquent ORM, used by the Laravel framework, does all of the things you're trying to do with your DB abstraction layer, and it might be easier to go that way than roll your own.  It is an ActiveRecord design pattern, and therefore probably unsuitable for operating at large scale, but if you're not getting more than a few thousand hits per hour, you'll be fine using Eloquent.
http://laravel.com/docs/5.0/eloquent

Sad truth is, there is no answer to "why" in your question.  There are only ways that work and ways that don't work.  It took me a long time to find and test the ways that work, and I didn't want others to have to do the same research that I had to do.  That's why I wrote the article.

Best of luck with the project, over and out, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
elepilAuthor Commented:
I think I've given this enough time. I guess it all boils down to what Ray said, "Sad truth is, there is no answer to "why" in your question.  There are only ways that work and ways that don't work."

One notch down on my respect for PHP. Thanks for both your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.