Creating a check(if statement) to create a database in PHP using PDO

Question

I know how to make a database in PDO , but I am having a problem with making a 'check'. I need to to read if you do not have 'this' database (where database could be mydb), then create it and then echo out "mydb created successfully."

If there is already a database with that name then echo out "mydb found". Like I said I am using PDO here. I will show what I have thus far. I will also include how I am putting in a check to make the tables that I will need also.

Thank you in advance for any help!

<?php
//Database creation
require_once 'core/init.php';

$servername = Config::get('mysql/host');
$username = Config::get('mysql/username');
$password = Config::get('mysql/password');
$dbname = Config::get('mysql/db');
$createtable = false;


try {
    $conn = new PDO("mysql:host=$servername", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "CREATE DATABASE IF NOT EXISTS $dbname";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Database created successfully<br>";
     $createtable = true;
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
$createtable = true;
} else {
  echo Config::get('mysql/db'). '- found!';
}

//Table1 creation
if($createtable){
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

    // sql to create table
    $table = 'surveys';
    $sql = "CREATE TABLE IF NOT EXISTS $table (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) NOT NULL,
    password VARCHAR(64) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    first_name VARCHAR(20) NOT NULL,
    middle_name VARCHAR(20),
    address_1 VARCHAR(32) NOT NULL,
    address_2 VARCHAR(32),
    city VARCHAR(32) NOT NULL,
    state VARCHAR(12) NOT NULL,
    zip INT(12) NOT NULL,
    salt VARCHAR(32) NOT NULL
    )";

    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Table {$table} created successfully";

//Table2 creation
    $table = 'users_session';
    $sql = "CREATE TABLE IF NOT EXISTS $table (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    user_id INT(11) NOT NULL,
    hash VARCHAR(64) NOT NULL,
    )";

    $conn->exec($sql);
    echo "Table {$table} created successfully";
  }

catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
} else {
  echo "Tables are already made and ready to go!";
}
?>

Take a look at this: http://stackoverflow.com/questions/2583707/can-i-create-a-database-using-pdo-in-php — Edward, Apr 25 '16 at 03:15

score 0 · Answer 1 · answered Apr 24 '16 at 23:09

0

You can use SHOW TABLES for this:

$sql = "SHOW TABLES LIKE 'tableName'";
$tableExists = $conn->query($sql)->rowCount() > 0;
if( !$tableExists ){
    //create table
}

answered Apr 24 '16 at 23:09

larsAnders

3,813
1
15
19

Mmm sorry I think what I was trying to get at is that when someone opens up this file on there pc it will do these tasks in order. 1. Make the database if not on the pc, then tell the user it created that db 2. Make the tables for that database if they are not there (which they would not be if first time for the user), then tell user it created them 3. Once that is done it should say db found, table found, every time the user opened this up – tdog Apr 24 '16 at 23:19
Just checking - are you aware that pcs generally don't have databases on them at all? This could only run if the user has installed a web server, PHP, and MySQL and configuration to connect this script to the MySQL server. – larsAnders Apr 25 '16 at 01:26

Creating a check(if statement) to create a database in PHP using PDO

1 Answers1