MySQL Create Database Failing

[Landslyde]

After all the fields have been confirmed on my Registration Form, just before the confirmation email is sent out to the user, I have this code to create a MySQL db from their unique username:
 

        require_once 'login.php';
        echo $uname;
        try{            
           $stmt = $db->prepare("CREATE DATABASE IF NOT EXISTS :dbname");
           $stmt->bindValue(':dbname', $uname, PDO::PARAM_STR);
           $stmt->execute();
        }  
        catch (PDOException $ex){
           echo 'Error creating database: '.$ex;
        }

My apache error log is clear. Same for MySQL eorror log. No errors reported. I have even commented out the bindValue $stmt and put a name in place of :dbname in the prepare $stmt, but I still get nothing. Anyone see what I'm doing wrong?

[CroNiX]

First, why would you create a new db for each user? That's not a normalized database.

 

Second, does your mysql user performing that query have permissions to CREATE?

[Landslyde]

First, why would you create a new db for each user? That's not a normalized database.

 

Second, does your mysql user performing that query have permissions to CREATE?

I guess I don't have to create separate db's for the users. I cld instead create the tables to include their names so the data they access is for them and no other. This is for individuals, not a company. The therapist in Washington state will access his/her own data. The one in Florida theirs. So one db wld work, but the tables wld then have to includ their usernames to make them unique to that user. But either scheme shld work for me.

 

Second: I am the one creating the db upon their registration completion. There the user will be assigned priveleges, none of which are create.

 

Hope this helps you understand better what I'm doing. But since I'm new to MySQL, I realize that I may be way off base with my thinking on this, made obvious by the fact that I can't even get the db created

Edited February 11, 2015 by Landslyde

[CroNiX]

Unless the mysql user has CREATE permission, they can't create databases or tables. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

 

I'd just use a single table for the users. Once they log in with username/pass, you can grab the data associated with that user using their userid. Personally I'd like to only maintain a single table in a database rather than hundreds or thousands of different databases, depending on how many users there ends up being. What if something changes in the future? Updating the schema for a single table is much faster than 1 per user.

[Landslyde]

Unless the mysql user has CREATE permission, they can't create databases or tables. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html

 

I'd just use a single table for the users. Once they log in with username/pass, you can grab the data associated with that user using their userid. Personally I'd like to only maintain a single table in a database rather than hundreds or thousands of different databases, depending on how many users there ends up being. What if something changes in the future? Updating the schema for a single table is much faster than 1 per user.

Thanks again, CroNiX. The user isn't creating the db. I am (or am trying to).

 

I didn't think to far along on putting all the therapist's client info in one table. I guess that wld work, but it wld sure be a big one. Anyways, please understand that I am the one creating the db and table(s). Not the customer. And I still don't know why my code from my original post up there isn't working. Any help there?

[CroNiX]

Databases are designed to handle millions of records, so storing tons of data shouldn't be a problem. The key to a high performing database is the indexes you place on the various fields.

 

You only showed this db code:

$stmt = $db->prepare("CREATE DATABASE IF NOT EXISTS :dbname");
$stmt->bindValue(':dbname', $uname, PDO::PARAM_STR);
$stmt->execute();

But, you do have a connection somewhere, using a mysql user and password somewhere before you try to do that, right? It's THAT user who needs the CREATE permission.

[Landslyde]

The top line in my original post is for my login credentials.

             require_once 'login.php'; // My login credentials
	     try{			
	        $stmt = $db->prepare("CREATE DATABASE IF NOT EXISTS :dbname");
   		$stmt->bindValue(':dbname', $uname, PDO::PARAM_STR);
   		$stmt->execute();
	     }  
	     catch (PDOException $ex){
   		echo 'Error creating database: '.$ex;
	     }

Edited February 12, 2015 by Landslyde

[kicken]

