Php code creating database

Create MySQL user and database from PHP

Is there a way to create a new MySQL database, a new MySQL user and give the new user privileges on the new database all using PHP? EDIT — should be pointed out this is run from 1 server to another, so Server A trying to install a DB/user on Server B i’ve got this:

$con = mysql_connect("REMOTE.IP.ADDRESS","root","pass"); mysql_query("CREATE DATABASE ".$db."",$con)or die(mysql_error()); mysql_query("GRANT ALL ON ".$db.".* to ".$user." identified by '".$dbpass."'",$con) or die(mysql_error()); 

"Access denied for user 'root'@'MY.REMOTE.SERVER.HOST.NAME' to database 'dbname'" 

4 Answers 4

This answer has been edited several times based on new info provided by the OP

Is root actually allowed to connect to the server from the host that you are connecting from? If the error string is returning the canonical name of the server, there’s a very good chance that ‘localhost’ is not pointing to 127.0.0.1 :

«Access denied for user ‘root’@’MY.SERVER.HOST.NAME’ to database ‘dbname'»

That should echo something like «Access denied for user ‘root’@localhost'», not the name of the server.

$con = mysql_connect("127.0.0.1","root","pass"); 

Edit (After more information provided in comments)

If you are connecting from a totally different host, you have to tell MySQL user@remote_hostname_or_ip is allowed to connect, and has appropriate privileges to create a database and users.

You can do this rather easily using phpmyadmin (on the MySQL server), or a query like:

CREATE USER 'root'@'192.168.1.1' IDENTIFIED BY PASSWORD 'secret'; GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.1.1' IDENTIFIED BY PASSWORD 'secret' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; 

I would advise not naming this user ‘root’ , just create a user with all of the global privileges needed. In the example, I used 192.168.1.1, that could easily be a hostname, just make sure DNS is set up appropriately. Specify the host to match exactly as it appears in logs when you connect to the remote server.

You may also want to adjust limits to taste. More information on the CREATE USER syntax can be found here, GRANT here.

If using MySQL 4 — CREATE is not an option. You would just use GRANT (4.1 Docs On User Management)

If using C-Panel, just use the API. While yes, it does have its quirks, its easier to maintain stuff that uses it rather than ad-hoc work arounds. A lot of successful applications use it without issue. Like any other API, you need to stay on top of changes when using it.

Источник

mysql_create_db

Данная функция объявлена устаревшей в PHP 4.3.0, и, вместе с модулем MySQL, удалена PHP в 7.0.0. Вместо неё используйте активно развивающиеся модули MySQLi или PDO_MySQL. Так же смотрите раздел MySQL: выбор API. Альтернативы для этой функции:

Описание

mysql_create_db() пытается создать базу данных на сервере, с которым ассоциирован переданный дескриптор соединения.

Список параметров

Имя создаваемой базы данных.

Соединение MySQL. Если идентификатор соединения не был указан, используется последнее соединение, открытое mysql_connect() . Если такое соединение не было найдено, функция попытается создать таковое, как если бы mysql_connect() была вызвана без параметров. Если соединение не было найдено и не смогло быть создано, генерируется ошибка уровня E_WARNING .

Возвращаемые значения

Возвращает true в случае успешного выполнения или false в случае возникновения ошибки.

Примеры

Пример #1 Пример создания базы данных MySQL

Функция mysql_create_db() не рекомендуется к использованию. Предпочтительнее использовать mysql_query() с SQL-запросом создания базы данных CREATE DATABASE .

$link = mysql_connect ( ‘localhost’ , ‘mysql_user’ , ‘mysql_password’ );
if (! $link ) die( ‘Ошибка соединения: ‘ . mysql_error ());
>

$sql = ‘CREATE DATABASE my_db’ ;
if ( mysql_query ( $sql , $link )) echo «База my_db успешно создана\n» ;
> else echo ‘Ошибка при создании базы данных: ‘ . mysql_error () . «\n» ;
>
?>

Результатом выполнения данного примера будет что-то подобное:

База my_db успешно создана

Примечания

Замечание:

