Trying To Merge Two Queries Properly

PHP
1

Hey,

I’m trying to get 2 queries to act as one but I am not sure how to go about it. Below I explain why and what I am working with. I hope this helps. Ty for your time.

Purpose:

The reason I need to combine 2 queries is because I have to list products to the public based on ‘best sellers’ over the past 2 months (which I can do, just fine, with one query - listed as Query 1 below) but I also have to add products, with ZERO sales, added by the admin, to that SAME list and page through ALL of those records (Query 2 listed below Query 1). That’s my dilemma…

Tables Involved: products, sales

Fields Involved (most important ones)

  • For Table Sales: prod_id,stamp (date of purchase)

  • For Table Product: ID, total_sales,marketplace_ready

Query 1: (Which works fine by itself - I am using ‘…’ to condense it/hide redundant fields)

SELECT DISTINCT prod_id, count( prod_id ) AS sales... FROM sales JOIN products ON ( products.ID = sales.prod_id ) where products.ID > '0' AND stamp >1268798400 GROUP BY title ORDER BY stamp DESC

Works great and displays 70 records now. This is how I can list top sellers of the last 2 months (the stamp is one that was properly dated a few moments ago) without fail. Below is the query used to pull products added by the admin to allow permission to be viewed in the marketplace even if they have 0 sales.

Query 2:

Table: products (same fields that are listed above are in play)

SELECT * FROM products where products.ID > '0'     AND total_sales = '0' AND marketplace_ready = 'yes'  group by title ORDER BY ID ASC

Total Records: 23 (as of this posting)

GOAL:

The idea is to just combine BOTH queries, into ONE, so I can get all 93 results (70 from Query 1 and 23 from Query 2) so I can use PAGING properly.

Do I use a regular join? Union statement?

Ty.

2

yes, a UNION query

but be careful of using GROUP BY – in your first query, you have GROUP BY on a column that isn’t in the SELECT clause, and in your second query it sure doesn’t look like you need GROUP BY at all

3

Yeah, the first group by was handy before I had to add the records of the 2nd query but I will re-adjust it. Same for the 2nd.

One quick q…how do I use union with these 2 if they don’t share equal # of fields in the queries? Or is the idea that I must find a way to do that? One query uses a join but the other does not need one… I received errors when I try. I hope it’s ok to ask, it’d solve a major headache. I use mysql 5+.

Thanks for your time.

PS. The reason query 1 must use a join (or so I thought) is because it checks if the product in product table was listed in the sale table, since we are listing top sellers. The 2nd query doesn’t need the join so that’s why my confusion using an ‘union query’ comes in. Ty, again.

4

by using NULLs as placeholders in the shorter SELECT clause

:slight_smile:

5

Interesting. With that said, I wonder if I am going about this wrong.
Maybe I should combine the arrays of $row, in 'while($row=mysql_fetch_array)
by adding the 2nd query records last and looping through them using the paging class I have.

Thoughts? Otherwise, I will use your suggestion and see how that works. I just wonder if my idea of combining both queries is poor and I am leading us on a goose chase.

6

if you would be kind enough to show me the actual queries, without the dreaded, evil “select star” you had in the second one, and with whatever adjustments you’ve made to the GROUP BY clause(s), i’d be happy to show you the UNION query

7

Ok, will do…

8

Here you go…

Query #1: Using tables products & sales

SELECT DISTINCT prod_id, count( prod_id ) AS sales, title, stamp, total_sales,description, products.price, comm, sales_page, marketplace, deactivated,marketplace_ready FROM sales JOIN products ON (products.ID = sales.prod_id ) where products.ID > ‘0’ AND stamp >1268798400 GROUP BY title ORDER BY total_sales DESC

  • I now group by total sales. The idea is I am showing MOST sales within 8 week period.
    The 2nd query is meant to post results after all of the query 1 results load.

By combining both, I HOPE I can use paging from now one (10 results per page/etc).

This gives me the 73 rows of all products with at least 1 sale, ordered by date. I will change this to be ordered by products.total_sales shortly

