Downloads Contoller error!

September 25, 2003

Hi All,

I need the functionality of the Downloads Controller contribution so I'm really trying hard to get it working correctly! I have osCommerce 2.2 Milestone 2 and it is a completely fresh and unmodified install. FYI, I have no other mods installed.

I did the install for the Downloads Controller trying it two different ways - first I tried editing in all the changes - everything worked fine in OSC except I get the following error ONLY when going to Orders in Admin:

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit -20, 20' at line 1

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by limit -20, 20

[TEP STOP]

Then I tried installing it by replacing all changed files - all file versions match exactly so I know I'm using the correct Download Controller version for my install. Still I get the same error trying to access Orders in Admin.

Can anyone help with this error? I was thinking it's a mySQL error and not an osCommerce, php, or Downloads Controller error. Oh, I did insert the new fields for the downloads controller with the sql file provided, so I don't think that's the problem.

Please help if you can,

Jay2k

massilia · September 26, 2003

I have the same problem. :blink:

1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 0, 20' at line 1

select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total' order by limit 0, 20

[TEP STOP]

What's wrong? :(

RobAnderson · October 6, 2003

Hi there, faced the same problem and managed to nut it out. Basically there was some missing sort fields (atleast from my version).

Please find below the code I used to sort it out. Use at your own risk ;-) I know it looks daunting, but just take your time...

NOTE: I have taken the opportunity to make some other minor modifications, e.g. removed preview image (because you can click on the row instead), added in the Order ID column, changed some of the sort options and basically rearranged how the sort buttons were arranged.

Hope it works for you - IT IS GREAT MOD and worth perservering with! (tx Linda ;-)

In /admin/orders.php

About line 20 Replace

 $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . (int)$languages_id . "'");

with

 $orders_status_query = tep_db_query("select orders_status_id, orders_status_name from " . TABLE_ORDERS_STATUS . " where language_id = '" . (int)$languages_id . "' order by orders_status_id");


About line 387 replace

             <tr class="dataTableHeadingRow">
               <td class="dataTableHeadingContent"><a href="<?php echo "$PHP_SELF?listing=customers"; ?>"><?php echo tep_image_button('ic_up.gif', ' Sort ' . TABLE_HEADING_CUSTOMERS . ' --> A-B-C From Top '); ?></a> <a href="<?php echo "$PHP_SELF?listing=customers-desc"; ?>"><?php echo tep_image_button('ic_down.gif', ' Sort ' . TABLE_HEADING_CUSTOMERS . ' --> Z-X-Y From Top '); ?></a><br><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
               <td class="dataTableHeadingContent"><a href="<?php echo "$PHP_SELF?listing=ottotal"; ?>"><?php echo tep_image_button('ic_up.gif', ' Sort ' . TABLE_HEADING_ORDER_TOTAL . ' --> 1-2-3 From Top '); ?></a> <a href="<?php echo "$PHP_SELF?listing=ottotal-desc"; ?>"><?php echo tep_image_button('ic_down.gif', ' Sort ' . TABLE_HEADING_ORDER_TOTAL . ' --> 3-2-1 From Top '); ?></a><br><?php echo TABLE_HEADING_ORDER_TOTAL; ?></td>
               <td class="dataTableHeadingContent" align="right"><a href="<?php echo "$PHP_SELF?listing=id-asc"; ?>"><?php echo tep_image_button('ic_up.gif', ' Sort ' . TABLE_HEADING_DATE_PURCHASED . ' --> 1-2-3 From Top '); ?></a> <a href="<?php echo "$PHP_SELF?listing=id-desc"; ?>"><?php echo tep_image_button('ic_down.gif', ' Sort ' . TABLE_HEADING_DATE_PURCHASED . ' --> 3-2-1 From Top '); ?></a><br><?php echo TABLE_HEADING_DATE_PURCHASED; ?>
               <td class="dataTableHeadingContent" align="right"><a href="<?php echo "$PHP_SELF?listing=status-asc"; ?>"><?php echo tep_image_button('ic_up.gif', ' Sort ' . TABLE_HEADING_STATUS . ' --> 1-2-3 From Top '); ?></a> <a href="<?php echo "$PHP_SELF?listing=status-desc"; ?>"><?php echo tep_image_button('ic_down.gif', ' Sort ' . TABLE_HEADING_STATUS . ' --> 3-2-1 From Top '); ?></a><br><?php echo TABLE_HEADING_STATUS; ?>
               </td><td class="dataTableHeadingContent" align="right"><br><?php echo TABLE_HEADING_ACTION; ?> </td>
             </tr>

