Transfer data into optimized MySQL tables from "raw" tables in cron with php

I'm working with an MLS real estate listing provider (RETS). Every 48 hours we will be pulling data from their server in a cron job to an SQL database. I'm charged with the task of writing a php script that will be run after the data from the remote server is dumped into our "raw" tables. In these raw tables, all columns are VARCHAR(255), and we want to move the data into optimized tables. Before I send my script to the guy in charge of setting up the cron job, I wondered if there is a more efficient way to do it so I don't look foolish.

Here's what I'm doing:

There are 8 total tables, 4 raw and 4 optimized - all in the same database. The raw table column names are non descriptive, like c1,c2,c2,c4 etc. This is intentional because the data that goes in each column may change. The raw table column names are mapped to the correct optimized table columns with php, something like this:

$tables['optimized_table_name1']['raw_table'] = 'raw_table_name1';
$tables['optimized_table_name1']['data_map'] = array(
    'c1'    =>  array( // <--- "c1" is the raw table column name
                    'column_name'   =>  'id',
                    // I use other values for table creation,
                    // but they don't matter to the question.
                    // Just explaining why the array looks like this
                    //'type'        =>  'VARCHAR',
                    //'max_length'  =>  45,
                    //'primary_key' =>  FALSE,
                    // etc.
                ),
    'c9'    =>  array('column_name' =>  'address'),
    'c25'   =>  array('column_name' =>  'baths'),
    'c2'    =>  array('column_name' =>  'bedrooms') //etc.
    );

I'm doing the same thing for each of the 4 tables: SELECT * FROM the raw table, read the config array and create a huge SQL insert statement, TRUNCATE the optimized table, then run the INSERT query.

foreach ($tables as $table_name => $config):

$raw_table  =   $config['raw_table'];
$data_map   =   $config['data_map'];
$fields =   array();
$values =   array();
$count      =   0;

// Get the raw data and create an array mapped to the optimized table columns.
$query = mysql_query("SELECT * FROM dbname.{$raw_table}");

while ($row = mysql_fetch_assoc($query))
{
    // Reading column names from my config file on first pass
    // Setting up the array, will only run once per table
    if (empty($fields))
    {
        foreach ($row as $key => $val)
        {// Produces an array with the column names
            $fields[] = $data_map[$key]['column_name'];
        }
    }
    foreach ($row as $key => $val)
    {// Assigns data to an array to be imploded later
        $values[$count][] = $val;
    }
    $count++;
}

// Create the INSERT statement string
$insert = array();
$sql = "\nINSERT INTO `{$table_name}` (`".implode('`,`', $fields)."`) VALUES\n";
foreach ($values as $key => $vals)
{
    foreach ($vals as &$val)
    {
        // Escape the data
        $val = mysql_real_escape_string($val);
    }
    // Using implode for simplicity, could avoid the nested foreach if I wanted to
    $insert[] = "('".implode("','", $vals)."')";
}
$sql .= implode(",\n", $insert).";\n";

// TRUNCATE optimized table and run INSERT query here

endforeach;

Which produces something like this (only larger - about 15,000 records max per table, and one insert statement per table):

INSERT INTO `optimized_table_name1` (`id`,`beds`,`baths`,`town`) VALUES
('50300584','2','1','Fairfield'),
('87560584','3','2','New Haven'),
('76545584','2','1','Bristol');

Now I'll admit, I have been under the wing of an ORM for a long time and am not up on my vanilla mysql/php. This is a pretty simple task and I want to keep the code simple.

My questions:

1. Is the TRUNCATE/INSERT method a good way to do this?
2. Is there anything about my code that you can see being a problem? I know you see nested foreach loops and just shudder, but I want to keep the code as small clean as possible and avoid lots of messy string concatenation (to produce the insert query). Like I said, I also haven't used native php functions for SQL in a long time.
3. I feel like it really doesn't matter if the code is not optimized if it is run at 3AM every 2 days. Does it matter? Is this code going to preform OK?
4. Is there a better overa开发者_StackOverflow社区ll strategy to accomplish this task?
5. Do I need to be using transactions?
6. How can I be aware of errors that may occur in cron scripts?

Apologize if I don't use correct cron jargon, it's new to me.

Keep it simple. ORM would be swell for this task.

Answers:

1. Yes.
2. Your code is readable. At least I did not have any problems to read it.
3. We had a script that ran early in the morning. It was not optimized and consumed a lot of memory. After FOUR years it started to consume over 512 Mb. I've spent 2 hours to optimize it to, so now it consumes 7 Mb (pretty good optimization, huh? :) ). I personally think it is "ok" that your script is not optimized now. If this script will start failing, you'll figure what the problem is. Maybe it will exhaust memory, maybe your SQL queries will cause deadlocks... maybe you will later optimize it to READ from slave servers... I don't know, but it works fine now, that's okay.
4. I'd do something similar to your code. But I'd probably generate file first and load data into the server by running shell command mysql -u username --password=password < import_file.sql. So I'd have my file stored somewhere on a disk so I cal always take a look at it. And maybe even edit for one-time correction load. But you still can do it by writing your sql statement into file.
5. No. It is just one query. If you use InnoDB engine it is already a transaction.
6. First, use error_reporting(E_ALL & ~E_NOTICE). Second, use mysql_error PHP function to ensure your query performed correctly. Third, in your cronjob output errors stream into some file like so: 0 7 * * 0 /path/to/php -c /path/to/php.ini /path/to/script.php 2> /tmp/errors_file And thus you can create SECOND script runnin after first one to notify about errors in script.php by email or.... whatever way of notifying you prefer. I'd prefer to register_shutdown_functions that would check for error_file and if it is not empty, notify you and delete it afterwards.

Just my opinion, but I hope my answer helps though.