Help customizing two SQL queries

HI,

I am trying to modify a couple of SQL queries on the download.php page of OpenCart. Currently the queries extract all download files a person has ordered and display remaining downloads, etc. It's based on orders getting to a certain status.

But free checkout never gets an order to the needed status and now we are trying to offer a free download. Unfortunately it never shows up in the list of downloads currently.

So these two queries:

$query = $this->db->query("SELECT o.order_id, o.date_added, od.order_download_id, od.name, od.filename, od.remaining FROM " . DB_PREFIX . "order_download od LEFT JOIN `" . DB_PREFIX . "order` o ON (od.order_id = o.order_id) WHERE o.customer_id = '" . (int)$this->customer->getId() . "' AND o.order_status_id > '0' AND o.order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND od.remaining > 0 ORDER BY o.date_added DESC LIMIT " . (int)$start . "," . (int)$limit);

Open in new window


and

	$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "order_download od LEFT JOIN `" . DB_PREFIX . "order` o ON (od.order_id = o.order_id) WHERE o.customer_id = '" . (int)$this->customer->getId() . "' AND o.order_status_id > '0' AND o.order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND od.remaining > 0");

Open in new window


If I wanted to have an OR clause in the WHERE statement to also check and display any orders with order.total=0.000 how would I incorporate that into these queries? No syntax I've tried is working. Here is my current attempt:

$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_download od LEFT JOIN `" . DB_PREFIX . "order` o ON (od.order_id = o.order_id) WHERE o.customer_id = '" . (int)$this->customer->getId(). "' AND o.order_status_id > '0' AND o.order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND od.order_download_id = '" . (int)$order_download_id . "' AND od.remaining > 0");

Open in new window


and

$query = $this->db->query("SELECT o.order_id, o.date_added, o.total, od.order_download_id, od.name, od.filename, od.remaining FROM " . DB_PREFIX . "order_download od LEFT JOIN `" . DB_PREFIX . "order` o ON (od.order_id = o.order_id) WHERE o.customer_id = '" . (int)$this->customer->getId() . "' AND o.order_status_id > '0' AND o.order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "' AND od.remaining > 0 OR o.customer_id = '" . (int)$this->customer->getId() . "' and o.total = 0.000 AND od.remaining > 0 ORDER BY o.date_added DESC LIMIT " . (int)$start . "," . (int)$limit);

Open in new window


The second set of queries return nothing. What am I doing wrong here?

Thanks

Bill
LVL 1
billium99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

prequel_serverCommented:
It would be easier for us sql geeks to only see the raw sql script.  Strip away all the PHP and try running the sql script directly on MySQL to validate the query.

My first guess is that your problem has to do with not using brackets in a WHERE clause that has both AND and OR operators. Make sure you put brackets around the set of conditions you want evaluated separately.

WHERE (
o.customer_id = @ID AND o.order_status_id > '0' AND o.order_status_id = @status AND od.remaining > 0
)
OR
(
o.customer_id = @ID AND o.total = 0.000 AND od.remaining > 0
)
0
PortletPaulfreelancerCommented:
as far as I can tell, the first 2 queries are the same, and the third is only different due to select * instead of select count(*)

The fourth contains the OR, and as Majd Hussini has pointed out, you need to group the conditions together using parentheses.
SELECT
     o.order_id
   , o.date_added
   , o.total
   , od.order_download_id
   , od.name
   , od.filename
   , od.remaining
FROM " . DB_PREFIX . "order_download od
INNER JOIN `" . DB_PREFIX . "order` o ON od.order_id = o.order_id
WHERE (
            o.customer_id = '" . (int)$this->customer->getId() . "'
        AND o.order_status_id > '0'
        AND o.order_status_id = '" . (int)$this->config->get('config_complete_status_id') . "'
        AND od.remaining > 0
      )
OR (
        o.customer_id = '" . (int)$this->customer->getId() . "'
        AND o.total = 0.000
        AND od.remaining > 0
        )
ORDER BY o.date_added DESC LIMIT " . (int)$start . "
   ," . (int)$limit);

Open in new window

A couple of extra pointers. There is no point in using a LEFT JOIN here so I have replaced it. You gave the left joined table the alias o; now look how many times o is referenced in the where clause. If there are mandatory requirements on that table then a LEFT JOIN has no value to you (because a LEFT JOIN would allow some fields to be NULL; but you ignore those anyway)

I have also removed the redundant parentheses in the join condition, you do need them in your where clause now - but you never needed them in that join condition.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
@Majd

If one removes $query = $this->db->query("
from the php strings using a sql formatter will often work to make it more legible to us sql geeks,
e.g query 4 could look like this
0
prequel_serverCommented:
@PortletPaul
cool thanks
0
billium99Author Commented:
Thanks for the help guys. It was indeed misplaced parentheses that was my undoing. The query is now working.

Thanks again!

Bill
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.