Query #2: Using only products since there is no point in referencing sales table as these represent products
that the admin added himself to the marketplace. Otherwise, a product MUST have 1 sale to be visible in the marketplace (hence, we used query #1 by itself initially until this new request popped up to show the following other products).

SELECT DISTINCT prod_id, count( prod_id ) AS sales, title, stamp, description, products.price, comm, sales_page, marketplace, deactivated,marketplace_ready,total_sales FROM products where products.ID > ‘0’ AND total_sales = ‘0’ AND marketplace_ready = ‘yes’

NOTE: I really did use * in the 2nd query but I replaced it, for your convenience, with the fields I need.

Basically the issue is I must display, from products:

prod_id, sales_page, marketplace_ready,title,description,stamp

but I figure it might be best if I combine them in a way where we group by title and order by most sales (Query 1 counts them by using count(prod_id) in sales table but we now have a field that is updated, each sale, in products called total_sales)

So I am not sure what is the best solution. I added that as the goal is to not have to depend on the sales table other than getting the sales timestamp from the sales table. That is key because the client wants to show all sales (highest total to lowest) within the timeframe of $stamp (56 days) and after THOSE results, anything that is past 8 weeks or newly added by the admin.

Fun, huh? If I am missing anything, please feel free to blow a raspberry at me.

9

I’ll be back shortly so I apologize if I am missing anything that is of use for you. I don’t want to be a time drain…

10

i’m having a ~real~ hard time distinguishing which table the columns are coming from

your explanation says that you want the stamp from the sales table, but i see it in the SELECT clause of the second query which accesses only the products table

could you do a SHOW CREATE TABLE for both tables please?

11

Sure.

Table: Products

CREATE TABLE IF NOT EXISTS products (
ID int(11) NOT NULL auto_increment,
member_id int(11) NOT NULL,
title varchar(48) collate utf8_unicode_ci NOT NULL,
price decimal(11,2) NOT NULL,
description varchar(255) collate utf8_unicode_ci NOT NULL,
rebill_price decimal(11,2) NOT NULL,
rebill_frequency varchar(9) collate utf8_unicode_ci NOT NULL,
rebill_duration int(3) NOT NULL,
rebill_total int(3) NOT NULL,
trial_price decimal(11,2) NOT NULL,
trial_period int(2) NOT NULL,
dimesale varchar(1) collate utf8_unicode_ci NOT NULL,
dimesale_rise decimal(11,2) NOT NULL,
dimesale_finalprice decimal(11,2) NOT NULL,
comm int(3) NOT NULL,
sales_page varchar(128) collate utf8_unicode_ci NOT NULL,
ty_page varchar(128) collate utf8_unicode_ci NOT NULL,
category varchar(40) collate utf8_unicode_ci NOT NULL,
subcategory varchar(40) collate utf8_unicode_ci NOT NULL,
marketplace varchar(3) collate utf8_unicode_ci NOT NULL,
recurring varchar(3) collate utf8_unicode_ci NOT NULL,
deactivated varchar(3) collate utf8_unicode_ci NOT NULL,
total_sales int(7) NOT NULL,
admin_activate varchar(3) collate utf8_unicode_ci NOT NULL,
marketplace_ready varchar(3) collate utf8_unicode_ci NOT NULL,
last_sale_date varchar(10) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (ID),
KEY total_sales (total_sales),
KEY member_id (member_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=482 ;

Table Sales:

CREATE TABLE IF NOT EXISTS sales (
id int(11) NOT NULL auto_increment,
sess_id varchar(64) collate utf8_unicode_ci NOT NULL,
first varchar(64) collate utf8_unicode_ci NOT NULL,
last varchar(64) collate utf8_unicode_ci NOT NULL,
paypal_email varchar(68) collate utf8_unicode_ci NOT NULL,
receiver_email varchar(64) collate utf8_unicode_ci NOT NULL,
stamp timestamp NULL default CURRENT_TIMESTAMP,
txn_id varchar(255) collate utf8_unicode_ci NOT NULL default ‘0’,
product varchar(255) collate utf8_unicode_ci NOT NULL default ‘’,
prod_id int(11) NOT NULL,
price decimal(11,2) NOT NULL,
ip varchar(20) collate utf8_unicode_ci NOT NULL default ‘’,
member_id int(11) NOT NULL,
affiliate_id int(11) NOT NULL,
status varchar(100) collate utf8_unicode_ci NOT NULL,
oto varchar(3) collate utf8_unicode_ci NOT NULL,
fee decimal(11,2) NOT NULL,
profit decimal(11,2) NOT NULL,
refund int(1) NOT NULL,
fee_paid varchar(3) collate utf8_unicode_ci NOT NULL,
payment_type varchar(7) collate utf8_unicode_ci NOT NULL,
PRIMARY KEY (id),
KEY paypal_email (paypal_email)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=441 ;

12

And you damn well better believe I’m buying your sitepoint book…

13 
SELECT products.ID
     , s.sales
     , products.title
     , products.total_sales
     , products.description
     , products.price
     , products.comm
     , products.sales_page
     , products.marketplace
     , products.deactivated
     , products.marketplace_ready 
  FROM products
INNER
  JOIN ( SELECT prod_id
              , COUNT(*) AS sales
           FROM sales
          WHERE stamp > 1268798400 
         GROUP
             BY prod_id ) AS s
    ON s.prod_id = products.ID
 [COLOR="Blue"]WHERE products.description LIKE '%test%' 
   AND products.comm = '50%'[/COLOR]
UNION ALL
SELECT ID
     , NULL AS sales -- this is a placeholder
     , title
     , total_sales
     , description
     , price
     , comm
     , sales_page
     , marketplace
     , deactivated
     , marketplace_ready 
  FROM products
 where ID > 0 
   AND total_sales = 0 
   AND marketplace_ready = 'yes' 
ORDER 
    BY total_sales DESC

any questions, just ask :slight_smile:

14

Wow, you’re good. I guess all I have to do is count the # of rows this produces (93 for now) and I will have the $total variable set for my paging class. I will update you once it’s complete and look forward to taking the next step in MYSQL learning thanks to you.

Wow…just a huge help. I’ve had a rough go of it recently and this means so much more to me than you can understand. Ty so much.

15

Oh, wait. I do have a last question. The original Query #1 I showed was if nobody performed a search. If they did, using keyword=‘test’ and selecting a product with commission=‘50%’, the query would dynamically turn into this:

SELECT DISTINCT prod_id, count( prod_id ) AS sales, title, description, products.price, comm, sales_page, marketplace, deactivated,marketplace_ready FROM sales JOIN products ON ( products.ID = sales.prod_id ) where description like ‘%test%’ AND comm=‘50%’ AND stamp >1268798400 GROUP BY title ORDER BY sales DESC

The bold is the dynamic ‘extra part’ of the query based on user searches.

Using your NEW query…would I place that dynamic text in the first part of the union, before the inner join, as well as before the group by part of the 2nd query part? Where should that go? I just need to know where to place it so I don’t muck this masterpiece query up :wink:

I just know that the 2 joined queries need to look for products, in this scenario, where description has ‘test’ in the text and comm=‘50’. That’s all. I can use the pattern, once I know where to plop it, with the other dynamic parts of the query that come from the other search fields.
Thanks.

16

i’ve added it to the first SELECT in the query (in blue)

if you also want it in the second SELECT, stick it in justbefore the ORDER BY clause

17

Will apply it and update this thread. Thanks, again. Big time.