englishchrissy Posted March 18, 2006 Posted March 18, 2006 Hi, I have just installed oscommerce and whist checking that everything was working ok I got this error message after buying a product and then clicking on the 'Continue' button in the 'Your Order Has Been Processed!' screen (checkout_success.php). I tried to find if anyone else has had this problem but could not find a topic on the forum. Thanks for your time. 1264 - Out of range value adjusted for column 'products_id' at row 1 insert into products_notifications (products_id, customers_id, date_added) values ('', '2', now()) [TEP STOP] Webmaster www.SexoticToys.co.uk
Guest Posted March 18, 2006 Posted March 18, 2006 for some reason, no product id is being passed into this argument, so first off, lets check you have a product id defined - open your sql manager (phpMyAdmin is the most common), browse to your database and to the products table, then browse and check that the product you just bought has a product_id defined - it doesn't matter what it is, so long as its a whole, positive number. If that part is there, then there is an issue with your site holding the info to pass into the success page, and a sthis is afresh install, probably the easiest way to resolve that is to re-upload all pages in your main folder called checkout_xxxx.php (where xxxx is anthing). If you still have issues after these two checks, post back.
englishchrissy Posted March 18, 2006 Author Posted March 18, 2006 Hi Tony, I checked the products table and all products have a product_id. I made a backup of all my checkout programs and copied all the checkout programs from the original download. I know the original ones are being used as they display the full width of the browser. The same problem occured. This happens when I click the 'Continue' button on the checkout_success screen. I can see that product_id is missing in the SQL statement but where has it gone. If I don click on the 'Continue' button but click on say, one of the categories it works ok. Any ideas. Cheers. Webmaster www.SexoticToys.co.uk
Guest Posted March 18, 2006 Posted March 18, 2006 hiya ive got the same problem i have tried what tonystewart said but still not working any help would be great thanks
Guest Posted March 18, 2006 Posted March 18, 2006 you should look your catalog\checkout_success.php the notify parameters are passed from there to the application_top.php where the notification entries are created in the database. So you could add some code to check the $notify_string if its null and do not pass parameters for the redirect to the default page..
Jimmy_D Posted March 18, 2006 Posted March 18, 2006 I wonder if this is a mysql5 thing?????? I've just fixed a similar problem by changing the insert into query lines in the code to omit inserting the id field (which is an auto-increment primary key) For Example, change from.................. tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values ('','" . (int)$products_id . "', '" . (int)$options_id . "', '" . (int)$values_id . "', '" . tep_db_input($value_price) . "', '" . tep_db_input($price_prefix) . "')"); to......... tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " (products_id, options_id, options_values_id, options_values_price, price_prefix) values ('" . (int)$products_id . "', '" . (int)$options_id . "', '" . (int)$values_id . "', '" . tep_db_input($value_price) . "', '" . tep_db_input($price_prefix) . "')"); Alternatively, this worked for me too...... tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values (NULL,'" . (int)$products_id . "', '" . (int)$options_id . "', '" . (int)$values_id . "', '" . tep_db_input($value_price) . "', '" . tep_db_input($price_prefix) . "')"); Cheers, James.
englishchrissy Posted March 18, 2006 Author Posted March 18, 2006 Hi again, Ive tried following what happens with my debugger. In checkout_success.php the product_id is still set ok. The program then goes into application_top.php where it falls over. Somewhere it has lost the product_id. I have not changed any code here so surely others must be getting the same problem. I'm not sure what the product_notification table is used for so I dont really want to do a bogged fix that might cause more problems later on. What actually should happen if it was working ok. Cheers. Webmaster www.SexoticToys.co.uk
Guest Posted March 19, 2006 Posted March 19, 2006 The product notify table keeps customers updated when you amend your products; they can subscribe to individual products and get an email automatically when that product is updated, or when they order goods, the system offers them a tick box to subscribe to the items they have ordered. Thats how it works, now completely up to you if this is something you need to use, and if not, whether you want to take that portion out of your checkout code....
smusser Posted April 10, 2006 Posted April 10, 2006 The product notify table keeps customers updated when you amend your products; they can subscribe to individual products and get an email automatically when that product is updated, or when they order goods, the system offers them a tick box to subscribe to the items they have ordered. Thats how it works, now completely up to you if this is something you need to use, and if not, whether you want to take that portion out of your checkout code.... Is there a way to turn the notify off?
CoffeeCeller Posted July 19, 2006 Posted July 19, 2006 1264 - Out of range value adjusted for column 'products_id' at row 1 insert into products_notifications (products_id, customers_id, date_added) values ('', '2', now()) [TEP STOP] I receive the same error if I do not check the box. If I check the Notification box everything is fine. I am still learning php so how do I know whether the "IF" statement is correct in Checkout_Success.php? Thanks in advance ---Coffee Celler
CoffeeCeller Posted July 21, 2006 Posted July 21, 2006 For those interested, I found this in application_top.php if ($check['count'] < 1) { tep_db_query("insert into " . TABLE_PRODUCTS_NOTIFICATIONS . " (products_id, customers_id, date_added) values ('" . $notify[$i] . "', '" . $customer_id . "', now())"); } by changing the IF ($check['count'] < 1) to IF ($check['count'] > 1) the problem went away. I hope this helps. ---Coffee Celler
Guest Posted July 21, 2006 Posted July 21, 2006 this is only an indication that is something wrong with the database. With what you did basically every customer may end up with multiple notifications for the same product. Also customers who have no notifications is likely they will not be able to add one.
CoffeeCeller Posted July 21, 2006 Posted July 21, 2006 Thanks for the response. Any idea what could be wrong with the DB, I mean broadly?
Guest Posted July 21, 2006 Posted July 21, 2006 using phpmyadmin check the products_notifications table. The entries/identifiers there should show valid products and customers
CoffeeCeller Posted July 21, 2006 Posted July 21, 2006 using phpmyadmin check the products_notifications table. The entries/identifiers there should show valid products and customers I guess I am just not quite understanding what you are saying. I have checked the DB I see two entries which I put there on the 19th. If I log into My Account under product notifications list, two products show up. If I uncheck one then refresh the DB. Only one entry shows shows in the DB and on the page. Seems like it is working so far. If I run an order on the checkout_success page I am asked if I would like notification of any of the products I have purchased. If I select one everything is fine. If I do not select any I get the 1264 error. (on the side) I recognize that my early proposed change to application_top could not have solved my problem because > 1 is not the same as >= 1. I have since changed it back to original < 1. Do you know where to find and/or have any code to add to the application_top to handle the Checkout_success page issue? Thanks ---Coffee Celler
♥Monika in Germany Posted July 21, 2006 Posted July 21, 2006 actually, I'm surprised it's working partly ... see this thread: http://www.oscommerce.com/forums/index.php?sho...=212360&hl= :-) Monika addicted to writing code ... can't get enough of databases either, LOL! my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum Interactive Media Award July 2007 ~ category E-Commerce my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...
Guest Posted July 21, 2006 Posted July 21, 2006 actually, I'm surprised it's working partly ... see this thread: http://www.oscommerce.com/forums/index.php?sho...=212360&hl= I am surprized is not working for you. The default works here that I tried. The entries are inserted properly in the database and the customer_id is maintained. Some customization must cause the problem.
♥Monika in Germany Posted July 21, 2006 Posted July 21, 2006 I am surprized is not working for you. The default works here that I tried. The entries are inserted properly in the database and the customer_id is maintained. Some customization must cause the problem. Mark, mine was a vanilla Nov 13 install on my local puter, set up for that forum thread. :-) Monika addicted to writing code ... can't get enough of databases either, LOL! my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum Interactive Media Award July 2007 ~ category E-Commerce my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...
Guest Posted July 21, 2006 Posted July 21, 2006 Mark, mine was a vanilla Nov 13 install on my local puter, set up for that forum thread. yes mine too. Here is the checkout_success.php which I tried and works <?php /* $Id: checkout_success.php,v 1.49 2003/06/09 23:03:53 hpdl Exp $ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2003 osCommerce Released under the GNU General Public License */ require('includes/application_top.php'); // if the customer is not logged on, redirect them to the shopping cart page if (!tep_session_is_registered('customer_id')) { tep_redirect(tep_href_link(FILENAME_SHOPPING_CART)); } if (isset($HTTP_GET_VARS['action']) && ($HTTP_GET_VARS['action'] == 'update')) { $notify_string = 'action=notify&'; $notify = $HTTP_POST_VARS['notify']; if (!is_array($notify)) $notify = array($notify); for ($i=0, $n=sizeof($notify); $i<$n; $i++) { $notify_string .= 'notify[]=' . $notify[$i] . '&'; } if (strlen($notify_string) > 0) $notify_string = substr($notify_string, 0, -1); tep_redirect(tep_href_link(FILENAME_DEFAULT, $notify_string)); } require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_CHECKOUT_SUCCESS); $breadcrumb->add(NAVBAR_TITLE_1); $breadcrumb->add(NAVBAR_TITLE_2); $global_query = tep_db_query("select global_product_notifications from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . (int)$customer_id . "'"); $global = tep_db_fetch_array($global_query); if ($global['global_product_notifications'] != '1') { $orders_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where customers_id = '" . (int)$customer_id . "' order by date_purchased desc limit 1"); $orders = tep_db_fetch_array($orders_query); $products_array = array(); $products_query = tep_db_query("select products_id, products_name from " . TABLE_ORDERS_PRODUCTS . " where orders_id = '" . (int)$orders['orders_id'] . "' order by products_name"); while ($products = tep_db_fetch_array($products_query)) { $products_array[] = array('id' => $products['products_id'], 'text' => $products['products_name']); } } ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo TITLE; ?></title> <base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>"> <link rel="stylesheet" type="text/css" href="stylesheet.css"> </head> <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0"> <!-- header //--> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <!-- header_eof //--> <!-- body //--> <table border="0" width="100%" cellspacing="3" cellpadding="3"> <tr> <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2"> <!-- left_navigation //--> <?php require(DIR_WS_INCLUDES . 'column_left.php'); ?> <!-- left_navigation_eof //--> </table></td> <!-- body_text //--> <td width="100%" valign="top"><?php echo tep_draw_form('order', tep_href_link(FILENAME_CHECKOUT_SUCCESS, 'action=update', 'SSL')); ?><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td><table border="0" width="100%" cellspacing="4" cellpadding="2"> <tr> <td valign="top"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_man_on_board.gif', HEADING_TITLE); ?></td> <td valign="top" class="main"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?><div align="center" class="pageHeading"><?php echo HEADING_TITLE; ?></div><br><?php echo TEXT_SUCCESS; ?><br><br> <?php if ($global['global_product_notifications'] != '1') { echo TEXT_NOTIFY_PRODUCTS . '<br><p class="productsNotifications">'; $products_displayed = array(); for ($i=0, $n=sizeof($products_array); $i<$n; $i++) { if (!in_array($products_array[$i]['id'], $products_displayed)) { echo tep_draw_checkbox_field('notify[]', $products_array[$i]['id']) . ' ' . $products_array[$i]['text'] . '<br>'; $products_displayed[] = $products_array[$i]['id']; } } echo '</p>'; } else { echo TEXT_SEE_ORDERS . '<br><br>' . TEXT_CONTACT_STORE_OWNER; } ?> <h3><?php echo TEXT_THANKS_FOR_SHOPPING; ?></h3></td> </tr> </table></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <tr> <td align="right" class="main"><?php echo tep_image_submit('button_continue.gif', IMAGE_BUTTON_CONTINUE); ?></td> </tr> <tr> <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td> </tr> <tr> <td><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td width="25%"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td width="50%" align="right"><?php echo tep_draw_separator('pixel_silver.gif', '1', '5'); ?></td> <td width="50%"><?php echo tep_draw_separator('pixel_silver.gif', '100%', '1'); ?></td> </tr> </table></td> <td width="25%"><?php echo tep_draw_separator('pixel_silver.gif', '100%', '1'); ?></td> <td width="25%"><?php echo tep_draw_separator('pixel_silver.gif', '100%', '1'); ?></td> <td width="25%"><table border="0" width="100%" cellspacing="0" cellpadding="0"> <tr> <td width="50%"><?php echo tep_draw_separator('pixel_silver.gif', '100%', '1'); ?></td> <td width="50%"><?php echo tep_image(DIR_WS_IMAGES . 'checkout_bullet.gif'); ?></td> </tr> </table></td> </tr> <tr> <td align="center" width="25%" class="checkoutBarFrom"><?php echo CHECKOUT_BAR_DELIVERY; ?></td> <td align="center" width="25%" class="checkoutBarFrom"><?php echo CHECKOUT_BAR_PAYMENT; ?></td> <td align="center" width="25%" class="checkoutBarFrom"><?php echo CHECKOUT_BAR_CONFIRMATION; ?></td> <td align="center" width="25%" class="checkoutBarCurrent"><?php echo CHECKOUT_BAR_FINISHED; ?></td> </tr> </table></td> </tr> <?php if (DOWNLOAD_ENABLED == 'true') include(DIR_WS_MODULES . 'downloads.php'); ?> </table></form></td> <!-- body_text_eof //--> <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2"> <!-- right_navigation //--> <?php require(DIR_WS_INCLUDES . 'column_right.php'); ?> <!-- right_navigation_eof //--> </table></td> </tr> </table> <!-- body_eof //--> <!-- footer //--> <?php require(DIR_WS_INCLUDES . 'footer.php'); ?> <!-- footer_eof //--> <br> </body> </html> <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> and the part from application top notify-case. case 'notify' : if (tep_session_is_registered('customer_id')) { if (isset($HTTP_GET_VARS['products_id'])) { $notify = $HTTP_GET_VARS['products_id']; } elseif (isset($HTTP_GET_VARS['notify'])) { $notify = $HTTP_GET_VARS['notify']; } elseif (isset($HTTP_POST_VARS['notify'])) { $notify = $HTTP_POST_VARS['notify']; } else { tep_redirect(tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action', 'notify')))); } if (!is_array($notify)) $notify = array($notify); for ($i=0, $n=sizeof($notify); $i<$n; $i++) { $check_query = tep_db_query("select count(*) as count from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $notify[$i] . "' and customers_id = '" . $customer_id . "'"); $check = tep_db_fetch_array($check_query); if ($check['count'] < 1) { tep_db_query("insert into " . TABLE_PRODUCTS_NOTIFICATIONS . " (products_id, customers_id, date_added) values ('" . $notify[$i] . "', '" . $customer_id . "', now())"); } } tep_redirect(tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action', 'notify')))); } else { $navigation->set_snapshot(); tep_redirect(tep_href_link(FILENAME_LOGIN, '', 'SSL')); } break;
Guest Posted July 21, 2006 Posted July 21, 2006 but if there is a problem with the database the insert query may fail.
♥Monika in Germany Posted July 21, 2006 Posted July 21, 2006 spooky ... as we both use vanilla, files are the same. Database is fine too, I had no issues adding a notify from the products page, and after the suggested change for the form the checkout_success form worked too ... :-) Monika addicted to writing code ... can't get enough of databases either, LOL! my toolbox: Textpad - Compare and Merge - phpMyAdmin - WS_FTP - Photoshop - How to search the forum Interactive Media Award July 2007 ~ category E-Commerce my advice on the forum is for free, PMs where you send me work are considered consultation which I charge for ...
CoffeeCeller Posted July 21, 2006 Posted July 21, 2006 but if there is a problem with the database the insert query may fail. Thanks to both you and Monika for your responses. I have read through and will attempt the code changes suggested by Monika in the other thread. Realistically I don't need the feature as much as I need to know why things break and how to fix them. Mark it appears the insert query failed because it is trying to stick a null in a field that is not set allow nulls. Remember the error shows what it is trying to insert into the DB Product_id, Customer_id, Date_Added ("","2","now()") To me it seems that when the continue button is pressed there is no code stating "if no box is checked don't add to db" and is trying to force the Product_notify feature regardless if a CB is selected. Still being new to php I am not sure how to create such a check. Thanks for your time.
Guest Posted July 21, 2006 Posted July 21, 2006 Mark it appears the insert query failed because it is trying to stick a null in a field that is not set allow nulls. well I don't see it with the defaults. One other thing you could do is check the demo.oscommerce.com where you have all default files just to be sure. If you can replicate the problem there, then I missed something with my configuration perhaps. But if not then there is a specific problem with the environment or the scripts. You could start with the default core files. Backup your files then use the default checkout_success.php and application_top.php and see if that works. If it doesn't then I would check the database or perhaps some of the low level functions of osc.
Zamil Posted August 9, 2006 Posted August 9, 2006 Hi there, following the thread, it seams that no solution was found so far. Or did I miss something? If so, would you be so kind and point me to the solution? Thank you! If not, I do have the same issue and I'm using vanilla files for checkout_success.php and application_top.php. The problem occures only if the customer clicks on the "continue" button and has no product selected for notification. If he has, all is fine. It seams to me that the following code in application_top.php causes the problem: case 'notify' : if (tep_session_is_registered('customer_id')) { if (isset($HTTP_GET_VARS['products_id'])) { $notify = $HTTP_GET_VARS['products_id']; } elseif (isset($HTTP_GET_VARS['notify'])) { $notify = $HTTP_GET_VARS['notify']; } elseif (isset($HTTP_POST_VARS['notify'])) { $notify = $HTTP_POST_VARS['notify']; } else { tep_redirect(tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action', 'notify')))); } if (!is_array($notify)) $notify = array($notify); for ($i=0, $n=sizeof($notify); $i<$n; $i++) { $check_query = tep_db_query("select count(*) as count from " . TABLE_PRODUCTS_NOTIFICATIONS . " where products_id = '" . $notify[$i] . "' and customers_id = '" . $customer_id . "'"); $check = tep_db_fetch_array($check_query); if ($check['count'] < 1) { tep_db_query("insert into " . TABLE_PRODUCTS_NOTIFICATIONS . " (products_id, customers_id, date_added) values ('" . $notify[$i] . "', '" . $customer_id . "', now())"); } } tep_redirect(tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action', 'notify')))); } else { $navigation->set_snapshot(); tep_redirect(tep_href_link(FILENAME_LOGIN, '', 'SSL')); } Correct me if I'm wrong but it seams that even if $HTTP_POST_VARS['notify'] does not contain a product_id, it is somehow set to some value. The else-branch } else { tep_redirect(tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action', 'notify')))); is not executed; which should have been if no product_it was given. In order to fix this problem I've changed the code to the following: if ($check['count'] < 1) { if ($notify[$i]!=''){ // added by ITE, Aug. 8th to get arround the redirect issue tep_db_query("insert into " . TABLE_PRODUCTS_NOTIFICATIONS . " (products_id, customers_id, date_added) values ('" . $notify[$i] . "', '" . $customer_id . "', now())"); } } Any better solutions available? Thank you for your help!
Guest Posted August 9, 2006 Posted August 9, 2006 you could change your checkout_success.php page from this if (!is_array($notify)) $notify = array($notify); to this if (!is_array($notify)) tep_redirect(tep_href_link(FILENAME_DEFAULT)); See if the redirection takes effect ie you do not see the error. Still I was unable to replicate the problem here. Also check your products_notifications table for invalid entries.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.