53

I want to create a class which uses PDO to interact with MySQL. Can I create a new MySQL table using PDO?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Xerri
  • 4,916
  • 6
  • 45
  • 54

2 Answers2

118

Yes, you can.

The dsn part, which is the first parameter of the PDO constructor, does not have to have a database name. You can simply use mysql:host=localhost. Then, given you have the right privilege, you can use regular SQL commands to create a database and users, etc.

Following is an example from an install.php file. It logs in with root, create a database, a user, and grant the user all privilege to the new created database:

<?php

    $host = "localhost";

    $root = "root";
    $root_password = "rootpass";

    $user = 'newuser';
    $pass = 'newpass';
    $db = "newdb";

    try {
        $dbh = new PDO("mysql:host=$host", $root, $root_password);

        $dbh->exec("CREATE DATABASE `$db`;
                CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass';
                GRANT ALL ON `$db`.* TO '$user'@'localhost';
                FLUSH PRIVILEGES;")
        or die(print_r($dbh->errorInfo(), true));

    }
    catch (PDOException $e) {
        die("DB ERROR: " . $e->getMessage());
    }
?>
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Jingshao Chen
  • 3,405
  • 2
  • 26
  • 34
  • 3
    @thearchitect Such a good answer and is not accepted? I feel discouraged :( – Jingshao Chen Jul 18 '11 at 18:09
  • Just note that on cPanel hosting plans, you won't be able to see your database except by using phpmyadmin or command line -- it won't show up in cPanel under "MySQL Databases" control panel. – Volomike Jun 15 '12 at 10:49
  • 8
    @JingshaoChen thanks for the helpful answer! To everyone else, I spent several hours debugging a "create database" query, only to realize that in the "Create Database `$db`" string, those are "backticks" NOT single quotes!!! You must bracket the name of the new database in BACKTICKS!! Hopefully this will save someone the annoyance of having to figure out the same thing. – hypervisor666 Aug 12 '12 at 16:57
  • @Volomike im confused - can i use this if my site has Cpanel? i've tried but it doesnt seem to work – Lan Jan 15 '14 at 17:15
  • Can we do something similar with Postgres? – SandeliusME Mar 05 '14 at 14:26
  • 1
    How do you open the new database after you've created it? A new PDO object? – Nick Mitchell May 22 '14 at 20:18
  • What is the root user and password here ? its not mysql user id and password right ? – logan Jan 31 '15 at 13:59
  • @logan It is the user that has privilege to create databse, user, and grant privileges to a user. This can be a root. – Jingshao Chen Feb 15 '15 at 20:53
  • I'm not sure if your solution works because it seems to me that CREATE DATABASE requires a db name without quotes. Anyway be careful with sql injection! Imagine that you have the following string in $db: `$db = "newdb; DROP DATABASE olddb;";` (Posted on behalf of [meons](http://stackoverflow.com/users/4944587/meons), who has insufficient rep to comment) – Philip C Jan 07 '16 at 11:32
  • @PhilipC the name of the database is in back ticks, not quotes. – Jingshao Chen Jan 17 '16 at 03:08
-1

Yes, it's the same, like running a regular query like "CREATE TABLE ...".

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
  • 2
    When creating a PDO connection, you need to associate that connection with a database right? I was wondering if that can be avoided or if it would be dangerous to use the default mysql database or else if it would be best to just create an empty database where PDO can connect to. – Xerri Apr 06 '10 at 09:37
  • 1
    @Haim : What is the root user and password here ? its not mysql user id and password right ? – logan Jan 31 '15 at 13:57