Return value for CREATE

I’m doing the following:

$create_q = "CREATE TABLE IF NOT EXISTS baz (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, age TINYINT NOT NULL) ENGINE = MyISAM CHARACTER SET utf8;";

$q = mysqli_query($this->link, $create_q);

// I want this to run only if the table above was NOT created (i.e. it existed already)
mysqli_query($this->link, "ALTER TABLE baz ADD age TINYINT NOT NULL AFTER name");

Before the second query, I want to check whether the first query actually resulted in table creation, or if it was bypassed because the table already existed.

I would have thought the $q variable would contain some sort of indication that the query created a table, but it doesn’t seem to. I’ve read that I could run an extra SHOW CREATE TABLE query, but I’d rather just run the ALTER even if the table was just made.

Basically, I want to add a new column to a table if it exists already, but if the table doesn’t exist, create the table including the new column. If I’m going about this a very wrong way, please tell me, as I’m enjoying learning this stuff.

Unfortunately it returns int(0) whether the table was created or not. The thing is I might need to alter the table while it’s still empty or not.

Just a thought, can you use LAST_INSERT_ID(). I’m thinking that if you create a table you will put some data in it. That way if LAST_INSERT_ID() is greater than 1 it’s a newly created table.

Try eliminating the IF NOT EXISTS

Thanks Guido, but the errno is 0 regardless of whether the table was actually created or not. If the table was already there, the errno is 0 as well. I want to be able to differentiate the two.

Check the error number. If 0, the create was succesful.
http://www.php.net/manual/en/mysqli.errno.php

Of course, the ALTER TABLE would also result in an error, if the column already exists.

Because this script is included on several pages. For every page it is included on, a table is created. It could end up being included on hundreds of pages. And it’s going to be used by different people, not just me.

Why don’t you know if the table already exists?

Yeah, that’s true. Perhaps an error is not that bad at the end of the day.

I was trying it using CREATE TABLE IF NOT EXISTS. Using IF NOT EXISTS var_dumping the result of the query gives true whether the table exists or not.

I’m just going for using CREATE without the check to see if it exists. It’ll spit an error out if the table is there already, and I can use that to decide whether to run the ALTER or not.

That’s weird. If I running the following code it returns true the first time and false the second and consecutive times (meaning the table already exists):


$link=mysqli_connect('blah','bleh','bloh');
$x=mysqli_query($link, 'CREATE TABLE cat4 (name varchar(255))');

var_dump($x);

(PHP 5.2.8 running as a module in Apache 2.2.11 – WampServer2 @ Win7)

Thanks Guido, that works. I feel a bit uncomfortable about an error being generated, like this is a hacky way of going about things. Is there no better way?