Perl - If table does not exist

Question

I'm currently using the following which works but throws an error every time the table is created (as it is first trying to insert into a db which does not exist and returning false).

$result = $dbh->prepare("INSERT INTO `". $host ."` (URL) VALUES ('$href')");
if( ! $result->execute() ){
    $result = $dbh->prepare("CREATE TABLE `" . $host . "` ( `ID` INT( 255 ) NOT NULL AUTO_INCREMENT , `URL` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `ID` )) ENGINE = MYISAM ;");
    $result->execute();
    print "Host added: " . $host . "\n";
}

Could someone let me know of a more efficient way of completing this task?

Regards,

Phil

EDIT - Bind

$result = $dbh->prepare("CREATE TABLE `?` If NOT EXISTS ( `ID` INT( 255 ) NOT NULL AUTO_INCREMENT , `URL` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `ID` )) ENGINE = MYISAM ;");
            $result->execute($host);
            $result = $dbh->prepare("INSERT INTO `?` (URL) VALUES ('?')");
            $result->execute($host, $href);
            print "Host added: " . $host . "\n";

is this correct?

Answer 1

CREATE TABLE Foo if NOT EXISTS  <schema>

Also, please use placeholders and bind values in your INSERT operations, lest you be known as Little Bobby Tables.

Answer 2

Try:

CREATE TABLE tblname IF NOT EXISTS ...

Answer 3

Is this a web app? If so, I highly recommend against using a user/pass that has rights to alter the database structure. A more typical method would be to have an installer script that creates the tables up front.

Answer 4

If you expect the table to exist more often than not, the existing code is the most efficient way. Note that you can override PrintError and RaiseError (assuming one of those is what you mean by "throws an error") for a given statement handle.