CODEWITHSUNDEEP
phpmysqlsql
Jay Ghosh
Jay Ghosh

Reputation: 682

Dynamically selecting tables in mySQL

I have a query in mySQL

SELECT id FROM admin_products;

which return a list of ids, like so

+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

And I was using PHP to dynamically generate tables like

vendor_1, vendor_2, vendor_3, vendor_4, vendor_5

Now I want to write a query to retrieve the price and quantity from the table id

For example

"ENTER QUERY HERE"

Should retrieve

+-----------------------------+
| id   | price   | quantity   |
+-----------------------------+
|    1 |       23|          13| // price and quantity retrieved from table vendor_1 since id=1
|    2 |      158|          85| // price and quantity retrieved from table vendor_2 since id=2
|    3 |       15|           7| // price and quantity retrieved from table vendor_3 since id=3
|    4 |      112|           9| // price and quantity retrieved from table vendor_4 since id=4
|    5 |      123|         199| // price and quantity retrieved from table vendor_5 since id=5
+-----------------------------+

What I'm doing now in PHP is:

$conn = mysqli_connect($server,$user,$pwd,$db);
$sql = "SELECT id FROM admin_products";
$res = mysqli_query($conn,$sql);
if(mysqli_num_rows($res)>0){
    while($row = mysqli_fetch_assoc($res)){
        $product = array();
        $innerSQL = "SELECT price,quantity FROM vendor_".$row['id'];
        $innerRes = mysqli_query($conn,$innerSQL);
        if(mysqli_num_rows($innerRes)>0){
            while($innerRow = mysqli_fetch_assoc($innerRes)){
                 array_push($product,$row['id']);
                 array_push($product,$innerRow['price']);
                 array_push($product,$innerRow['quantity']);
            }
        }
   }
}

But it takes two hits to the mySQL database. Can't it be reduced to one?

EDIT

I have later on realized that my database structure was incorrect and dynamically creating tables is a very bad idea and could spell disaster later on

Upvotes: 2

Views: 261

Answers (2)

Vasil Rashkov
Vasil Rashkov

Reputation: 1830

-Solution 1:

Note: This will only work if you have in your vendor_x tables id for the vendor id to match them with. (As Strawberry said, this is a terrible idea to dynamically generate tables).

After selecting the correct id you can do something like this:

  • connect to the MySql Server

Then you can create the table name and store it in a variable.

$tableName = 'vendor_' . $id;

I would suggest after that to have a check if the table exists with a simple query:

$sql = "SHOW TABLES LIKE '$tableName'";

If this returns empty result you can throw an exception that the table does not exist or handle it whatsoever way you would like.

After checking every table, to be sure it exists, you can create your query.

$joins = "";

$sql = "
    SELECT 
        v.id, 
        price, 
        quantity 
    FROM
        vendors AS v
";

foreach ($ids as $id) {
    $tableName = "vendor_" . $id;
    $tableAlias = "v".$id;

    $joins .= " LEFT JOIN " . $tableName . " AS ". $tableAlias ." 
                    ON (v.id = ". $tableAlias .".vendor_id) ";
}

$sql .= $joins;

Then execute the query.

-Solution 2:

Create only one table to manage your vendors. It should have a structure like this :

`id` // AI value
`vendor_id` // The id of the vendor to easily join it afterwards
`price`
`quantity`

You can name it something like vendor_product or whatsoever

And now you have only one simple query:

$sql = "
    SELECT
        v.id,
        vp.quantity,
        vp.price
    FROM
        vendors AS v
        LEFT JOIN vendor_product AS vp
            ON (vp.vendor_id = v.id)
";

EDIT for the comment about the structure:

You will need one table for the vendors, such so:

`vendor`:
    `id`, //AI value
    `username`,
    `password` // I suggest to you not to keep it in plain text.

`vendor_product` :
    `id`, //AI value
    `vendor_id`,
    `price`,
    `quantity`

I don't know here if you are going to store more information about each product, but this should do the trick.

How to show the product with least price ? You need to match them by somehow and group by that selecting minimum price.

Upvotes: 1

Passionate Coder
Passionate Coder

Reputation: 7294

Try this if it suits

$table = "vendor"."_".$id; // this will create table name if $id = 1 then $table = vendor_1;
mysqli_query($connect , "SELECT * FROM $table");

2nd

If you want to fetch data of all table at once then

1) fetch id from admin_products and store in an array like

$ids = array(1,2,3,4,5);

2) Now loop throw array and create sql;

$sql = "SELECT * FROM "; 
$ids = array(1,2,3,4,5);
foreach($ids as $id){
$table = "vendor"."_".$id; // this will create table name if $id = 1 then $table = vendor_1;
$sql .=" $table,";

}
$sql = rtrim($sql,",");// this will trim the last comma
echo $sql;

// output SELECT * FROM vendor_1, vendor_2, vendor_3, vendor_4, vendor_5

Upvotes: 1

Related Questions