Entries Tagged 'cpCommerce' ↓

Day after day I am surrounded in queries. Whether it be writing them, analyzing them, or just sorting through them as I look at code, I am introduced to queries normally every hour. This day was no different. Today, I had to write a query that took a lot of thought. I had to write a query that would find the minimum price (between a sale price and an original price), determine whether or not the product was in stock (or any of its subproducts were in stock), and sort it by 6 possible ways which was to be determined dynamically.

The query I ended up producing was:

  $sql['products']   = "select DISTINCT(A.name), A.id_product, A.thumbnail, IF (D.price <> 0, D.price, " .
                       "IF (MIN(C.price) <> 0, MIN(C.price), A.price)) as `price`, A.subproducts, " .
                       "IF (MIN(C.stock) <> 0, IF (MIN(C.stock) < 0, 1000, C.stock), IF (A.stock = -1, " .
                       "1000, A.stock)) as `stock`, A.description from `{$config['prefix']}Products` as " .
                       "A left join `{$config['prefix']}LinkedProducts` as B on (A.id_product=B.id_product) " .
                       "left join `{$config['prefix']}SubProducts` as C on (A.id_product=C.id_product) " .
                       "left join `{$config['prefix']}Sales` as D on (A.id_product=D.id_product and " .
                       "C.id_subproduct=D.id_subproduct and D.start >= NOW() and D.last < = NOW()) " .
                       "where A.id_category='{$_GET['id_category']}' or B.id_category='{$_GET['id_category']}' " .
                       "GROUP BY A.name order by `{$orderby}` {$direction} limit {$start}, " .
                       "{$config['pageProducts']}";

As you can tell, this is no ordinary query. It is a complex one that makes use of several joins and even if functions. An amatuer in this situation would be likely to split up that single query into 3-4 separate queries and several if statements in PHP. How many of you would have done it in a single query?

As you all are aware, when using cpCommerce you have the ability to attach zip files to the products so the user can download the item upon completion of the purchase. In the old method, there were several ways on which a person could find the downloads directory and then have access to all of the files you have uploaded, this is now changed.

Now, it should be cleared up that this method does not stop them from “finding” the download directory by sniffing the header content in the browser, but rather the whole idea is placing the download directory in a spot not accessible by Port 80. To do this, you will need to have a web host who gives you SSH access (though FTP access may be sufficient too).

On most web servers you have a directory called ‘www’ or ‘public_html’. These directories tell the web server they are do be allowed for viewing on Port 80. Now if you create a directory called ‘downloads’ outside of these folders, they are inaccessible from Port 80 and thus anyone with an Internet connection cannot get to that directory by surfing your site (Please realize that if they had FTP access or SSH access, they could still get to it!).

Once you make that directory and chmod it to 0777, you can move all of your current download files from the public directory to the “private” directory. Finally, you will need to update your Configuration area, to tell it where the files are located. For the most part, it should be self-described, other than the fact that by default it will point to: /path to public or www directory/store directory/images/products/downloads/. You will want it to be: /path to your downloads directory/. An example of this path would be: /home/cpradio/downloads/

Now that you have it all configured, your download files are safe from the computer literate.