It's possible (and generally preferable) to create a database user account which can only work with a specific database, and not create new databases. This is what we are saying you need to check for. Whatever username you specify when connecting to mysql will need to have the proper permissions to create a new database.

 

As mentioned though, you really should re-consider your layout and keep things in a single database and link things together in the tables by ID rather than trying to create a separate database (or table) for each end-user.

[mac_gyver]

the database name isn't a piece of data and cannot be a place-holder/bound data in a prepared query.

 

have you set the pdo error mode to exceptions in your database connection code so that the try/catch block would have any effect?

[Landslyde]

the database name isn't a piece of data and cannot be a place-holder/bound data in a prepared query.

 

have you set the pdo error mode to exceptions in your database connection code so that the try/catch block would have any effect?

Yes, I use PDO error catching. Kicken is the one who turned me on to how to use PDO's one day. Many thanks for that.

 

See, I use login.php, which has all my login credentials in it:

<?php
	$db = new PDO('mysql:host=localhost;dbname=login', 'root', 'myPW');
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
?>

So when I try to create the database from the users username, it shld work. But it doesn't. I may be going about this the wrong way guys. I know that. Maybe all I need is one db and a giant table to handle all the data. And I know I cld keep everything straight between the users by their custID. I'm really new to all this and wasn't thinking that way, but I can see where it wld be a whole lot easier. I think the one thing I don't like is having all the data in a single table. For me, it wld be easier to break things down and make them manageable in multiple tables. However, I think one table with one db is the way to go.

 

I came here for help on one issue and ended up seeing everything in a whole new light. You guys are really good. I listen to you. I might tend to butt heads sometimes, but that's usually how I get convinced, one way or the other. I know most of you do this for a living. Me? I'm just an old man trying to jump in the middle of waters I know nothing of. So thanks to each of you for your input.

 

But before we close this post, can someone please tell me why my code fails to create a db via the users username? While I see that's a bad idea now, I still wld like to know what I'm doing wrong there.

 

Thanks

[CroNiX]

 

But before we close this post, can someone please tell me why my code fails to create a db via the users username? While I see that's a bad idea now, I still wld like to know what I'm doing wrong there.

 

Thanks

I believe mac_gyver answered that

 

 

 

the database name isn't a piece of data and cannot be a place-holder/bound data in a prepared query.

[mac_gyver]

your posted code throws the following exception for me -

 

Error creating database: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1' in C:\Apache24\htdocs\d.php:25 Stack trace: #0 C:\Apache24\htdocs\d.php(25): PDO->prepare('CREATE DATABASE...') #1 {main}

 

 

therefore, either that code isn't being ran at all or you are doing something like a header() redirect later on the page, with output_buffering turned on, and you are not seeing the output from the catch block.

[Landslyde]

I am doing a header() redirect. After the code I posted here, it drops pn down and sends a confirmation email to the user, the it redirects to a thank-you page.

 

Thanks again for you help.

Edited February 12, 2015 by Landslyde

[mac_gyver]

two suggestions then -

 

1) turn off output buffering (it's probably set in your php.ini). you should only buffer output when you want to, at all other times, it just creates problems by hiding what's actually being output by your code.

 

2) the query failing due to an error is a fatal application problem. code that's dependent on the query working, shouldn't be ran. your code should take an appropriate action when there is a query error, such as not running any follow-on code.

 

you would actually want to output a user message - 'sorry this page cannot complete the requested action' and display (when debugging) and log (on a live server) all the information you have about the problem. if you use php's trigger_error() statement for handling the actual error information, it follows the php error_reporting/display_errors/log_errors settings, so you can set if errors are displayed or logged at run-time by setting the php settings.

[mac_gyver]

another reason why you wouldn't dynamically create database tables based on usernames, or any other user entered data, on a case-sensitive operating system, the table names are also case-sensitive. unless you have taken steps to normalize the usernames, you could end up with multiple tables - Macgyver, macgyver, MACGYVER, and any other letter-case variations, that would all be different tables for the same entered username.