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. 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. 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. 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. 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. 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? 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. 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? 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? 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 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 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. 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
Archived
This topic is now archived and is closed to further replies.