How to create a MySql database using shell_exec ph

Question

I am running MySql + PHP inside a IIS and windows 7. This is the php code that I am using to try to create a new database somedb:

$cmd = escapeshellcmd('mysql -u root -p myPasswd  -h localhost -Bse "create database somedb;" ');
$test = shell_exec("C:\\Program^ Files^ ^(x86)\\MySQL\\MySQL^ Server^ 5.6\\bin\\".$cmd);

var_dump($test);
echo "<br>============<br>";
print_r($test);

I put var_dump and print_r only to check the output. After running the code I have this output:

string 'C:\Program  Ver 14.14 Distrib 5.6.23, for Win32 (x86)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: C:\Program [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table'... (length=12085)
=========================================================================================
C:\Program Ver 14.14 Distrib 5.6.23, for Win32 (x86) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Usage: C:\Program [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion, but startup and reconnecting may take a longer time. Disable with --disable-auto-rehash. (Defaults to on; use --skip-auto-rehash to disable.) -A, --no-auto-rehash No automatic rehashing. One has to use 'rehash' to get table and field completion. This gives a quicker start of mysql and disables rehashing on reconnect. --auto-vertical-output Automatically switch to vertical output mode if the result is wider than the terminal width. -B, --batch Don't use history file. Disable interactive behavior. (Enables --silent.) --bind-address=name IP address to bind to... 

So it seems that shell_exec can call the mysql inside the bin directotry but it is not creating the database. It is not a permission issue. I've tried to create using sql statement inside command line and it works good. I already tried to create without -h localhost, or changing -Bse for -e or --execute. I've tried all suggestions of this post Create database in Shell Script - convert from PHP but it is not working for me. What am I missing?

===========================================================================

Only to update, this is a code that works good if I run in Apache+win7:

echo shell_exec("C:\\xampp\\mysql\\bin\\mysql   -u root    -e \"CREATE DATABASE IF NOT EXISTS somedb\" ");

It creates the database and display nothing in the browser. Note: no matter with the space between -u and root.

Answer 1

Ditch the spaces after -u and -p for starters. I know it looks weird, but mysql has an aneurysm if u don't.

Also if you are in php anyway, why are you doing this? Just rifle off a mysql command from php and no shell. If the active connection that you have lacks the credentials to do it, use a new connection object with proper credentials, do it, then close that connection.

This does bring me to the embarrassing but necessary question of, when u shell, what box is that shell running on, a client machine that is not the server?

Answer 2

Ok I found the solution:

1. I follow this tutorial to add system environment variables. Start at 7:40. It was made to PHP but we can do the same for MySql. https://www.youtube.com/watch?v=AEyzKm2pTBw&index=51&list=PL1C87CCACA68A94A3 . Just find what is the path to the bin folder inside mysql folder instalation. In my case it is C:Program Files (x86)\MySQL\MySQL Server 5.6\bin. Just copy and paste inside System VAriables Path.
2. Restart the computer, so windows can recognize the new variables when start the system.
3. Make a test. Run command line. Just type mysql -uUsername -pPassword. Should be able to enter in mysql.

4. Go to php code and delete the path to mysql. The new shell_exec should be like this:

   echo shell_exec("mysql -umYusername -pmYpassword -e \"CREATE DATABASE IF NOT EXISTS somedb\" ");

5. Note: AsConfused was rigth. We must to avoid empty space between -u and -p

6. Run the code. The database should be created!