[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help customizing two SQL queries

Posted on 2014-08-24
5
Medium Priority
?
171 Views
Last Modified: 2014-08-25
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
0
Comment
Question by:billium99
  • 2
  • 2
5 Comments
 
LVL 3

Assisted Solution

by:prequel_server
prequel_server earned 600 total points
ID: 40282365
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1400 total points
ID: 40282389
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40282399
@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
 
LVL 3

Expert Comment

by:prequel_server
ID: 40282414
@PortletPaul
cool thanks
0
 
LVL 1

Author Closing Comment

by:billium99
ID: 40283634
Thanks for the help guys. It was indeed misplaced parentheses that was my undoing. The query is now working.

Thanks again!

Bill
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month20 days, 4 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question