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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1114409
  • 565
  • 3
  • 13
  • 26
  • 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. – Danny Beckett Feb 06 '13 at 07:10
  • Create a stored procedure that do the job and call it from your php script. – peterm Feb 06 '13 at 07: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. – user1114409 Feb 06 '13 at 07: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. – Andy Groff Feb 06 '13 at 07:22
  • @AndyGroff I need a php script, as the user is interacting, by users selection, 2013-database would be created based on 2012-db and tables. Is there any other way of doing this ? This is for a financial management program – user1114409 Feb 06 '13 at 07:27
  • 1
    This is a disaster waiting to happen... TDWTF here we come ;) – Danny Beckett Feb 06 '13 at 07:30
  • Use php `exec`, and see commands here for it: http://dev.mysql.com/doc/workbench/en/mysqldbcopy.html or here http://dev.mysql.com/doc/refman/5.5/en/mysqldump-copying-to-other-server.html – Kerem Feb 06 '13 at 07:41

2 Answers2

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.

Community
  • 1
  • 1
Dan Blows
  • 20,846
  • 10
  • 65
  • 96
  • 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. – user1114409 Feb 06 '13 at 10:16
  • 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? – Dan Blows Feb 06 '13 at 10:18
  • No i donot have access to command line, I need to recreate database once in a year – user1114409 Feb 06 '13 at 10:58
  • 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. – Dan Blows Feb 06 '13 at 11:37
  • I have PHPMyAdmin access, do you mean use phpmyadmin and copy it manully ? – user1114409 Feb 06 '13 at 11:47
  • 1
    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. – Dan Blows Feb 06 '13 at 11:49
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.

Omid
  • 4,575
  • 9
  • 43
  • 74
  • 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. – user1114409 Feb 06 '13 at 07:47
  • 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 – Omid Feb 06 '13 at 07:52
  • 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 – user1114409 Feb 06 '13 at 09:57