Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm currently building software that:

  1. 'talks' to Google Analytics API,
  2. fetches data,
  3. persists the data to a MySql db.

Insertion of data into the db needs to happen automatically - based on the received headers and rows. This is a utility method that I wrote to generate the needed sql:

public static function generateInsertQuery($tableName, array $headers, array $rows)
{
    /**
     * Normalize column names
     */
    $columns = [];
    foreach($headers as $header) {
        $columns[] = str_replace(':', '_', $header['name']);
    }

    $columnsStr = implode(',', $columns);

    /**
     * Make initial sql with placeholders ready
     */
    $sql = "INSERT INTO $tableName ($columnsStr) VALUES %s";
    $value = '(%s)';
    $values = [];

    /**
     * Quote string values to insert
     */
    foreach ($rows as $row) {
        foreach ($row as &$entry) {
            $entry = "'" . $entry . "'";
        }

        $formattedRow = sprintf($value, implode(',', $row));
        $values[] = $formattedRow;
    }

    /** Generate sql */
    $sql = sprintf($sql, implode(',', $values));

    return $sql;
}

As you can see, the sql will be generated dynamically each time the method is called.

Nevertheless, when I look at this method I have an impression that it could be improved and 'prettified' a lot.

I'd be very grateful to receive your review and perhaps some advice on how I can do it better.

P.S.

You might be asking - how is that supposed to work with a permanent db schema since we do not know what data will be inserted. This issue, however, has been sorted. I'm working with Doctrine DBAL and I am using Doctrine\DBAL\Schema\Comparator to make sure the schema is ready for data insertion:

    /**
     * Compare the current db schema with the new schema
     */
    $schemaDiff = $this->schemaComparator->compare($currentSchema, $this->schema);
    $saveQueries = $schemaDiff->toSaveSql($platform);

    /**
     * Changed the db schema to if there are any differences in a safe mode
     * - no data will ever be deleted
     */
    if (!empty($saveQueries)) {
        $this->updateSchema($saveQueries);
    }
share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.