I'm currently building software that:
- 'talks' to Google Analytics API,
- fetches data,
- 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);
}