CODEWITHSUNDEEP
phpmysqlcheckboxforeach
Ali Hamra
Ali Hamra

Reputation: 232

PHP Output multiple tables and its values in one table

I need to show you step by step what I've done so far and what the output I am looking for to happen.

First of all I have a table called stores as follows :

id | store_name
1    S1
2    S2
3    S3
4    S4

And EACH store_name has its own table that contains item number, qty, cost, sell price and date - One of them as follows :

Table Name : S1
id | item_no | qty | cost | sell | date
1      b1001   10     6      12     2015-3-31
2      b1002   12     6      12     2015-3-31
3      b1003   6      3      6      2015-3-30
4      b1004   4      5      10     2015-3-30

And so on for each store_name table.

What I'm aiming for is that I need an output to compare each Item number how many qty for each store and list them next to each other as follows :

item_no | cost | sell | S1 | S2 | S3 | S4
b1001     10     12    10     9    8     N/A
b1002     6      12    6      3    N/A   N/A
b1003     3      6     6      6    N/A   12
b1004     5      10    4      N/A  10    10

Please note, the above stores are chosen up by the user request (it could be one store name, or could be all of them) as follows:

<form method="POST" action="Somewhere.php">
//Looping To show how many stores
<input type=checkbox name=store[] value={$row['stores_name']} style='width:20px; height:20px;'>{$row['stores_name']}
//End of Looping
</form>

After submitting to Somewhere.php , we have to find out which store selected by doing foreach loop:

$allstore = $_POST['store'];

   foreach ($allstore as $store=>$value) {

//Don't Know what code should be here

}

I've tried doing my own way, but it lists store_name tables on top of each others, by which its not what I aimed for. Is there a way to do it ? Any other suggestions?

Please Note: COST and SELL prices are the same for all stores!

Upvotes: 1

Views: 54

Answers (1)

alexander.polomodov
alexander.polomodov

Reputation: 5534

You can use INNER JOIN to select data from table.

For example use this function createSelect and pass to it array with keys from your tablename, i.e $allstore1 = ['S1' => 1, 'S2' => 2, 'S3' => 3]:

<?php
function createSelect($allstore)
{
    if (empty($allstore))
        return "";

    $querySelect = "";
    $queryJoin = "";
    $baseTable = "";
    foreach ($allstore as $store => $value) {
        if (!$querySelect) {
            $baseTable = $store;
            $querySelect = "SELECT " . $store . ".item_no, " . $store . ".cost, " . $store . ".sell, " . $store . ".qty as " . $store;
        } else {
            $querySelect .= ", " . $store . ".qty as " . $store;
            $queryJoin .= "
             INNER JOIN " . $store . " ON " . $baseTable . ".item_no = " . $store . ".item_no";
        }
    }
    $querySelect .= " FROM " . $baseTable;
    $query = $querySelect . $queryJoin;

    return $query;
}

$allstore1 = ['S1' => 1, 'S2' => 2, 'S3' => 3];
$allstore2 = ['S1' => 1];
$allstore3 = [];

var_dump(createSelect($allstore1), createSelect($allstore2), createSelect($allstore3));

And output will be:

string(201) "SELECT S1.item_no, S1.cost, S1.sell, S1.qty as S1, S2.qty as S2, S3.qty as S3 FROM S1
             INNER JOIN S2 ON S1.item_no = S2.item_no
             INNER JOIN S3 ON S1.item_no = S3.item_no"
string(57) "SELECT S1.item_no, S1.cost, S1.sell, S1.qty as S1 FROM S1"
string(0) ""

Upvotes: 1

Related Questions