inserting data into DB from .sql file uisng php


#1

Hello Guys,

In my website, everytime i delete a user, i backup their whole profile and related data into a sql file.
a sql backup for a given user will look like this

[code]USE database_name; //tell which database to insert data.

INSERT INTO table1 VALUES();
INSERT INTO table2 VALUES();
INSERT INTO table3 VALUES();[/code]
[sub]note: a real back up sql file will contain hundred of rows.[/sub]

Now i decided to integrate into my website a way to restore a deleted user, by reading the sql codes from the file into a variable and execute it back to the database. I’m already up to the point where the $var just hangs in there with the whole file waiting for me to do something with it.

I was reading php functions such as exec & shell_exec which i’m unfamiliar with.

my goal is to run the whole file into the database at once. i’m trying to avoid a loop to run row by row.

any advice/approach or point to the right direction is greatly appreciated.

Thanks,
Wilson B


#2

Since your now wanting to restore users you might be better off just setting an active/inactive flag rather than deleting the data.


#3

I like your Point Kevin.

I’m interest in this method not only to be able to restore an user but as well because I cron a daily backup of my whole website.

I’m really looking to learn how I can run a whole file of SQL codes including CREATE and INSERT statements.

My goal is to be able to roll back my website db data with the power of a simple click.


#4

To back it up with a click you will just use a PHP script using MySQL data outfile function. To import the file you would use PHP to execute the MySQL data in file function.


#5

What you are wanting to do, is actually accomplished when you are running a VPS. The flag, either isActive or isDeleted is the professional standard. You would add a where clause that checks for the proper value on selecting. From the admin stand point, you would allow the use flag to be modified.

Running anything that allows shell access is not a good idea for a beginner to attempt. It has large security implications that could wipe everything out (including you backup copies). So, I would advise you to stay away from that.


#6

Guys,

I have added isActive columm to a few tables and just check value on SELECT queries.

for now i will stay away from what I wanted to accomplish.

thanks for the recommendation.