backup systems ideas???

[cooldude832]

I have a database that stores customer data across 4 tables.

 

I want to be able to extract a single customer's data in a way that I can put it back up to the server (or another equivalent server) so in case I delete a customer and reload it if needed.

 

Is there a easy way I can generate a single or multiple INSERT INTO queries without have to query out the data and build the queries then reconstruct them?

 

My thought process which I find bad is

 

#Get UserID )(verify its valid)
# Select * from `t1`
#while row on t1 query extract build query store that insert query into an array

#repeat for t2,t3,t4

# put the array into a file and go.

 

The resulting file can be a .sql file because the reloader isn't built yet.

[neromir]

Is there a easy way I can generate a single or multiple INSERT INTO queries without have to query out the data and build the queries then reconstruct them?

 

I don't think I understand the quoted statement, exactly... What do you mean by "and build the queries then reconstruct them?"

 

At any rate, here's a shot at answering your question.

 

I know in MySQL you can do this:

 

<?php
  $result = mysql_query("SELECT * FROM t1");
  $row = mysql_fetch_array($result);

  $recoveryQuery = "INSERT INTO t1(";
  $recoveryValues = "VALUES(";
  $i = 0;

  foreach($row as $columnName => $value)
  {
    if($i > 0)
    {
      $recoveryQuery .= ", ";
      $recoveryValues .= ", ";
    }
    $recoveryQuery .= $columnName;
    $recoveryValues .= $value;
  }

  $totalQuery = $recoveryQuery .") ". $recoveryValues.")";
?>

 

Now, the deal with $i is kind of a hack I've used in the past to tell you whether or not you need to insert a comma into the query-- I haven't been able to come up with a better way to do it and still be able to use a foreach.

 

You'd probably also have to do some detection on $value to determine if it's a string type or not-- you could easily do that with several built in functions in PHP-- I just can't remember what their names are right now.  is_numeric() will probably work.

 

Note this isn't a complete solution, but this might at least get you started.  I would imagine there is something similar depending on the database interface you're using.