Для обратной совместимости может быть использован следующий устаревший псевдоним: mysql_createdb()

Замечание:

Данная функция не будет доступна, если модуль MySQL был скомпилирован клиентской библиотекой MySQL версии 4.x.

Смотрите также

User Contributed Notes

• MySQL
  • mysql_​affected_​rows
  • mysql_​client_​encoding
  • mysql_​close
  • mysql_​connect
  • mysql_​create_​db
  • mysql_​data_​seek
  • mysql_​db_​name
  • mysql_​db_​query
  • mysql_​drop_​db
  • mysql_​errno
  • mysql_​error
  • mysql_​escape_​string
  • mysql_​fetch_​array
  • mysql_​fetch_​assoc
  • mysql_​fetch_​field
  • mysql_​fetch_​lengths
  • mysql_​fetch_​object
  • mysql_​fetch_​row
  • mysql_​field_​flags
  • mysql_​field_​len
  • mysql_​field_​name
  • mysql_​field_​seek
  • mysql_​field_​table
  • mysql_​field_​type
  • mysql_​free_​result
  • mysql_​get_​client_​info
  • mysql_​get_​host_​info
  • mysql_​get_​proto_​info
  • mysql_​get_​server_​info
  • mysql_​info
  • mysql_​insert_​id
  • mysql_​list_​dbs
  • mysql_​list_​fields
  • mysql_​list_​processes
  • mysql_​list_​tables
  • mysql_​num_​fields
  • mysql_​num_​rows
  • mysql_​pconnect
  • mysql_​ping
  • mysql_​query
  • mysql_​real_​escape_​string
  • mysql_​result
  • mysql_​select_​db
  • mysql_​set_​charset
  • mysql_​stat
  • mysql_​tablename
  • mysql_​thread_​id
  • mysql_​unbuffered_​query

  Источник

  PHP MySQL Create Database

  In this tutorial you will learn how to create a database in MySQL using PHP.

  Creating MySQL Database Using PHP

  Now that you’ve understood how to open a connection to the MySQL database server. In this tutorial you will learn how to execute SQL query to create a database.

  Before saving or accessing the data, we need to create a database first. The CREATE DATABASE statement is used to create a new database in MySQL.

  Let’s make a SQL query using the CREATE DATABASE statement, after that we will execute this SQL query through passing it to the PHP mysqli_query() function to finally create our database. The following example creates a database named demo.

  Example

   // Attempt create database query execution $sql = "CREATE DATABASE demo"; if(mysqli_query($link, $sql)) < echo "Database created successfully"; >else < echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); >// Close connection mysqli_close($link); ?>

  connect_error); > // Attempt create database query execution $sql = "CREATE DATABASE demo"; if($mysqli->query($sql) === true) < echo "Database created successfully"; >else< echo "ERROR: Could not able to execute $sql. " . $mysqli->error; > // Close connection $mysqli->close(); ?>

  setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch(PDOException $e)< die("ERROR: Could not connect. " . $e->getMessage()); > // Attempt create database query execution try< $sql = "CREATE DATABASE demo"; $pdo->exec($sql); echo "Database created successfully"; > catch(PDOException $e)< die("ERROR: Could not able to execute $sql. " . $e->getMessage()); > // Close connection unset($pdo); ?>

  Tip: Setting the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION tells PDO to throw exceptions whenever a database error occurs.

  Источник

  PHP Create a MySQL Database

  You will need special CREATE privileges to create or to delete a MySQL database.

  Create a MySQL Database Using MySQLi and PDO

  The CREATE DATABASE statement is used to create a database in MySQL.

  The following examples create a database named «myDB»:

  Example (MySQLi Object-oriented)

  $servername = «localhost»;
  $username = «username»;
  $password = «password»;

  // Create connection
  $conn = new mysqli($servername, $username, $password);
  // Check connection
  if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
  >

  // Create database
  $sql = «CREATE DATABASE myDB»;
  if ($conn->query($sql) === TRUE) echo «Database created successfully»;
  > else echo «Error creating database: » . $conn->error;
  >

  Note: When you create a new database, you must only specify the first three arguments to the mysqli object (servername, username and password).

  Tip: If you have to use a specific port, add an empty string for the database-name argument, like this: new mysqli(«localhost», «username», «password», «», port)

  Example (MySQLi Procedural)

  $servername = «localhost»;
  $username = «username»;
  $password = «password»;

  // Create connection
  $conn = mysqli_connect($servername, $username, $password);
  // Check connection
  if (!$conn) die(«Connection failed: » . mysqli_connect_error());
  >

  // Create database
  $sql = «CREATE DATABASE myDB»;
  if (mysqli_query($conn, $sql)) echo «Database created successfully»;
  > else echo «Error creating database: » . mysqli_error($conn);
  >

  Note: The following PDO example create a database named «myDBPDO»:

  Example (PDO)

  $servername = «localhost»;
  $username = «username»;
  $password = «password»;

  try $conn = new PDO(«mysql:host=$servername», $username, $password);
  // set the PDO error mode to exception
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = «CREATE DATABASE myDBPDO»;
  // use exec() because no results are returned
  $conn->exec($sql);
  echo «Database created successfully
  «;
  > catch(PDOException $e) echo $sql . «
  » . $e->getMessage();
  >

  Tip: A great benefit of PDO is that it has exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block. In the catch block above we echo the SQL statement and the generated error message.

  Источник

  Create MySQL Database Using PHP

  To create and delete a database you should have admin privilege. Its very easy to create a new MySQL database. PHP uses mysql_query function to create a MySQL database. This function takes two parameters and returns TRUE on success or FALSE on failure.

  Syntax

  bool mysql_query( sql, connection );

  Required — SQL query to create a database

  Optional — if not specified then last opend connection by mysql_connect will be used.

  Example

  Try out following example to create a database −

   echo 'Connected successfully'; $sql = 'CREATE Database test_db'; $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not create database: ' . mysql_error()); >echo "Database test_db created successfully\n"; mysql_close($conn); ?>

  Selecting a Database

  Once you establish a connection with a database server then it is required to select a particular database where your all the tables are associated.

  This is required because there may be multiple databases residing on a single server and you can do work with a single database at a time.

  PHP provides function mysql_select_db to select a database.It returns TRUE on success or FALSE on failure.

  Syntax

  bool mysql_select_db( db_name, connection );

  Required — Database name to be selected

  Optional — if not specified then last opend connection by mysql_connect will be used.

  Example

  Here is the example showing you how to select a database.

   echo 'Connected successfully'; mysql_select_db( 'test_db' ); mysql_close($conn); ?>

  Creating Database Tables

  To create tables in the new database you need to do the same thing as creating the database. First create the SQL query to create the tables then execute the query using mysql_query() function.

  Example

  Try out following example to create a table −

   echo 'Connected successfully'; $sql = 'CREATE TABLE employee( '. 'emp_id INT NOT NULL AUTO_INCREMENT, '. 'emp_name VARCHAR(20) NOT NULL, '. 'emp_address VARCHAR(20) NOT NULL, '. 'emp_salary INT NOT NULL, '. 'join_date timestamp(14) NOT NULL, '. 'primary key ( emp_id ))'; mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not create table: ' . mysql_error()); >echo "Table employee created successfully\n"; mysql_close($conn); ?>

  In case you need to create many tables then its better to create a text file first and put all the SQL commands in that text file and then load that file into $sql variable and excute those commands.

  Consider the following content in sql_query.txt file

  CREATE TABLE employee( emp_id INT NOT NULL AUTO_INCREMENT, emp_name VARCHAR(20) NOT NULL, emp_address VARCHAR(20) NOT NULL, emp_salary INT NOT NULL, join_date timestamp(14) NOT NULL, primary key ( emp_id ));

   $query_file = 'sql_query.txt'; $fp = fopen($query_file, 'r'); $sql = fread($fp, filesize($query_file)); fclose($fp); mysql_select_db('test_db'); $retval = mysql_query( $sql, $conn ); if(! $retval ) < die('Could not create table: ' . mysql_error()); >echo "Table employee created successfully\n"; mysql_close($conn); ?>

  Источник