PHP implode array to create query

Question

The idea is to create the following:

$sql = "SELECT column1, column2 FROM table1
UNION SELECT column1, column2 FROM table2
UNION SELECT column1, column2 FROM table3
ORDER BY 'column3'";

I wanna keep it DRY tho, so I came up with the following already thinking about the possible dozens of tables I might use:

$tables = array('table1', 'table2', 'table3');
$array_of_tables = array('SELECT column1, column2 FROM ', $tables[0]);

for($i = 1; $i < count($tables); $i++){
    array_push($array_of_tables, "UNION SELECT column1, column2 FROM " . $tables[$i]);
}

array_push($array_of_tables, "ORDER BY 'eng'" . '"');

$sql = implode('', $array_of_tables);

I get the correct string, however I'm not able to use it. Could anyone please tell me what's going on and how to fix it - please, explain in newbie terms! I'm new to PHP and decided to give it a shot, even though half the internet says it's not worthy it. Cheers!

Please include what the actual issue you encountered when running your attempt is, including a traceback. — robinsax, May 12 '20 at 19:03
You shouldn't have single quotes around the column name in `ORDER BY`. — Barmar, May 12 '20 at 19:04
I wondered if there are missing spaces around the various sections of the query. Maybe imploding with a space might make a difference. I'd expect that to be clear in the query string though. — droopsnoot, May 12 '20 at 19:06

score 2 · Accepted Answer · answered May 12 '20 at 19:10

You have the right idea, there just some problems with the details:

You have no whitespace between the pieces that you're imploding, so it's ending up like ... FROM table1UNION SELECT .... Use implode(' ', $array_of_tables).
You have quotes around the column in the ORDER BY clause. That will order by a literal string, not the column values. See When to use single quotes, double quotes, and backticks in MySQL.

If you need to combine these tables frequently, you might want to look into using MySQL's MERGE Storage Engine. This allows you to create a virtual table whose contents are the combination of several other tables.

Thank you so much! It's now fixed. Thanks for the links too. I'll read them. Cheeers! — Jason, May 12 '20 at 19:35

score 2 · Answer 2 · answered May 12 '20 at 19:31

2

try this

<?php
$tables = array('table1', 'table2', 'table3');
$array_of_tables = array('SELECT column1, column2 FROM ', $tables[0]);

for ($i = 1; $i < count($tables); $i++) {
    array_push($array_of_tables, " UNION SELECT column1, column2 FROM " . $tables[$i]);
}

array_push($array_of_tables, " ORDER BY 'eng'");

$sql = implode('', $array_of_tables);

answered May 12 '20 at 19:31

BBoriko

31
5

1

thanks, BBoriko! I imploded the array with and space and unquoted 'eng' and it worked. Cheers! – Jason May 13 '20 at 17:35

Jasen · Answer 3 · 2020-05-13T04:58:03.377

1

I don't understand the intent of the for loop. I would solve it like this:

$tables = array('table1', 'table2', 'table3');
$sel = "SELECT column1, column2 FROM ";
$sql = $sel . implode(" UNION $sel",$tables) . " ORDER BY 'eng'";

edited May 13 '20 at 04:58

answered May 13 '20 at 04:52

Jasen

11,837
2
30
48

thank you for your response! How would I do if I keep adding tables and want them to be displayed? Should I keep adding the tables to $tables or is there a way of doing it like using ...args in JS? – Jason May 13 '20 at 15:41
yes, just add them to `$tables`. the `implode()` joins all the parts together with the apropriate in-fill – Jasen May 13 '20 at 22:35

PHP implode array to create query

3 Answers3