Create one age with on-sale products

[mozack]

Hi,

No problem . I found the simple solution but i'm missing something here. I create a new dislpay file, new page layout and new Panel with some name of display file. Now it shoes the product but i'm missing something, a problem with mysql query.

please see it:

Code: Select all

		$query = "
			SELECT p.*, FLOOR(prodratingtotal/prodnumratings) AS prodavgrating, imageisthumb, imagefile, ".GetProdCustomerGroupPriceSQL().",
			(SELECT COUNT(reviewid) FROM [|PREFIX|]reviews WHERE revstatus='1' AND revproductid=p.productid AND revstatus='1') AS numreviews,
			(SELECT brandname FROM [|PREFIX|]brands WHERE brandid=p.prodbrandid) AS prodbrandname,
			(SELECT COUNT(imageid) FROM [|PREFIX|]product_images WHERE imageprodid=p.productid AND imageisthumb=0) AS numimages
			FROM [|PREFIX|]products p
			LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid)
			WHERE p.prodvisible='1' AND prodprice < prodretailprice AND imageisthumb=0
			ORDER BY imagesort ASC
		";

Here i want to pass products where vcstock in [|PREFIX|]product_variation_combinatios is more than 1. How can i add this sql to code????

I want to integrate something like this:

Code: Select all

SELECT vcoptionids	FROM [|PREFIX|]product_variation_combinations
	WHERE vcenabled = 1 AND 	vcstock > 0 AND vcproductid =p.productid

in the big code before this... Can you help me?

Thanks

[Tony Barnes]

Reads ok to me, just bob in a couple of ' 's - tested as a line in mysql browser and seems to work nicely

Code: Select all

SELECT vcoptionids  FROM [|PREFIX|]product_variation_combinations WHERE vcenabled='1' AND vcstock>'0' AND vcproductid =p.productid

Stick it anywhere in the top

[mozack]

Hi, thanks for your answer...

Here is my point, I have one query:

Code: Select all

$query = "
         SELECT p.*, FLOOR(prodratingtotal/prodnumratings) AS prodavgrating, imageisthumb, imagefile, ".GetProdCustomerGroupPriceSQL().",
         (SELECT COUNT(reviewid) FROM [|PREFIX|]reviews WHERE revstatus='1' AND revproductid=p.productid AND revstatus='1') AS numreviews,
         (SELECT brandname FROM [|PREFIX|]brands WHERE brandid=p.prodbrandid) AS prodbrandname,
         (SELECT COUNT(imageid) FROM [|PREFIX|]product_images WHERE imageprodid=p.productid AND imageisthumb=0) AS numimages
         FROM [|PREFIX|]products p
         LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid)
         WHERE p.prodvisible='1' AND prodprice < prodretailprice AND imageisthumb=0
         ORDER BY imagesort ASC
      ";

Now, i want to put in query this code to array the products with all this statements:

Code: Select all

SELECT vcoptionids  FROM [|PREFIX|]product_variation_combinations WHERE vcenabled='1' AND vcstock>'0' AND vcproductid =p.productid

Can you tell me how to put all the code together in one single query?

Thanks

Mozack

[Tony Barnes]

Code: Select all

    $query = "
             SELECT vcoptionids  FROM [|PREFIX|]product_variation_combinations WHERE vcenabled='1' AND vcstock>'0' AND vcproductid =p.productid,
             SELECT p.*, FLOOR(prodratingtotal/prodnumratings) AS prodavgrating, imageisthumb, imagefile, ".GetProdCustomerGroupPriceSQL().",
             (SELECT COUNT(reviewid) FROM [|PREFIX|]reviews WHERE revstatus='1' AND revproductid=p.productid AND revstatus='1') AS numreviews,
             (SELECT brandname FROM [|PREFIX|]brands WHERE brandid=p.prodbrandid) AS prodbrandname,
             (SELECT COUNT(imageid) FROM [|PREFIX|]product_images WHERE imageprodid=p.productid AND imageisthumb=0) AS numimages
             FROM [|PREFIX|]products p
             LEFT JOIN [|PREFIX|]product_images pi ON (p.productid=pi.imageprodid)
             WHERE p.prodvisible='1' AND prodprice < prodretailprice AND imageisthumb=0
             ORDER BY imagesort ASC
          ";

Can't you just stick it in wherever, and stick a comma at the end like above?? lol, SQL isn't my hot topic!

SELECT vcoptionids FROM [|PREFIX|]product_variation_combinations WHERE vcenabled='1' AND vcstock>'0' AND vcproductid =p.productid

[mozack]

Hi,

Well it doesnt work like that

But no problem i use the podcurrentinv instead of vcstock, by this way i dont need to extract data from different table...

Well i think i figure it no has i want, but has i can

Regards