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


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/public_html/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
13 views
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? I would like to combine the 2 queries together...

Thanks in advance for the help
by | 13 views

1 Answer

0 like 0 dislike
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
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
4 answers
0 like 0 dislike
1 answer
110,608 questions
257,186 answers
0 comments
36,635 users