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
– robinsax, Commented May 12, 2020 at 19:03
You shouldn't have single quotes around the column name in ORDER BY. — Barmar
– Barmar, Commented May 12, 2020 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
– droopsnoot, Commented May 12, 2020 at 19:06

Barmar · Accepted Answer · 2020-05-12 19:10:28Z

2

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.

answered May 12, 2020 at 19:10

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jason Over a year ago

Thank you so much! It's now fixed. Thanks for the links too. I'll read them. Cheeers!

BBoriko · Accepted Answer · 2020-05-12 19:31:36Z

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, 2020 at 19:31

BBoriko

315 bronze badges

1 Comment

Jason Over a year ago

thanks, BBoriko! I imploded the array with and space and unquoted 'eng' and it worked. Cheers!

Jasen · Accepted Answer · 2020-05-13 04:58:03Z

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, 2020 at 4:58

answered May 13, 2020 at 4:52

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

2 Comments

Jason Over a year ago

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?

Jasen Over a year ago

yes, just add them to $tables. the implode() joins all the parts together with the apropriate in-fill

Collectives™ on Stack Overflow

PHP implode array to create query

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related