1

Using a PHP script I need to do the below, as user selects a financial year 2013 and I need a separate database for 2013:

My 2012 database contains 105 tables. I need a PHP script that will "read" the structure of this database and it's tables and then create an identical database with all tables.

Can someone help please?

Improve this question
7
  • This sounds like a lot more work than just fixing up your main database to accept multiple years. What have you tried? Where is your source code? Nobody is going to sit here writing reams of PHP for you. Commented Feb 6, 2013 at 7:10
  • Create a stored procedure that do the job and call it from your php script. Commented Feb 6, 2013 at 7:12
  • @DannyBeckett I am asking for a direction or a link which can be tried or understood, not asking for a script to be written by someone exclusively. Thank you for your suggestion. Commented Feb 6, 2013 at 7:14
  • 2
    Why do you want to copy a database? Is there a reason it needs to be PHP? You'll probably have better luck with mysqldump if that's an option. I would do it in python or another scripting language if that's available. Commented Feb 6, 2013 at 7:22
  • 1
    This is a disaster waiting to happen... TDWTF here we come ;) Commented Feb 6, 2013 at 7:30

2 Answers 2

Reset to default
1

You could use exec() to run mysqldump with the -d switch to tell it to copy structure only. This question has an example.

As a security tip, don't use the root account for this. Create defined roles for just dumping the database, and another for creating databases. That way, if you're hacked, the potential for damage is limited.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I tried exec() and mysqldump but i get a error like this: Warning: exec() has been disabled for security reasons in /home/tech17/public_html/jvga2/databasedupli.php on line 3, so how do i enable this to work.
OK, no exec. Do you have command-line access onto the box, so that you could run it manually? How often do you need to recreate the database?
No i donot have access to command line, I need to recreate database once in a year
Are you able to connect to MySQL through something like Toad (Windows) or Sequel Pro (OSX)? Or do you have PHPMyAdmin installed? If not, then you could use mysql_list_tables(), mysql_list_fields(), and mysql_field_type() to generate the SQL. However, I'd rather you than me. Perhaps since it's only once per year, you could go the human way: contact the host and ask for them to enable CLI access or at least run mysqldump for you.
PHPMyAdmin has an 'export' function. I don't know whether it relies on exec() but it's worth a go. If it does work, it will give you all the SQL you need to recreate the database and it's tables.
|
0

This is so BUGGY to doing such thing. I'm experienced in financial programs and know what are you trying to do.

I suggest you to have SCHEMA in a simple query and try to create tables manually. That was finally what i did after testing more than 20 solutions.

None of scripts were stable, Specially on the web. you have to wast a lot of time to repair your databases.

I see that could be fiddly but totally worth the times you spent. SQLyog was a good tool to copy and get all SCHEMA immediately.

Improve this answer

3 Comments

In this kind of situation where financial years would define the start and end of transactions. What is the best approach you have adapted to separate the data. Pl elaborate.
Each database has named with suffix (financial year) such as 'acc_2012' and had a config table to save its specific settings. Financial Year was saved in this table
Please clarify this point, You mean, i should have different databases for 2012, 2013, these filenames stored in a table. I need to query based on the years selected by the user, Right! But what about the 2013 database creation, should i use SQLyog to create manually a blank database with tables and not use script ? pl clarify

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.