with

             <tr class="dataTableHeadingRow">
         //Missing sort fields
         <?php
         switch ($listing) {
             case "orders":
             $order = "o.orders_id";
             break;
             case "orders":
             $order = "o.orders_id DESC";
             break;
             case "customers":
             $order = "o.customers_name, o.orders_id DESC";
             break;
             case "customers-desc":
             $order = "o.customers_name DESC, o.orders_id DESC";
             break;
             case "order_total":
             $order = "ot.value";
             break;
             case "order_total-desc":
             $order = "ot.value DESC";
             break;
             case "date_purchased":
             $order = "o.date_purchased, o.customers_name";
             break;
             case "date_purchased-desc":
             $order = "o.date_purchased DESC, o.customers_name";
             break;
             case "order_status":
             $order = "s.orders_status_id, o.customers_name";
             break;
             case "order_status-desc":
             $order = "s.orders_status_id DESC, o.customers_name";
             break;
    default:
             $order = "o.orders_id DESC, o.customers_name";
         }
         ?>              
               <td class="dataTableHeadingContent" align="center">
                 <table border="0" cellspacing="0" cellpadding="0">
                   <tr>
                     <td rowspan="2" align="center" class="dataTableHeadingContent">
                        <?php echo TABLE_HEADING_ORDERS; ?>
                     </td>
                     <td align="right" valign="bottom">
                        <a href="<?php echo "$PHP_SELF?listing=orders"; ?>"><?php echo tep_image_button('ic_up.gif', 'Sort ' . TABLE_HEADING_ORDERS . ' Ascending', 'hspace="3"'); ?></a><br><a href="<?php echo "$PHP_SELF?listing=orders-desc"; ?>"><?php echo tep_image_button('ic_down.gif', 'Sort ' . TABLE_HEADING_ORDERS . ' Descending', 'hspace="3"'); ?></a>
                        </td>
                   <tr>
                 </table>
               </td>
               <td class="dataTableHeadingContent" align="center">
                 <table border="0" cellspacing="0" cellpadding="0">
                   <tr>
                     <td rowspan="2" align="left" class="dataTableHeadingContent">
                        <?php echo TABLE_HEADING_CUSTOMERS; ?>
                     </td>
                     <td align="right" valign="bottom">
                        <a href="<?php echo "$PHP_SELF?listing=customers"; ?>"><?php echo tep_image_button('ic_up.gif', 'Sort ' . TABLE_HEADING_CUSTOMERS . ' Ascending', 'hspace="3"'); ?></a><br><a href="<?php echo "$PHP_SELF?listing=customers-desc"; ?>"><?php echo tep_image_button('ic_down.gif', 'Sort ' . TABLE_HEADING_CUSTOMERS . ' Descending', 'hspace="3"'); ?></a>
                     </td>
                   <tr>
                 </table>
               </td>
               <td class="dataTableHeadingContent" align="center">
                 <table border="0" cellspacing="0" cellpadding="0">
                   <tr>
                     <td rowspan="2" align="right" class="dataTableHeadingContent">
                        <?php echo TABLE_HEADING_ORDER_TOTAL; ?>
                     </td>
                     <td align="right" valign="bottom">
                        <a href="<?php echo "$PHP_SELF?listing=order_total"; ?>"><?php echo tep_image_button('ic_up.gif', 'Sort ' . TABLE_HEADING_ORDER_TOTAL . ' Ascending', 'hspace="3"'); ?></a><br><a href="<?php echo "$PHP_SELF?listing=order_total-desc"; ?>"><?php echo tep_image_button('ic_down.gif', 'Sort ' . TABLE_HEADING_ORDER_TOTAL . ' Descending', 'hspace="3"'); ?></a>
                     </td>
                   <tr>
                 </table>
               </td>
               <td class="dataTableHeadingContent" align="center">
                 <table border="0" cellspacing="0" cellpadding="0">
                   <tr>
                     <td rowspan="2" align="center" class="dataTableHeadingContent">
                        <?php echo TABLE_HEADING_DATE_PURCHASED; ?>
                     </td>
                     <td align="right" valign="bottom">
                        <a href="<?php echo "$PHP_SELF?listing=date_purchased"; ?>"><?php echo tep_image_button('ic_up.gif', 'Sort ' . TABLE_HEADING_DATE_PURCHASED . ' Ascending', 'hspace="3"'); ?></a><br><a href="<?php echo "$PHP_SELF?listing=date_purchased-desc"; ?>"><?php echo tep_image_button('ic_down.gif', 'Sort ' . TABLE_HEADING_DATE_PURCHASED . ' Descending', 'hspace="3"'); ?></a>
                     </td>
                   <tr>
                 </table>
               </td>
               <td class="dataTableHeadingContent" align="center">
                 <table border="0" cellspacing="0" cellpadding="0">
                   <tr>
                     <td rowspan="2" align="left" class="dataTableHeadingContent">
                        <?php echo TABLE_HEADING_STATUS; ?>
                     </td>
                     <td align="right" valign="bottom">
                        <a href="<?php echo "$PHP_SELF?listing=order_status"; ?>"><?php echo tep_image_button('ic_up.gif', 'Sort ' . TABLE_HEADING_STATUS . ' Ascending', 'hspace="3"'); ?></a><br><a href="<?php echo "$PHP_SELF?listing=order_status-desc"; ?>"><?php echo tep_image_button('ic_down.gif', 'Sort ' . TABLE_HEADING_STATUS . ' Descending', 'hspace="3"'); ?></a>
                     </td>
                   <tr>
                 </table>
               </td>
               <td class="dataTableHeadingContent" align="right">
                 <?php echo TABLE_HEADING_ACTION; ?>
               </td>
             </tr>

