I have a php file which connects to mysql database.
I need to create a condition, which if the database is not found, it will create it.
I researched and I found how to create a database with PHP. However, I need to create one with the following criteria:
DB Name, DB User, Password and all privileges granted
Is there a way how I can implement this?
You can do like this
$connection=mysql_connect('server','username',password);
$db_result=mysql_select_db('database');
if(!$db_result){
mysql_query("CREATE USER 'stackoverflow'@'localhost' IDENTIFIED BY 'mypass';",$connection); //user
mysql_query("GRANT ALL ON db1.* TO 'stackoverflow'@'localhost');"
mysql_query("CREATE DATABASE my_db",$connection); //create database
}else{
//Database is available
}
This'll be a bit of a chicken and egg problem. To connect to MySQL via PHP, you need a valid username/password. But your program may possibly connecting to a database which does not have that user/password configured, so how is it going to be able to connect to create the user/password/database in the first place?
$connection=mysql_connect('server','username',password);
$db_result=mysql_select_db('database');
if(!$db_result){
mysql_query("CREATE USER 'stackoverflow'@'localhost' IDENTIFIED BY 'mypass';",$connection); //user
mysql_query("GRANT ALL ON db1.* TO 'stackoverflow'@'localhost');"
mysql_query("CREATE DATABASE my_db",$connection); //create database
}else{
}
Here is a cli script i wrote (modified other copy-pasta) which creates a database, username and password (all the same) which works in php 7.4
<?php
$usage =
"dbusercreate - quickly create a db with user and password with privileges\n".
'php '.$argv[0].' <db-user-pass> <root-password>'."\n";
if (empty($argv[1])){
die($usage);
}
if (empty($argv[2])){
die($usage);
}
$dbuserpass = $argv[1];
$rootpass = $argv[2];
$con=mysqli_connect("localhost","root", $rootpass);
// check con...
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error()."\n";
exit(1);
}
// Create database
$sql="CREATE DATABASE $dbuserpass";
echo $sql."\n";
if (mysqli_query($con,$sql) == false){
echo "Error creating database: " . mysqli_error($con)."\n";
exit(1);
}
// Create user
$sql='GRANT usage on *.* to ' . $dbuserpass . '@localhost identified by ' . "'" . "$dbuserpass" . "'";
echo "$sql"."\n";
if (mysqli_query($con,$sql) == false){
echo "Error creating database user: " . mysqli_error($con)."\n";
exit(1);
}
// Create user permissions
$sql="GRANT all privileges on $dbuserpass.* to $dbuserpass@localhost";
echo "$sql"."\n";
if (mysqli_query($con,$sql) == false){
echo "Error creating database user: " . mysqli_error($con)."\n";
exit(1);
}
echo "Database Name: $dbuserpass"."\n";
echo "Database Username: $dbuserpass"."\n";
echo "Database Password: $dbuserpass"."\n";
echo "Database Host: localhost"."\n";
