«

»

Mai 10

Beitrag drucken

Copy SQL records

Just found myself needing a way to copy and modify SQL records in one process.

I came up with the following PHP code which is rather ugly but works:

// open mysql connection and stuff
...
// select rows you want to copy, adapt as needed
$result = mysql_query("SELECT * FROM downloads WHERE pid=2");
// check if there are actually rows
if (mysql_num_rows($result) > 0) {
  // fetch columns
  $amount_fields = mysql_num_fields($result);
  $fields = array(); // create field array
  $counter = 0; // set counter var
  // build columns array
  while($counter < $amount_fields) {
    $fields[] = mysql_field_name($result, $counter); // fetch column name
    $counter++; // next column
  }
  // go through selected rows
  while($row = mysql_fetch_assoc($result)) {
    // start of insert query
    $insert = 'INSERT INTO downloads ';
    $columns = '('; // query columns part
    $values = '('; // query values part
    $counter = 0; // reset our counter
    // go through the columns
    foreach($fields as $value) {
      // the first time we do not add commas
      if ($counter > 0) {
        $columns .= ',';
        $values .= ',';
      }
      $columns .= $value; // add column
      $values .= "'".$row[$value]."'"; // add value
      // HERE would be the place to modify the values
      $counter++;
    }
    // close brackets
    $columns .= ')';
    $values .= ')';
    // finally build query
    $insert .= $columns.' VALUES '.$values;
    // run query, should check the return value
    mysql_query($insert);
  }
}

I wonder if there is a easier way. If you only need to copy (without modifying) a record you may be interested in SELECT INTO.

Permanentlink zu diesem Beitrag: http://techblog.steffmeister.at/copy-sql-records/

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>