Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

create mysql table if it doesn't exist

Tags:

php

mysql

I don't work much with php/mysql, but I need what I thought would be a relatively straightforward task: to check if a table exists and create it if it doesn't. I can't even get a useful error message and there's no table being created in the db. There is obviously something wrong with my syntax.

<?php

    session_start();
    error_reporting(E_ALL);
    ini_set('display_errors', 1);

    // 1. CONNECT TO THE DB SERVER, confirm connection
    mysql_connect("localhost", "root", "") or die(mysql_error());
    echo "<p>Connected to MySQL</p>";
    $mysql_connexn = mysql_connect("localhost", "root", ""); // redundant ?

    // 2. CONNECT TO THE SPECIFIED DB, confirm connection
    $db = "weighttracker";
    mysql_select_db($db) or die(mysql_error());
    echo "<p>Connected to Database '$db'</p>";
    $db_connexn = mysql_select_db($db)or die(mysql_error("can\'t connect to $db"));

    // 3. if table doesn't exist, create it
    $table = "WEIGHIN_DATA";
    $query = "SELECT ID FROM " . $table;
    //$result = mysql_query($mysql_connexn, $query);
    $result = mysql_query($query, $mysql_connexn);

    if(empty($result)) {
        echo "<p>" . $table . " table does not exist</p>";
        $query = "CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
            id INT NOT NULL AUTO_INCREMENT,
            PRIMARY KEY(id),
            DATE    DATE NOT NULL,
            VALUE   SMALLINT(4) UNSIGNED NOT NULL
        )"
    }
    else {
        echo "<p>" . $table . "table exists</p>";
    } // else

?>
like image 914
user1613163 Avatar asked Aug 05 '14 18:08

user1613163


People also ask

What is CREATE TABLE if not exists SQL?

The “if not exists” statement used in the creation of the table, first will analyze the list of all tables present in that schema, then if there is no table with the name of the table, which is going to be created, it will successfully create the table, else it will execute the command successfully without creating the ...

What is CREATE TABLE if not exists?

Every CREATE DATABASE IF NOT EXISTS statement is replicated, whether or not the database already exists on the source. Similarly, every CREATE TABLE IF NOT EXISTS statement without a SELECT is replicated, whether or not the table already exists on the source.

How do you add column if not exists MySQL?

SELECT column_name FROM INFORMATION_SCHEMA . COLUMNS WHERE TABLE_SCHEMA =[Database Name] AND TABLE_NAME =[Table Name]; If the above query returns a result then it means the column exists, otherwise you can go ahead and create the column.

Which clause is used to create a database only if it doesn't already exist?

6. To create a database only if it doesn't already exist, which clause is used? Explanation: The 'CREATE DATABASE' statement supports many optional values. To create a database named 'my_db' only if it doesn't already exist, we write 'CREATE DATABASE IF NOT EXISTS my_db'.


1 Answers

A few things.

There was a missing semi-colon ; in and at the end of )"

if(empty($result)) {
    echo "<p>" . $table . " table does not exist</p>";
    $query = "CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
        id INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(id),
        DATE    DATE NOT NULL,
        VALUE   SMALLINT(4) UNSIGNED NOT NULL
    )" // <--- right there

which would have caused/thrown a parse error, such as:

Parse error: syntax error, unexpected '}' in...

Amongst other errors as shown in my comments from your originally posted code.

Plus, you were not using mysql_query in your table creation.

Here is a mysqli_ method, where I commented out your original codes.

Sidenote: You're using ID for your column in $query = "SELECT ID FROM " . $table; and yet you create your table and column as id in lowercase; both lettercase must match.

<?php

session_start();
error_reporting(E_ALL);
ini_set('display_errors', 1);

$DB_HOST = "xxx"; // put your own data
$DB_NAME = "xxx"; // put your own data
$DB_USER = "xxx"; // put your own data
$DB_PASS = "xxx"; // put your own data


$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($conn->connect_errno > 0) {
  die('Connection failed [' . $conn->connect_error . ']');
}



/*

    // 1. CONNECT TO THE DB SERVER, confirm connection
    mysql_connect("localhost", "root", "") or die(mysql_error());
    echo "<p>Connected to MySQL</p>";
    $mysql_connexn = mysql_connect("localhost", "root", ""); // redundant ?


    // 2. CONNECT TO THE SPECIFIED DB, confirm connection
    $db = "weighttracker";
    mysql_select_db($db) or die(mysql_error());
    echo "<p>Connected to Database '$db'</p>";
    $db_connexn = mysql_select_db($db)or die(mysql_error("can\'t connect to $db"));

    // 3. if table doesn't exist, create it
    $table = "WEIGHIN_DATA";
    $query = "SELECT ID FROM " . $table; // that should be id and not ID
    //$result = mysql_query($mysql_connexn, $query);
    $result = mysql_query($query, $mysql_connexn);


*/


    $table = "WEIGHIN_DATA";
    $query = "SELECT ID FROM " . $table; // that should be id and not ID
    //$result = mysql_query($mysql_connexn, $query); // your original code
    // however connection comes last in mysql method, unlike mysqli
    $result = mysqli_query($conn,$query);


if(empty($result)) {
    echo "<p>" . $table . " table does not exist</p>";
    $query = mysqli_query($conn,"CREATE TABLE IF NOT EXISTS WEIGHIN_DATA (
        id INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(id),
        DATE    DATE NOT NULL,
        VALUE   SMALLINT(4) UNSIGNED NOT NULL
    )");
    }
    else {
        echo "<p>" . $table . "table exists</p>";
    } // else

?>
like image 83
Funk Forty Niner Avatar answered Sep 21 '22 15:09

Funk Forty Niner