restore mysql structure from php pages

[yandoo]

Hiya I know this is probably a long shot but is there a programme or something out there that can use to search my .php pages to identify the structure of its related database?

 

I lost my .sql file and the only way to restore the structure of the db at this point is to manually go through each .php page to identify and re-create the structure.

 

Thank you

Edited February 14, 2014 by yandoo

[mac_gyver]

the question has been asked many times before and there has never been a script/link posted that does this, primarily due to the variability in how queries can be formed (you could for example use a defined-constant/variable for any part of the table or column names) and how the data can be fetched from a query and with people using things like SELECT * (it would take knowing what php variable the result is fetched into, then find either the array index names or object property names...)

 

is your actual database lost/corrupted or a just your defining .sql file, because you can always get the table definitions from the actual database.

[yandoo]

Thank you for your reply,

 

I actually lost the db after i reinstalled my o/s and can't find the .sql. I noticed in the notes file i have many .mno files, they look like they could be of some use. The task is so mammoth though and manually will take ages to analyse and rebuild the appropriate structure.

[mac_gyver]

you shouldn't have that many different queries to look at.

 

i do have some suggestions -

 

1) search for the mysql_query(....) statements to find where all the queries are at.

 

2) remove the actual sql query string from inside the mysql_query(....) statement and form the query string in a php variable, $query for example, then put the php variable into the mysql_query($query) statement. this will separate the query statement from the code running the query. this will accomplish a couple things - 1) allow you to echo/log the actual sql query statements to help with the process of rebuilding your database table structure (it also helps with normal troubleshooting), 2) allow you to more easily eliminate the mysql_ functions, which are depreciated and need to be changed to either mysqli or PDO functions.

 

3) while you are doing item #2, convert any * in the query to the actual list of column names, gotten from the references in the php code that is fetching the data from the query. looking at how the php code is using the data will also help identify the data types for the columns.

 

4) you could at this point write your own query function that accepts the $query variable, echoes or logs the query statement, runs the query with error checking, and echoes or logs the error, then returns the result from the query to the calling code. you would do a search/replace to change all the mysql_query() statement names into a call to your own query function.

 

5) examining the echoed/logged queries and query errors will help you iteratively create the tables/columns.

 

6) after you have finished recreating your database tables and every query runs error free, convert your code to use either the msyqli or PDO database functions.

[boompa]

Save yourself from this hassle in the future by using a source code control repository like git, hg, svn, etc. For best results either use a hosted repository (github, bitbucket, CloudForge, Assembla, etc) or back up your repo off-site.

[yandoo]

Thank you very much for all your replies.

 

Sorry for my late reply, the weather knocked out the power.

 

Thank you