Search code examples
sqlalter

Modify SQL query

Somewhere in my application, I have a query:

$sql = SELECT * FROM table ORDER BY column1;

I pass this query to my class, which generates a table:

$table = new HtmlTable($sql)

This $table object has a method 'groupBy', which should alter the $sql string and it should add second column in the ORDER BY clause, so the query should look like:

$sql = SELECT * FROM table ORDER BY added_column, column;

I tried this:

select * from (SELECT * FROM table ORDER BY column1) innerSQL ORDER BY innerSQL.column2

but it does not sort correctly ..

Actually, what I need is this:

$sql = SELECT * FROM table ORDER BY column2, column1;

But i don't know, how can I alter the $sql string inside the HtmlTable class ..

Any ideas?

Solution

  • You can use a regular expression to do it. Here's an example of how your method could look like:

    protected function groupBy($sql) {
  return preg_replace('/ORDER BY (.*)/i', 'ORDER BY SomeField, $1', $sql);
}

    What it does is replacing "ORDER BY " with "ORDER BY , SomeField". Here's an example to test it:

    $sql = 'SELECT * FROM table ORDER BY column1';
$sql = preg_replace('/ORDER BY (.*)/i', 'ORDER BY SomeField, $1', $sql);
echo $sql;