Jay Ghosh Jay Ghosh - 3 months ago 7
SQL Question

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 retieved from table vendor_1 since id=1
| 2 | 158| 85| //price and quantity retieved from table vendor_2 since id=2
| 3 | 15| 7| //price and quantity retieved from table vendor_3 since id=3
| 4 | 112| 9| //price and quantity retieved from table vendor_4 since id=4
| 5 | 123| 199| //price and quantity retieved 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

Answer

-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.