Generate Tables from .SQL

[chasethemetal]

So I get how to create a new database via PHP. But is there a way to load the tables from a .SQL file so I don't have to manually do 100's of table creates via php?Note* the .sql file would be on my server so no upload would be required.Any help would be great thanks!

[MadFly]

I assume the .sql file was an export of an database of some sort? Couldn't you use import to import the .sql file into the current database?

[niche]

Have you really already created 100s of tables in another DB or directory and you need to use them somewhere else?

[Ingolme]

If there's a delimiter for the queries you can split the SQL file into an array of individual queries and use mysql_query() with each one of them.

[chasethemetal]

I thought about using the phpMyAdmin export as php array... But that's so much code. And Niche no I don't have 100's of tables, but I do have about 30 tables and each table has 20+ fields. I don't need to copy data, just the structure... I'm building accounts for my project. And each user is a database with the same fields on the inside. So when I create an account I must generate a blank database set. Just trying to think of the work around to having to write out the database structure manually, because I plan on expanding / changing it and it would be much more efficient if I could create structure from a file not lines of code incase I change it up a lot.

[justsomeguy]

Select all of the tables to export, choose SQL file, you can specify structure only with other export options, and you can have it compress the output for a smaller file. Switch to the other database and go to the import tab and upload the file to create the structure there.

[chasethemetal]

Yes I understand how to do this manually inside phpmyadmin. But what I am trying to do is have a php script do this when a user "creates" an account. I am searching for an automated way to do this via php without having to type out the structure. If no one has any ideas, its looking like I may just have to do this by writing out the structure in php? really wish you could import structure from a .sql file in php.

[justsomeguy]

It's typical to send a system command to MySQL to have it import the file. e.g.:exec("mysql -h {$config['db_host']} -D {$config['db_name']} -u {$config['db_user']} -p{$config['db_pass']} < {$config['file_root']}/sql/copy_records.sql");http://www.php.net/manual/en/function.exec.phphttp://www.google.com/search?client=opera&...channel=suggest

[chasethemetal]

Thanks for the advice. Funny enough I spoke too soon. I thought I had the create of a database down (working in mamp), but in fact apparently it is impossible to create a database and add a user from a PHP script on my shared host. They require that I use phpMyAdmin or if I want that functionality I must upgrade to a VPS or Dedicated server. Does anyone think there is a work around and my hosting company is telling a tall tale?

[justsomeguy]

Locking a server down like that is pretty typical for a shared host. You can still try to run the console command and include the statements to create the database in the import file just to see if it works.

[chasethemetal]

I'd like to try. I know early you posted an example. Though before I even try I'm sure I will need to include my cPanel login information no? How would that code look? Any guides on accessing cPanel through PHP would be very helpful.

[chasethemetal]

Update. Using the XML API php class I was able to create a database and add a user!

<?phpinclude ('xmlapi.php');$db_host = "localhost";$cpaneluser = "username";$cpanelpass = "password";$databasename = "testtest";$databaseuser = "testuser";$databasepass = "testuser";$xmlapi = new xmlapi($db_host);$xmlapi->password_auth("".$cpaneluser."","".$cpanelpass."");$xmlapi->set_debug(1);//output actions in the error log 1 for >true and 0 false$xmlapi->set_output('array');$xmlapi->set_port(2083);//You will have to use 2082 if you don't have cURLSSL or OpenSSL compiled in you PHP binary;//set this for browser output//create database$createdb = $xmlapi->api1_query($cpaneluser, "Mysql", "adddb", array($databasename));//create user$usr = $xmlapi->api1_query($cpaneluser, "Mysql", "adduser", array($databaseuser, $databasepass));//add user to db$addusr = $xmlapi->api1_query($cpaneluser, "Mysql", "adduserdb", array("".$cpaneluser."_".$databasename."", "".$cpaneluser."_".$databaseuser."", 'all'));?>

Does anyone have an idea to add a line that will import a table structure from an existing empty template database?

[chasethemetal]

Hey all. Still having difficulties trying to import a .sql file from PHP into a database. I tried justsomeguy's method...exec("mysql -h {$config['db_host']} -D {$config['db_name']} -u {$config['db_user']} -p{$config['db_pass']} < {$config['file_root']}/sql/copy_records.sql");But it didn't do anything... Though I could have done something silly, most likely with the 'file_root' part, anyways... Let me explain my simple situation a little more clearly.I have an empty database named "test" it has a user named testuser with a pass of testpass, it has full permissions to this database. now located on my server I have a sql file located here... home/myusername/public_html/website/sql/template.sql or www.website.com/sql/template.sql (not sure which one to use BUT i did try both)I am just trying to run a script that will grab that .sql file and import it to my empty database test. Just as one would do in PHPmyAdmin's import tab. The reason I need it to be a script is I am building accounts. So when someone signs up the script needs to run, then creating their blank database template.

[justsomeguy]

That's what the command I showed will do, as long as the values are correct. Make sure you use the correct host name, user name, password, etc. The filename after the less-than sign should be the full local path to the SQL file.

[chasethemetal]

Yes thats what I figured... This is what I am doing. I know the credentials are correct. See anything that looks fishy?exec("mysql -h {$config['localhost']} -D {$config['user_test']} -u {$config['user_testuser']} -p {$config['testpass']} < {$config['/home/user/public_html']}/website.com/sql/template.sql");The SQL file is set up to CREATE TABLE IF NOT EXISTS. And all information inside seems correct. Yet when I run this script nothing happens. the script is located at website.com/signup/fillDB.php

[justsomeguy]

You should be able to test the command over SSH to see if it works, or echo the output from exec to see if there's an error message. The space you added after -p is not correct, you're telling MySQL to prompt for a password and then you're saying to use your password as the default database.It looks like you've filled in my config array with the values you're trying to use. My application stores configuration settings in an array called $config, I left it there just to show the names. If you're not using a $config array then remove the array syntax and just write your details there. Look at the examples on this page:http://dev.mysql.com/doc/refman/5.0/en/com...ne-options.html

[chasethemetal]

Thank you. This is the final line of code that makes for a successful import.exec("mysql user_test -u user_testuser -ptestpass < /home/user/public_html/website.com/sql/fillDB.sql");Thank you. This topic has been solved.