Jump to content

Generate Tables from .SQL


chasethemetal
 Share

Recommended Posts

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

Edited by niche
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...