About line 394 Replace

<?php
   if (isset($HTTP_GET_VARS['cID'])) {
     $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);
     $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by $order";
   } elseif (isset($HTTP_GET_VARS['status'])) {
     $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by $order";
   } else {
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by $order";
   }

with

<?php
   if (isset($HTTP_GET_VARS['cID'])) {
     $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);
     $orders_query_raw = "select o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, s.orders_status_id, ot.value, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by $order";
   } elseif (isset($HTTP_GET_VARS['status'])) {
     $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, s.orders_status_id, ot.value, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by $order";
   } else {
     $orders_query_raw = "select o.orders_id, o.customers_name, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, s.orders_status_id, ot.value, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by $order";
   }

About line 417 Replace

               <td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $orders['orders_id'] . '&action=edit') . '">' . tep_image(DIR_WS_ICONS . 'preview.gif', ICON_PREVIEW) . '</a> ' . $orders['customers_name']; ?></td>
               <td class="dataTableContent" align="right"><?php echo strip_tags($orders['order_total']); ?></td>
               <td class="dataTableContent" align="center"><?php echo tep_datetime_short($orders['date_purchased']); ?></td>
               <td class="dataTableContent" align="right"><?php echo $orders['orders_status_name']; ?></td>
               <td class="dataTableContent" align="right"><?php if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) { echo tep_image(DIR_WS_IMAGES . 'icon_arrow_right.gif', ''); } else { echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '">' . tep_image(DIR_WS_IMAGES . 'icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?> </td>

with

               <td class="dataTableContent" align="center"><?php echo strip_tags($orders['orders_id']); ?></td>
               <td class="dataTableContent" align="left"><?php echo strip_tags($orders['customers_name']); ?></td>
               <td class="dataTableContent" align="right"><?php echo strip_tags($orders['order_total']); ?></td>
               <td class="dataTableContent" align="center"><?php echo tep_datetime_short($orders['date_purchased']); ?></td>
               <td class="dataTableContent" align="left"><?php echo $orders['orders_status_name']; ?></td>
               <td class="dataTableContent" align="right"><?php if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) { echo tep_image(DIR_WS_IMAGES . 'icon_arrow_right.gif', ''); } else { echo '<a href="' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '">' . tep_image(DIR_WS_IMAGES . 'icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?> </td>

Also, add to /admin/includes/languages/english/orders.php the following line:

define('TABLE_HEADING_ORDERS', 'Order');

Finally, I couldn't find any ic_up.gif and ic_down.gif images in the Download Controller contribution so made my own (store in /admin/includes/languages/english/images/buttons/)

Regards, Rob

October 7, 2003

Thanks :D

lazrk · October 7, 2003

Hi Rob,

Great Post! Thanks a million.

I have just two little questions I hope you can help with. Or anyone else.

In the documentation it says:

Add a new Order Status, I call mine Updated and check its value in the orders_status table. Then set the value in the Admin for:

Downloads Controller Update Status Value

to this new value. Usually this is a 4, but could be different depending on how many orders_status settings you have.

I have no clue how to add a new order status. Please advise.

Also:

The default setting for:

Downloads Controller Orders Status Value

is 2. This means that all payment methods that set the orders_status value to >=2 will allow immediate downloads.

How do I control how payment methods set the orders_status value?

Thank you for your time,

Brandon

RobAnderson · October 7, 2003

Hi Brandon, glad to be of help ;-)

Regarding your questions:

I have no clue how to add a new order status. Please advise.

If you go into your Admin console, look for Localization and then Orders Status. This where you can insert a new one. As there are normally three by default, the new Status would automatically have a value of 4 (see further down for an explanation of how this is used, but first...)

How do I control how payment methods set the orders_status value?

If you go into your Admin console, look for Modules and then Payment.

Now for certain payment methods, e.g. Cash on Delivery, Cheque/Money Order or Bank Transfers you do not want to enable downloads until you have received payment. And because the default Set Order Status for each of these is "default", i.e. Order Status value = 1 (normally Pending), then you don't need to do anything. Provided you have installed the Downloads Controller correctly, and left the Downloads Controller Order Status Value in Configuration -> Download set to 2, then you should find that anyone purchasing downloadable products will not able able to download them until the Order Status is set to 2 or higher. This is done manually by the store admin in Customers -> Orders -> Edit once the payment has been received.

Now, for other payment methods where money is received instantaneously, e.g. Credit Cards or PayPal, you need to make sure that the Set Order Status is not set to default, i.e. set it to Processing or any other Status that has a value higher than or equal to 2. This way, when someone makes a purchase, downloads are immediately available (and no additional action is required by the store admin).

Finally, in case you're not sure what the Downloads Controller Update Status Value in Configuration -> Download does, basically it allows you to reset someone's downloads once their download period has expired or perhaps the've gone past the maximum download attempts. Again, this would be made in Customers -> Orders -> Edit and you would have to select a status that corresponds to the value of this setting (the default is 4).

I hope this all makes sense! Once you get your head around it and see it working then you'll be away.

Best of luck, Rob

P.S. Not sure if you've struck this one yet, but... to make a product downloadable, you have to have a filename entry in a Product Attribute corresponding to your product and a corresponding file in the downloads directory. Come back to me if you can't get this working.

Edited October 7, 2003 by RobAnderson

lazrk · October 8, 2003

Rob,

I can't tell you how much I appreciate the time you are spending to help me.

I'm going to copy your posts to a file and go over in detail tomorrow night.

I was just getting into the product attributes when I read your post.

I hope to have that figured out soon.

First impressions about the order status processing and paypal........

Does this script use the ipn from paypal?

If the order is processing (value = 2) then they can instant download.

Does it only switch to processing after it recieves the ipn from paypal saying the

payment has been completed?

Well, just a first impression. You are doing wonders for my learning curve Rob.

Thanks once again!

Brandon

RobAnderson · October 8, 2003

Hi Brandon, hmmm...I hope I haven't mislead you about PayPal. As far as I know there are some contributions that extend the default Paymet Module for PayPal to include IPN, e.g. http://www.oscommerce.com/community/contributions,1352

There's also a forum topic about this if that helps: http://www.oscommerce.com/forums/index.php?showtopic=51005

As I don't use this mod, that's about all I can help with sorry ;-( Perhaps someone else can help here who has some experience using this add-on?

As for helping out, it's the least I can do. I've only been at this a couple of months and am still very much a noob. But when you're helped by others, you naturally want to do the same ;-)

Keep at it! Rob

RobAnderson · October 10, 2003

Now, for other payment methods where money is received instantaneously, e.g. Credit Cards or PayPal, you need to make sure that the Set Order Status is not set to default, i.e. set it to Processing or any other Status that has a value higher than or equal to 2. This way, when someone makes a purchase, downloads are immediately available (and no additional action is required by the store admin).

Addendum...

When I mentioned the "Credit Card" payment type in my previous post, I meant it in the most generic way, i.e. through gateway payment systems like PayPal, Authorize.net, iPayment, 2Checkout, SECPay, WorldPay, etc. These all take credit cards and therefore the funds are "clear", hence downloads would become available immediately (default order status reset to Processing = 2).

The basic Credit Card payment system module in OSC is a manual system whereby you take their details securely and process them manually offline. In this case, you would leave the default order status to "default". i.e. normally Pending, until such time as you had been able to process them manually (and confirm valid card/funds etc).

Sorry for any confusion!

Rob (still v.much a noobie)

Steve2004 · December 15, 2003

Hi,

Rob I tried your code to fix the 1064 error in orders.php but I get the following error when loading 'orders' in admin.

Parse error: parse error in /home/thelemon/public_html/catalog/admin/orders.php on line 390

I have gone through carefully and tried entering your code from scratch 3 times, but get the smae error.

Any ideas?

Thanks

Steve

December 15, 2003

Steve,

Post the error message here that you are receiving.

Steve2004 · December 15, 2003

Hi,

When I load the orders.php page I get a blank page with just the following at the top of the page.

Parse error: parse error in /catalog/admin/orders.php on line 390

Thats it!

From what I can see line 390 on the orders.php modified with Rob's code is:

?switch ($listing) {

Thanks

Steve

Downloads Contoller error!

Recommended Posts

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation