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 stackoverflow.com/questions/4796872/full-outer-join-in-mysql for a workaround. — Barmar
– Barmar, Commented Mar 22, 2016 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
– Ali Hamra, Commented Mar 22, 2016 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
– Barmar, Commented Mar 22, 2016 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
– Barmar, Commented Mar 22, 2016 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
– Barmar, Commented Mar 22, 2016 at 18:02

Tin Tran · Accepted Answer · 2016-03-22 22:17:10Z

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, 2016 at 22:17

answered Mar 22, 2016 at 19:42

Tin Tran

6,1763 gold badges25 silver badges34 bronze badges

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

3 Comments

Ali Hamra Over a year ago

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.

Tin Tran Over a year ago

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 Over a year ago

I have updated my answer with PHP code to give you idea on how you might dynamically generate SQL hope that helps

trincot · Accepted Answer · 2016-03-23 10:30:02Z

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

Collectives™ on Stack Overflow

PHP select multiple Array tables

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related