If Tables In Database

Question

I am creating a PHP application where they input their database information and my program will connect to the database and insert tables. I want to first ensure there are no current tables in the database and if there are I would like to throw them an error.

I have already tried "SELECT COUNT(*)" but when I try to run that it says:

"Object of class mysqli_result could not be converted to int".

      $check = 'SELECT COUNT(*)';
      $val = $connection->query($check);

      if ($val > 0) {
        header("Location: ../../db-setup.php?notclean");
      }

      else {
        $sql = 'CREATE TABLE `test` (`name` VARCHAR(255) NOT NULL);';
        $connection->query($sql) or die();
        header('Location: ../../db-setup.php?success');
      }

I tried this configuration and even if there is no tables in the database it still throws me my notclean created error.

So I tried...

      $check = 'SELECT COUNT(*)';
      $val = $connection->query($check);

      if ($val > 1) {
        header("Location: ../../db-setup.php?notclean");
      }

      else {
        $sql = 'CREATE TABLE `test` (`name` VARCHAR(255) NOT NULL);';
        $connection->query($sql) or die();
        header('Location: ../../db-setup.php?success');
      }

After trying this, if there is nothing in the database it will create the tables, as I wanted. But, if there is something in the database it throws a weird error:

"Notice: Object of class mysqli_result could not be converted to int".

weird ! why you don't specify a table for the `SELECT` statement ? You'll get `1` as the count (even if you don't select a database !). — ThS, Aug 23 '19 at 21:32

PaulProgrammer · Answer 1 · 2019-08-23T21:31:47.870

1

A random call to SELECT 'foo' with no table specified is not an error, and will return something reasonable.

The general pattern is to use CREATE TABLE IF NOT EXISTS ... and use that unconditionally.

CREATE TABLE IF NOT EXISTS `test` (
  `name`  varchar(255) not null,
);

Most SQL dialects support this. Since you don't list your SQL server I can't comment for sure. I guess mysql because you mention 'mysqli_result', and mysql does in fact support IF NOT EXISTS.

If you really want to avoid IF NOT EXISTS, you need to do a SQL-dialect specific query -- DESCRIBE DATABASE or SHOW TABLES or SELECT table_names FROM all_tables or whatever.

edited Aug 23 '19 at 21:31

answered Aug 23 '19 at 21:26

PaulProgrammer

16,175
4
39
56

Thanks so much. How would I go about retrieving an error if there are other tables, so I can print that to the screen? – Gabe Oliver Aug 23 '19 at 21:32
Depends on the database engine in question. For mysql, see [this answer](https://stackoverflow.com/questions/8334493/get-table-names-using-select-statement-in-mysql). Once you query the names, you can decide if you should throw an error. – PaulProgrammer Aug 23 '19 at 21:34

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

Problem

First of all, let me show you why you get the error :

the query method returns a mysqli_result object that holds the results (rows maybe) from the query.
when you use $val > 1, PHP tries to cast the $val variable (which is a mysqli_result object) to an integer (that can't happen) thus you get the error.

I saw a weird query used in your code, SELECT COUNT(*) will always return 1 ! You need to specify a FROM clause here.

...I want to first ensure there are no current tables in the database... To do so, you could use a query like SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'DB_NAME' where DB_NAME is the database you're using to stores/create the tables.

Solution

So, to solve the issue you need to fetch (and assign the result to a variable) the returned mysqli_result object ($val variable) using a method like fetch_assoc (return an associative array) and then use the variable in which you stored the result in (which is an associative array) to get the count you need. You'll use something like :

$check = "SELECT COUNT(*) AS `c` FROM `information_schema`.`tables` WHERE `table_schema` = 'DB_NAME';"; /** COUNT(*) is aliased as c to simply get it using $result['c'] later **/
$val = $connection->query($check);
$result = $val->fetch_assoc();
if($result['c'] > 0) { /** or just if($val->fetch_assoc()['c'] > 0) after the ->query call as you don't need to hold the result from the query (you don't/won't it need/use it later) **/
  header("Location: ../../db-setup.php?notclean");
} else {
  $sql = 'CREATE TABLE `test` (`name` VARCHAR(255) NOT NULL);';
  $connection->query($sql) or die();
  header('Location: ../../db-setup.php?success');
}

Thanks so much! I really appreciate it. – Gabe Oliver Aug 24 '19 at 16:44 — Gabe Oliver, Aug 24 '19 at 16:44

If Tables In Database

2 Answers2

Problem

Solution