Move Current DB Structure to a New One

[bschultz]

I have a database table with the following structure:

 

table calendar

 

columns:

 

row_number (key, auto increment)

date (mysql date)

sport

visitor

home

time (varchar)

ump1

ump2

ump3

ump4

ump5

field

notes

 

 

I want to switch to a different format

 

new table games

 

columns:

 

game_id (key, auto increment)

sport

day (mysql date)

visitor

home

start (time)

field

notes

level

 

 

 

new table scheduled_umps

 

columns:

 

row (key, auto increment)

game_id (needs to match the game_id of the games table)

ump

 

 

 

I need to copy the data from the current one table into the two new tables.  How would I go about this?  You'll notice that the current table has a field for each of five possible umpires.  The new structure will keep the game_id of the game, and have up to five matches for the same game_id...in the scheduled_umps table.

 

Thanks!

[gizmola]

Write a conversion program.  Do you know PHP? 

[bschultz]

I know a little.  I'm guessing I'd write a select to grab all the info...then a second sql query to insert the new first table's info...then a third sql query to insert into the new second table.  The only part I have NO clue about, is keeping the game_id the same for both new tables.

[dawsba]

<?php

  function q($query,$assoc=1) {
   $r = @mysql_query($query);
   if( mysql_errno() ) {
       $error = "<script>window.location='?ref=0'</script>";//'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). '</small><br><VAR>$query</VAR>';
       echo($error); return FALSE;
   } 
   if( strtolower(substr($query,0,6)) != 'select' ) return array(mysql_affected_rows(),mysql_insert_id());
   $count = @mysql_num_rows($r);
   if( !$count ) return 0;
   if( $count == 1 ) {
       if( $assoc ) $f = mysql_fetch_assoc($r);
       else $f = mysql_fetch_row($r);
       mysql_free_result($r);
       if( count($f) == 1 ) {
           list($key) = array_keys($f);    
           return $f[$key];
       } else {
           $all = array();
           $all[] = $f;
           return $all;
       }
   } else {
       $all = array();
       for( $i = 0; $i < $count; $i++ ) {
           if( $assoc ) $f = mysql_fetch_assoc($r);
           else $f = mysql_fetch_row($r);
           $all[] = $f;
       }
       mysql_free_result($r);
       return $all;
    }
  }

  $sql = "SELECT * FROM `calendar`";
  $result2 = mysql_db_query(YOURDATABASE,$sql,YOURLINK) or die("Invalid Query<br>". mysql_error());
  while ($r3 = mysql_fetch_array($result2))
  {
    $new_id = q("INSERT INTO `games` (game_id,sport,day,visitor,home,start,field,notes,level)VALUES('','".$r3[sport]."','".$r3[date]."','".$r3[visitor]."','".$r3[home]."','$r3[time]','".$r3[field]."','".$r3[notes]."','".$r3[level]."')");
    $new_id = $new_id[0];
    for($i=1;$i<=5;$i++)
    {
      q("INSERT INTO `scheduled_umps` (row,game_id,ump)VALUES('','".$new_id."','".$r3['ump'.$i]."')");
    }
  }
?>

[gizmola]

I know a little.  I'm guessing I'd write a select to grab all the info...then a second sql query to insert the new first table's info...then a third sql query to insert into the new second table.  The only part I have NO clue about, is keeping the game_id the same for both new tables.

 

Yes.  This is not an issue, even for a table that has an auto_increment key, because mysql will only auto_increment when you omit the auto_increment column in the INSERT or pass a NULL value.  dawsha's code looks like it could work for you.

[bschultz]

Thanks for the help on this.

 

The code above inserted about 40,000 rows into the DB before I hit stop on the browser.  There were only 416 games in the original database...so obviously something is wrong with the loop.

 

Also, the old db structure had a name in the ump1 ump2, and so on.

 

The new structure will have that umpires id (integer)

 

I think to simplify, I'll do two steps.  One step to move the games info...and one to move the umpire info.

 

For the first step, I have this:

 

<?php
require "config.php";

$dbc = mysql_pconnect($host, $username, $password);  
mysql_select_db($db,$dbc);  
//now get stuff from a table  
$sql = "SELECT * FROM calendar";           
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 

$row_number = $row[row_number];
$date = $row[date];
$sport = $row[sport];
$visitor = $row[visitor];
$home = $row[home];
$time = $row[time];
$ump1 = $row[ump1];
$ump2 = $row[ump2];
$ump3 = $row[ump3];
$ump4 = $row[ump4];
$ump5 = $row[ump5];
$field = $row[field];
$notes = $row[notes];


$sqlquery = "INSERT INTO games (game_id, sport, day, visitor, home, start, field, notes, level) VALUES ('$row_number', '$sport', '$date', '$visitor', '$home', '$time', '$field', '$notes', '')";

$results = mysql_query($sqlquery);

}
?>

 

That inserted 410 rows.  What happened to the other 6 rows?  How can I find out which ones are missing?

 

 

[gizmola]

So you have a seperate umpires table?  Does the current ump1..umpN columns have names in there?  If so what you'll need to do is do a query for each umpire to see if they are already in the umpire table, and if so, get the row, so you have their umpire_id to store in the scheduled_umps row.  If you don't find them, then you instead need to insert them into the umpire table, and call mysql_insert_id.

 

I can't help you with your row number discrepency ... we don't have access to your database, but I'm guessing there's a logic error or the data in some of the rows is incomplete and some of your inserts are failing.

[bschultz]

The current table has all the game info AND the umpire names.  There is a NEW table for umpire info (including names and umpire ID).

 

I'll dig into the first step row number problem first.  Thanks again.