prathameshkakade Posted August 28, 2013 Share Posted August 28, 2013 I am trying to create MySQL database using PHP. Here is my code. db-config.php <html> <head> <title> Database Configuration </title> </head> <body> <?php $host = "localhost"; $user = "root"; $pass = "123456789"; $database = "hello"; $mysql = new mysqli($host,$user,$pass); if($mysql->connect_error) { die ("Could not connect to database"); } ?> createdb.php <html> <head> <title> Create tables </title> </head> <body> <?php include("\db-config.php"); if(!$a = ($mysql->query("CREATE DATABASE $database")))die("Could not create database"); echo "Successfully created database"; $table = "user"; $td = "userid MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,"; $td .= "username VARCHAR(20) BINARY NOT NULL,"; $td .= "password VARCHAR(20) BINARY NOT NULL,"; $td .= "name VARCHAR(20) BINARY NOT NULL"; $test = $mysql->query("SELECT DATABASE $database"); if(!(mysqli_select_db($mysql,$database)))die("Could not connect to database"); if(!(mysqli_query("CREATE TABLE $table ($td)")))die("Could not create tables"); echo "Successfully created tables"; ?> </body> </html> It always results in die message. Please help. Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/ Share on other sites More sharing options...
prathameshkakade Posted August 28, 2013 Author Share Posted August 28, 2013 It successfully connects to the mysql server as I don not receive the error from db-config.php file. Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447152 Share on other sites More sharing options...
mac_gyver Posted August 28, 2013 Share Posted August 28, 2013 you need to use the $mysql->error property to find out why the query failed. Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447169 Share on other sites More sharing options...
mac_gyver Posted August 28, 2013 Share Posted August 28, 2013 It successfully connects to the mysql server as I don not receive the error from db-config.php file. are you sure the include("\db-config.php"); statement is even working? that path is referring the root of your disk, which is an unlikely place for a .php file to be at. Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447170 Share on other sites More sharing options...
Barrikor Posted August 28, 2013 Share Posted August 28, 2013 In real life you'll want to be displaying a message on screen, maybe a "Sorry, Try Later" message to the user instead of using die() For testing you'll want to see what the error was, so that you can fix it. Maybe something like this: $resultHandle = $databaseHandle->query("CREATE DATABASE $database") if(resultHandle) { //Stuff to do if the query looks like it worked } else { // Stuff to do if the query failed print 'Error Description: ' . $databaseHandle->error . '----------- Error Number: ' . $databaseHandle->errno; } Also, for your current error, the most common reason for a CREATE DATABASE query to fail is for the database "user" to not have the permission set to allow it to create new databases. Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447174 Share on other sites More sharing options...
prathameshkakade Posted August 28, 2013 Author Share Posted August 28, 2013 In real life you'll want to be displaying a message on screen, maybe a "Sorry, Try Later" message to the user instead of using die() For testing you'll want to see what the error was, so that you can fix it. Maybe something like this: $resultHandle = $databaseHandle->query("CREATE DATABASE $database") if(resultHandle) { //Stuff to do if the query looks like it worked } else { // Stuff to do if the query failed print 'Error Description: ' . $databaseHandle->error . '----------- Error Number: ' . $databaseHandle->errno; } Also, for your current error, the most common reason for a CREATE DATABASE query to fail is for the database "user" to not have the permission set to allow it to create new databases. Mysql user which I am using here is root.So I don't understand why the user does not have permission? Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447177 Share on other sites More sharing options...
prathameshkakade Posted August 28, 2013 Author Share Posted August 28, 2013 are you sure the include("\db-config.php"); statement is even working? that path is referring the root of your disk, which is an unlikely place for a .php file to be at. Yes it is working.I put wrong username and it showed error. Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447188 Share on other sites More sharing options...
prathameshkakade Posted August 31, 2013 Author Share Posted August 31, 2013 I tried the other way to create tables. <html> <head> <title> Database </title> </head> <body> <?php require("./db-config.php"); //$a = mysqli_query($mysql,"CREATE DATABASE $database"); //if(!$a)die(mysqli_error($mysql)); //else echo "Successfully created database"; $table = "user"; $function = "CREATE TABLE $table ( 'userid' MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT, 'username' VARCHAR(20) BINARY NOT NULL, 'password' VARCHAR(20) BINARY NOT NULL, 'name' VARCHAR(20) BINARY NOT NULL) CHARACTER SET utf8 COLLATE utf8_general_ci"; $test = mysqli_query($mysql,"SELECT DATABASE $database"); if(!$test)die(mysqli_error($mysql)); else { $ct = mysqli_query($mysql,$function); if(!$ct)die(mysqli_error($mysql)); else { echo "Successfully created tables $table ($td)<br/>"; } } ?> Now it shows the following error: 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 'hello' at line 1. What is the error here? Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447544 Share on other sites More sharing options...
vinny42 Posted August 31, 2013 Share Posted August 31, 2013 What's the exact query that is being executed? Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447558 Share on other sites More sharing options...
mac_gyver Posted August 31, 2013 Share Posted August 31, 2013 the query that's failing is SELECT DATABASE $database. if you want to select a database using a query statement, it would be USE $database Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447561 Share on other sites More sharing options...
prathameshkakade Posted September 1, 2013 Author Share Posted September 1, 2013 the query that's failing is SELECT DATABASE $database. if you want to select a database using a query statement, it would be USE $database I tried that.Now it is showing the following error. 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 'DATABASE hello' at line 1 Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447639 Share on other sites More sharing options...
mac_gyver Posted September 1, 2013 Share Posted September 1, 2013 all you are doing here is trying things and just dumping your error messages on a forum. why don't you try to troubleshoot what is causing the problem yourself? at least pin down which code or query is failing and post the current code/query so that someone would know what you tried. Quote Link to comment https://forums.phpfreaks.com/topic/281636-unable-to-create-database-and-tables/#findComment-1447656 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.