When working with SQL and PHP, there are times when you might want to convert SQL queries into structured arrays for easier manipulation or integration. This can be especially useful in scenarios like creating schema definitions or processing complex queries dynamically. In this blog, we’ll explore how to achieve this by focusing on CREATE TABLE queries and extending our approach to handle more complex queries like SELECT or JOIN using a powerful library.
Why Convert SQL Queries into Arrays?
SQL queries are inherently unstructured strings that are easy for databases to understand but not as flexible for programmatic manipulation. By converting them into arrays, you can:
- Dynamically process queries.
- Validate and modify structures.
- Create reusable definitions (e.g., schema generation).
- Integrate them into frameworks or tools more effectively.
Let’s start with a basic implementation for converting CREATE TABLE queries into arrays.
Step 1: Parsing CREATE TABLE Queries
Here’s an example class that converts CREATE TABLE queries into a structured PHP array:
PHP Class: SQLToSchemaConverter
class SQLToSchemaConverter
{
/**
* Converts an SQL CREATE TABLE query into a schema array.
*
* @param string $sqlQuery The SQL CREATE TABLE query.
* @return array The converted schema array.
*/
public static function convert($sqlQuery)
{
$schema = [
'columns' => [],
'primary_key' => [],
'indexes' => [],
];
// Remove newlines and extra spaces for easier parsing
$sqlQuery = preg_replace('/\s+/', ' ', $sqlQuery);
// Extract column definitions
if (preg_match('/\((.*)\)/s', $sqlQuery, $matches)) {
$tableDefinition = $matches[1];
$definitions = explode(',', $tableDefinition);
foreach ($definitions as $definition) {
$definition = trim($definition);
// Handle PRIMARY KEY
if (stripos($definition, 'PRIMARY KEY') === 0) {
if (preg_match('/\(([^)]+)\)/', $definition, $keyMatches)) {
$primaryKeys = array_map('trim', explode(',', $keyMatches[1]));
$schema['primary_key'] = $primaryKeys;
}
continue;
}
// Handle indexes
if (stripos($definition, 'KEY') === 0 || stripos($definition, 'INDEX') === 0) {
if (preg_match('/KEY\s+`?([^`(]+)`?\s+\(([^)]+)\)/i', $definition, $indexMatches)) {
$schema['indexes'][$indexMatches[1]] = [
'columns' => array_map('trim', explode(',', $indexMatches[2])),
];
}
continue;
}
// Handle column definitions
if (preg_match('/^`?([^`]+)`?\s+([^\s]+)(.*)$/', $definition, $columnMatches)) {
$columnName = $columnMatches[1];
$columnType = $columnMatches[2];
$attributes = trim($columnMatches[3]);
// Detect AUTO_INCREMENT
$isPrimary = stripos($attributes, 'AUTO_INCREMENT') !== false;
$schema['columns'][$columnName] = [
'type' => strtoupper($columnType),
'attributes' => $attributes,
];
if ($isPrimary) {
$schema['columns'][$columnName]['primary'] = true;
}
}
}
}
return $schema;
}
}Example Usage
$sqlQuery = <<<SQL
CREATE TABLE wp_posts (
ID bigint(20) unsigned NOT NULL auto_increment,
post_author bigint(20) unsigned NOT NULL default '0',
post_date datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (ID),
KEY post_name (post_name(191)),
KEY type_status_date (post_type, post_status, post_date, ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SQL;
$schema = SQLToSchemaConverter::convert($sqlQuery);
print_r($schema);Output
Array
(
[columns] => Array
(
[ID] => Array
(
[type] => BIGINT(20) UNSIGNED
[attributes] => NOT NULL auto_increment
[primary] => 1
)
[post_author] => Array
(
[type] => BIGINT(20) UNSIGNED
[attributes] => NOT NULL default '0'
)
[post_date] => Array
(
[type] => DATETIME
[attributes] => NOT NULL default '0000-00-00 00:00:00'
)
)
[primary_key] => Array
(
[0] => ID
)
[indexes] => Array
(
[post_name] => Array
(
[columns] => Array
(
[0] => post_name(191)
)
)
[type_status_date] => Array
(
[columns] => Array
(
[0] => post_type
[1] => post_status
[2] => post_date
[3] => ID
)
)
)
)Step 2: Handling Complex Queries
For more complex queries like SELECT or JOIN, building your own parser is challenging. Instead, you can leverage a library like PHP-SQL-Parser. This library provides robust tools for parsing SQL queries into structured arrays.
Example with PHP-SQL-Parser
require 'vendor/autoload.php';
use PHPSQLParser\PHPSQLParser;
$sql = <<<SQL
SELECT posts.ID, posts.post_title, users.display_name
FROM wp_posts AS posts
JOIN wp_users AS users ON posts.post_author = users.ID
WHERE posts.post_status = 'publish'
ORDER BY posts.post_date DESC;
SQL;
$parser = new PHPSQLParser();
$parsedQuery = $parser->parse($sql);
print_r($parsedQuery);Output
Array
(
[SELECT] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => posts.ID
)
[1] => Array
(
[expr_type] => colref
[base_expr] => posts.post_title
)
[2] => Array
(
[expr_type] => colref
[base_expr] => users.display_name
)
)
[FROM] => Array
(
[0] => Array
(
[table] => wp_posts
[alias] => Array
(
[name] => posts
)
)
)
[JOIN] => Array
(
[0] => Array
(
[table] => wp_users
[alias] => Array
(
[name] => users
)
[ref_clause] => Array
(
[expr_type] => colref
[base_expr] => posts.post_author = users.ID
)
)
)
[WHERE] => Array
(
[0] => Array
(
[expr_type] => colref
[base_expr] => posts.post_status = 'publish'
)
)
)Conclusion
Converting SQL queries into arrays is a powerful way to programmatically manipulate database structures and queries. For simpler use cases like CREATE TABLE, you can use custom parsers. For more complex queries, libraries like PHP-SQL-Parser make the task much easier.
Whether you’re building a schema generator, query validator, or dynamic query builder, these approaches will help you bridge the gap between SQL and PHP.
Leave a Reply