PHP select multiple Array tables

Question

Is it possible to SELECT with multiple Array tables. I know it sounds confusing but here is what I have done :

First of all, I've created a form, that has two checkboxes option as follows :

<form action="something.php" method="post">
<input type="checkbox" name="store[]" value="M1">
<input type="checkbox" name="store[]" value="M2">
<input type="submit" value="Go">
</form>

Now after submitting the form, I can view which store selected by doing foreach loop:

$allstore = $_POST['store'];
 foreach ($allstore as $store=>$value) {
echo $value;
}

Everything till now works as needed ! However those two values in checkboxes are considered to be table names ! Now how can I find a way to let PHP select either one or two tables based on user selection $value ?

$query = "SELECT * from **{$allstore[0]},{$allstore[1]}** WHERE .....";

As you can see {$allstore[0]},{$allstore[1]} should be created based under foreach loop. I can't seem to find a way of doing it! Can I insert a function to do it for me?

Like this : $query = "SELECT * from ((( Function Here ))) WHERE .....";

If you have a different way of doing it, Please share it.

Edit :

M1 Table

id |item_no |qty |price
1   x1       10   20
2   x2       5    22
3   x3       3    5

M2 Table

id |item_no |qty |price
1   x1      11    20
2   x9      5     30
3   x10     6     26

The output table should be

item_no | price |   M1 |  M2
x1         20       10    11
x2         22       5     N/A
x3         5        3     N/A
x9         30       N/A   5
x10        26       N/A   6

That's what I am aiming for. I hope it can be solved !

What should the output be if the user doesn't select all the tables? What goes in the `price` column if there's a different price in the two tables? What you want looks like `FULL OUTER JOIN`, which MySQL doesn't have; see http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql for a workaround. — Barmar, Mar 22 '16 at 17:58
Well, I can assure that the prices are fixed no matter what. Secondly , the user can not submit the form unless 1 checkbox is selected. — Ali Hamra, Mar 22 '16 at 18:00
I meant what should the result be if they only select one table, not both. What goes in the M2 column if they only select M1? — Barmar, Mar 22 '16 at 18:01
If the prices are the same in all tables, this is bad normalization, there should be another table that contains the prices of the items. — Barmar, Mar 22 '16 at 18:01
Can there be more than 2 tables? Do you need a solution that works with any number of tables, or just 2? — Barmar, Mar 22 '16 at 18:02
I see what you mean, it will only show M1 column only, because I've made both store names as Arrays! ... And when I explode this array it will create accordingly. Its works already for me right now. — Ali Hamra, Mar 22 '16 at 18:03
See the answers [here](http://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join) for how to emulate full outer join of more than 2 tables. You'll then need to write code to generate that dyncamically from the list of tables in `$_POST`. — Barmar, Mar 22 '16 at 18:11

Tin Tran · Answer 1 · 2016-03-22T22:17:10.027

1

here's the structure for 2 tables sqlfiddle I think you can add more tables from here.

SELECT T1.item_no,
  COALESCE(M1.price,M2.price) as price,
  M1.qty as M1,
  M2.qty as M2
FROM
  (SELECT item_no FROM M1
    UNION
   SELECT item_no FROM M2
   )T1
   LEFT JOIN M1 ON T1.item_no = M1.item_no
   LEFT JOIN M2 ON T1.item_no = M2.item_no

UPDATED: I am not too familiar with PHP but I looked up some syntax and was able to dynamically generate SQL based on array of either ["M1","M2"] or ["M1"] or ["M2"]

DynamicPHPtobuildSQL

<?php
        //Enter your code here, enjoy!

$allstore = ["M2"];
$item = 0;
$sqlpart1 = "";
$sqlpart2 = "";
$sqlpart3 = "";
$sqlpart4 = "";
foreach ($allstore as $store=>$value) {
   $item += 1;
   if ($item > 1){
     $sqlpart1 .= ",";
     $sqlpart2 .= ",";
     $sqlpart3 .= " UNION ";
   }
   $sqlpart1 .= $value . ".price ";
   $sqlpart2 .= $value . ".qty as " . $value . " ";
   $sqlpart3 .= "SELECT item_no FROM " . $value . " ";
   $sqlpart4 .= "LEFT JOIN " . $value . " ON T1.item_no=" . $value . ".item_no ";
}
$SQL = "SELECT T1.item_no,COALESCE(" . $sqlpart1 . ") as price," . $sqlpart2;
$SQL .= "FROM (" . $sqlpart3 . ")T1 " . $sqlpart4;
echo $SQL;
?>

edited Mar 22 '16 at 22:17

answered Mar 22 '16 at 19:42

Tin Tran

6,194
3
19
34

Thanks for your answer, but this would come very handy if I know how to let user select which table he wants to see. I mean you have already assigned M1 and M2 without noticing that it needs to be checked from checkbox . In which it might be M1 only, Or M2 only ..Or Both. I've totally lost hope finding proper solution. – Ali Hamra Mar 22 '16 at 21:13
you can dynamically build up the SQL based on which ones are selected in PHP I think.. like the M1.price,M2.price can be built up... M1.qty as M1,M2.qty as M2 can be built up... SELECT item_no FROM M1 UNION SELECT item_no FROM M2 can be built up... and same with LEFT JOIN M1 ON T1.item_no = M1.item_no LEFT JOIN M2 ON T1.item_no can be built up....based on what user selects you can just build up each pieces and concat the pieces together to build your final query – Tin Tran Mar 22 '16 at 21:19
I have updated my answer with PHP code to give you idea on how you might dynamically generate SQL hope that helps – Tin Tran Mar 22 '16 at 22:06

score 0 · Answer 2 · answered Mar 23 '16 at 10:30

Be careful to avoid the risk of SQL injection: compare the posted values against a closed list of existing store table names and reject any other value.

Note also that not only the FROM clause is influenced by the user's choices, but also the SELECT clause. So you have two dynamic parts in your SQL statement.

You could use this code which makes use of array_intersect, implode and array_map:

$selected_stores = $_POST['store'];

// Protect against SQL-injection by only accepting known tables:
$all_stores = array("M1", "M2", "M3");
$selected_stores = array_intersect($selected_stores, $all_stores);

// Build dynamic part of the FROM clause
$from = implode("
           UNION
           ", array_map(function ($store) { 
    return "SELECT '$store' as store, item_no, price, qty FROM $store";
}, $selected_stores));

// Build dynamic part of the SELECT clause
$cols = implode(",
     ", array_map(function ($store) { 
    return "CASE store WHEN '$store' THEN qty END AS $store";
}, $selected_stores));

$sql = "
SELECT   item_no,
         MAX(price) as price,
         $cols
FROM     ( $from ) data
GROUP BY item_no
";

The SQL generated looks like this:

SELECT   item_no,
         MAX(price) as price,
         CASE store WHEN 'M1' THEN qty END AS M1,
         CASE store WHEN 'M2' THEN qty END AS M2
FROM     ( SELECT 'M1' as store, item_no, price, qty FROM M1
           UNION
           SELECT 'M2' as store, item_no, price, qty FROM M2 ) data
GROUP BY item_no

PHP select multiple Array tables

2 Answers2