How to create a table in MySQL using PHP?

Question

I want to create a table in a MySQL Database with PHP. This is my try:

$dbhost = 'rdbms.strato.de';
$dbusername = 'Userxxx';
$dbuserpass = 'Passwordxxx';
$dbname = 'DBxxx';

$link_id = mysql_connect ($dbhost, $dbusername, $dbuserpass);
echo "success in database connection.";

if (!mysql_select_db($dbname)) die(mysql_error());
echo "success in database selection.";

$result = "CREATE TABLE address_book (first_name VARCHAR(25), last_name VARCHAR(25), phone_number VARCHAR(15))";

if (mysql_query($result)){
 echo "TABLE created.";
}

else {
 echo "Error in CREATE TABLE.";
}

But this give me the error

success in database connection.Access denied for user XXX to database XXX

I search a lot but find no successfull solution. Have anyone an idea?

Does your MySQL user has write access to this database? If not, look at this link: http://dev.mysql.com/doc/refman/5.7/en/grant.html — FragBis, Sep 02 '16 at 14:22
Seems like it's not letting you select DBxxx. Does your user have the right permissions? — Iain, Sep 02 '16 at 14:22
Your current user `$dbusername` for the connection has not the rights to create a table `address_book` in the seleted DB `$dbname`. — JustOnUnderMillions, Sep 02 '16 at 14:22
You shouldn't use any mysql_*-functions. They have been deprecated since php 5.5 and completely removed in php 7.0 — Manish, Sep 02 '16 at 14:22
Note for some of the above. The error is _before_ trying to create the table. — Jonnix, Sep 02 '16 at 14:23
You don't have needed privileges for user `Userxxx`, check your mysql user settings on strato.de host provider — rjanjic, Sep 02 '16 at 14:23
Every time you use [the `mysql_`](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) database extension in _meow_ code **[a Kitten is strangled somewhere in the world](http://2.bp.blogspot.com/-zCT6jizimfI/UjJ5UTb_BeI/AAAAAAAACgg/AS6XCd6aNdg/s1600/luna_getting_strangled.jpg)** it is deprecated and has been for years and is gone for ever in PHP7. If you are just learning PHP, spend your energies learning the `PDO` or `mysqli` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) — RiggsFolly, Sep 02 '16 at 14:32

score 0 · Answer 1 · edited May 23 '17 at 12:08

Try connecting without involving PHP:

mysql -u Userxxx -h rdbms.strato.de -pPasswordxxx

If this doesn't work, and I suspect it won't, contact your database administrator to reset your password.

If the password isn't the problem, the remote machine may not be ready to accept your connection. See Access remote database from command line for more on this.

If you can get in, then try to access the database you want:

USE DBxxx;
SHOW TABLES;

Thing is, PHP isn't your problem here; it's the connection. So that's what to investigate.

...and when you get that established, you might look into updating your code; mysql_connect and mysql_select_db are deprecated.

score 0 · Answer 2 · answered Sep 03 '16 at 04:40

I need some clarification about your hostname,username,password,database name.How ever following code will works in my local server.If you have any doubt about my code please share your host name,password,dbname,etc.

$dbhost = 'localhost';
$dbusername = 'root';
$dbuserpass = '';
$dbname = 'test';

    $link_id = mysqli_connect ($dbhost, $dbusername, $dbuserpass,$dbname);
echo "success in database connection.";



$result = "CREATE TABLE address_book (first_name VARCHAR(25), last_name VARCHAR(25), phone_number VARCHAR(15))";

if (mysqli_query($link_id,$result)){
 echo "TABLE created.";
}

else {
 echo "Error in CREATE TABLE.";
}

How to create a table in MySQL using PHP?

2 Answers2