How to combine 2 query to the database, written in PHP alone?

Hello and thank you for decided to try to help the dropout.

There are 3 tables (names are specified using constants):
1st (TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS) contains properties and id of the goods for which they are specified
+---------------+---------------+-----------------------------+----------------------------------+
|_____ID___ _ ____|_products_id_|_products_extra_fields_id|_products_extra_fields_value_|
+---------------+---------------+-----------------------------+----------------------------------+
|______1______ |______6 ______|_____________4____________|_____________Y________________|
|______2 ______|______45_____|_____________4____________| _____________ Y ______________ |

2nd (TABLE_PRODUCTS) abbreviated
+---------------+---------------+-----------------------------+----------------------------------+
|_____ID___ _ ____|_products_id_|_____products_image_____|____products_page_url_______|
+---------------+---------------+-----------------------------+----------------------------------+
|______1_______/______6_______/_____photo1.jpg__________/__________tovar1.html________|
|______2_______/______25______/_____photo2.jpg__________/__________tovar2.html________|

3rd (TABLE_PRODUCTS_DESCRIPTION) abbreviated
+---------------+---------------+--------------------+
|_____ID___ _ ____|_products_id_|_products_name_|
+---------------+---------------+--------------------+
|______1_______|______6_______|____Product 1______|
|______1_______|______25______|____Item 2_____|

The sample do the following:
from the 1st table choose 5 products id for which the property = 'Y'
Second choose the desired properties of the products of the 2 product tables.

How it looks in code:
$sql = "
SELECT
of products_id
 FROM " . TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS . "
WHERE
 products_extra_fields_id = '4'
 and products_extra_fields_value = 'Y'
 LIMIT 5";

$productsIds = "";

$query = vamDBquery($sql);

while ($one = vam_db_fetch_array($query,true)) {
 $productsIds .= $one['of products_id'].','; 
}

// Now choose the goods themselves

$productsIds = substr($productsIds, 0, -1);

$sqlProd = "
SELECT
p.of products_id,
p.products_image,
p.products_page_url,
p.products_price,
p.products_tax_class_id,
d.products_name
 FROM " . TABLE_PRODUCTS . "AS p," . TABLE_PRODUCTS_DESCRIPTION . "AS a d
WHERE
 p.of products_id IN (".$productsIds.")
 and p.of products_id=d.of products_id";

$queryProd = vamDBquery($sqlProd);

Then using fetch this is all going in one array and displayed correctly, i.e. it all works.
BUT!
"Spit" in the way that going id-shnik and generally from 2 queries to 1 action. Yes, actually, code, it seems to me, even crutches not be called...
How to optimize can? I would like to combine the 2 queries together...

Thanks in advance for the help
March 12th 20 at 08:03
1 answer
March 12th 20 at 08:05
Solution
select
t1.*,
t2.*,
t3.*
from
TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS t1
TABLE_PRODUCTS join t2 on t2.of products_id = t1.of products_id
TABLE_PRODUCTS_DESCRIPTION join t3 on t3.of products_id = t1.of products_id
where t1.products_extra_fields_id = '4'
and t1.products_extra_fields_value = 'Y'
limit 5
@kavon66 , thanks a lot!
As they say, all genius is simple.
Like just the basics I understand, but c JOIN while it is hard to understand without practice.

Already tried it, it worked perfectly, and even from the crutch with id-shkami get rid of :) - Frances67 commented on March 12th 20 at 08:08
@Frances67, Good Luck - kavon66 commented on March 12th 20 at 08:11
Now compare the time for which result.
It is desirable to compare when You have tables there will be at least 10 thousand records. - Katelynn.Bedn commented on March 12th 20 at 08:14
@Faith.Bedn, I about it wrote above, but the moonlight heckled...)))
on the other hand, is quite successful (in terms of profitability) can be stores with 100-200 items))) - Abby commented on March 12th 20 at 08:17

Find more questions by tags SQLMySQL