testing if a database & table are already created

[Trekker182]

Hello everyone,

 

I'm trying to test and if a database or table has already been created, inform the user.  So far, the only thing that works is if the mySQL error number is 1049 from the response page for unknown database, it will use the include to create a new database and table.  I've tried using an else if the error was 1007 for database already exists, but its not working...all I get is a blank screen and it doesn't tell the user that the database already exists. 

 

$link_id = db_connect($default_dbname);
if (!link_id || $link_id == 0) {
  if($MYSQL_ERRNO == 1049) {
     include "createdbs.inc";
  } else if($MYSQL_ERRNO == 1007)   {
    echo "database already exists<br>";
  } else {
    die(sql_error());
  }
}

 

Same situation inside the include for if a table already exists.

 

  $queryString = "CREATE DATABASE $default_dbname";
  $queryResult = mysqli_query($connection, $queryString);
  if (!$queryResult) {
    die(sql_error());
  } 
  echo "Database created<br>";
  $selectDB = mysqli_select_db($connection, $default_dbname);
  $link_id = db_connect($default_dbname);
  if ($MYSQL_ERRNO == 1050)  {
  echo "Table already exists";
  } else if ($MYSQL_ERRNO == 1051) {
  $queryString = "CREATE TABLE $client_table_name($myField1 VARCHAR(40), $myField2 VARCHAR(40), $myField3 VARCHAR(40), $myField4 VARCHAR(40))";
  $queryResult = mysqli_query($connection, $queryString);
    echo "Table created";}
else if (!$queryResult) {
    die(sql_error()); 
  }

 

Thanks.

 

[Pikachu2000]

Look at the mysql CREATE TABLE and CREATE DATABASE manual pages, and see how IF NOT EXISTS clause is used in them. That should be a good start for you. Link and Another Link

[AbraCadaver]

If you just need to know, then these tests will work (note that permission and other errors may make these fail):

if(mysqli_query($connection, "USE $database")) {
echo "exists";
} else {
echo "does not exist";
}

if(mysqli_query($connection, "SELECT * FROM $table")) {
echo "exists";
} else {
echo "does not exist";
}

 

If you want a list ahead of time, then you can use these:

 

SHOW DATABASES

 

and

SHOW TABLES

 

 

[Trekker182]

Thanks a lot everyone!

 

I'll try these out and let you know!

 

 

[Trekker182]

I've been trying out a variation of your code AbraCadaver and haven't gotten it to work.  It does work on my include page for the tables so I'm thinking its not a permissions issue, plus if it were wouldn't the error show up with the else statement?

 

On first run with no database of the same name created, it works fine and creates it.  But then on second run, same blank page.  Am I missing the logic here?  On the first run, mySQL throws a unknown database error 1049 which triggers the code on the include page to execute and create the database and tables.  On second run, the first if statement evaluates to false because the database now exists so it moves on to the next else if which should evaluate to true since the database now exists and i'm trying to create it with the same database name and yet I just get a blank page.  Is there something wrong with my logic that I'm not getting? 

 

Thanks.

 

$link_id = db_connect($default_dbname);
if (!link_id || $link_id == 0) {
  if($MYSQL_ERRNO == 1049) {
     include "createdbs.inc";}
      else if(mysqli_query($connection, "USE $default_dbname")) {
	 echo "the database already exists";
  } else {
    die(sql_error());
  }
}

[Pikachu2000]

Since your intent is to create a database or a table if it doesn't already exist, the CREATE - IF NOT EXISTS query would seem more appropriate, and easier to implement in this case.

[Trekker182]

Actually I got it working with this code:

 

if(mysqli_query($connection, "USE $default_dbname"))  {
  echo "the database already exists";
   } else if(mysqli_query($connection, "USE $default_dbname"))  {
       include "createdbs.inc";
   } else {
    die(sql_error());
  }

 

Thanks again!  Getting away from using the $MYSQL_ERRNO as a condition helped.

[Trekker182]

CORRECTION:

 

 

 	$link_id = db_connect($default_dbname);
if(mysqli_query($connection, "USE $default_dbname"))  {
        echo "the database already exists";
   } else if ($MYSQL_ERRNO == 1049) {
       include "createdbs.inc";
   } else {
    die(sql_error());
  }