Automated way of generating PHP to create tables?

Posted: Sun Jan 07, 2007 6:55 pm

I'm making a module that has several database tables. I would like to provide a PHP script to admins that they can run one time only to create the tables and populate any required initial data when they install the module. The only reason I am considering this is so I can take account the Nuke $prefix. Otherwise I would just give the user an export of the SQL.

Right now I'm thinking of just doing an export on my tables and saving that to a file. And then just use my favorite editor to turn the SQL into PHP. Or maybe write a Perl script to do that. But is there a better way? I didn't see anything in phpMyAdmin for this. Thanks.

registered · Posted: Sun Jan 07, 2007 10:44 pm

I've not seen anything quite like that. But your approach should work, you could write a script to easily change the SQL exported to PHP code for phpNuke

registered · Posted: Sun Jan 07, 2007 11:13 pm

Well, interestingly, I wonder if phpMyAdmin could be used for it. If you took this in steps, you could possibly use the "Create PHP Code" button AFTER you run the particular SQL in question. Copy and paste that out to your editor and then just replace the nuke_ with the appropriate variable.

Sells PC To Pay For Divorce Joined: Posts: 5661

Is Only registered users can see links on this board! Get registered or login! something ?

Posted: Mon Jan 08, 2007 6:43 am

Interesting find hitwalker! Thx.

Posted: Mon Jan 08, 2007 8:43 am

montego wrote:

Well, interestingly, I wonder if phpMyAdmin could be used for it. If you took this in steps, you could possibly use the "Create PHP Code" button AFTER you run the particular SQL in question. Copy and paste that out to your editor and then just replace the nuke_ with the appropriate variable.

Hi montego! I am aware of the "Create PHP Code" button that appears after you run an SQL query. However I am not aware of any SQL query that produces the crazy long CREATE TABLE statement that I am after...if you see what I mean. Confused

A Perl script to process the exported SQL shouldn't be too bad. If I get something to work I'll release it or post it here.

Posted: Mon Jan 08, 2007 11:58 am

I'm not sure if the DB compare utility I have on my site will help you with this (its a simple php class file). It should in theory if you use it to compare against an empty database. It will display the sql queries and also provide links which when clicked will execute the query.
You might be able to develop it further for your own specific needs.

Posted: Mon Jan 08, 2007 6:26 pm

Guardian2003, thanks a lot that sounds interesting. I will look at that.

I think I found my answer in phpmyAdmin. When looking at a table, there is an Operations tab. Under that tab is a copy table operation. If you copy your table to a new table, it will generate a CREATE TABLE statement. You can then hit the "Create PHP Code" button on the results of the query! I've only got 3 tables so I think I will use that.

Posted: Mon Jan 08, 2007 7:50 pm

Gremmie, you missed my point. Your objective as stated in your first post was to provide a PHP script that an admin can run to create the new tables and populate them with the initial data that is needed. Ok, run with this example of say five new tables:

1) During the course of your development you already have the five tables in your development system and the way you want them (make the records in the tables be what you would want for a fresh install too.

2) Export the structure and data -- do not take the autoindex -- to a file and save that off.

3) Come back in mySQL and drop those tables.

4) Now copy and paste the SQL you saved off into a file into the phpMyAdmin SQL executer. Once it is done, you can click on the said "Button" and there is your SQL as PHP!

What am I missing here?

Posted: Mon Jan 08, 2007 8:12 pm

montego wrote:

What am I missing here?

Yeah, that would work too. Embarassed

I didn't think about exporting it, dropping it, and re-importing it, then hitting the "Create PHP Code" button. You are a genius!

Copying and then dropping the copy also gets the same result. Wink

I think I'm sleep deprived..... Very Happy

Posted: Mon Jan 08, 2007 10:01 pm

Actually, I was thinking that maybe I was cracked and sleep deprived so I was needing help to understand why phpMyAdmin could not be used. Laughing

Take care and good luck!

Posted: Mon Jan 08, 2007 11:23 pm

Just a thought. If you want a full PHP script, take a look at what Raven did with the installSQL.php program that comes in the INSTALLATION folder. It runs in conjunction with the sql files in the INSTALLATION/sql directory. It won't generate your CREATE statements for you but if you have those it will give your admins a menu driven way to install the tables. You don't need the whole thing probably but take a look at the way it reads in the SQL and processes it a line at a time.

Posted: Tue Jan 09, 2007 8:28 am

montego, that's why I use these forums, to bounce ideas of you pros to keep myself out of the ditch (and off the crack pipe). Razz

fkelly, thanks. I was already looking at the NukeSentinel installer scripts for ideas too. I got a skeleton script running last night. Cool