Mort-lemur Posted January 25, 2014 Posted January 25, 2014 Just found that I have a 1064 error on one of my reports pages (recover cart sales report) The error given is: 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 ') and s.orders_status_id = o.orders_status and o.date_purchased >= "20140124" an' at line 1 select o.orders_id, o.customers_id, o.date_purchased, s.orders_status_name, ot.text as order_total, ot.value from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where (o.customers_id = 4758 OR o.customers_email_address like "" OR o.customers_name like "customers name") and orders_status NOT IN () and s.orders_status_id = o.orders_status and o.date_purchased >= "20140124" and ot.class = "ot_total" [TEP STOP] The query calling the sql in the file is: // Query DB for the FIRST order that matches this customer ID and came after the abandoned cart $orders_query_raw = "select o.orders_id, o.customers_id, o.date_purchased, s.orders_status_name, ot.text as order_total, ot.value 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 . ' OR o.customers_email_address like "' . $crec['customers_email_address'] .'" OR o.customers_name like "' . $crec['customers_firstname'] . ' ' . $crec['customers_lastname'] . '") and orders_status NOT IN (' . implode(unserialize(RCS_PENDING_SALE_STATUS), ',') . ') and s.orders_status_id = o.orders_status and o.date_purchased >= "' . $inrec['dateadded'] . '" and ot.class = "ot_total"'; Im even worse at SQL than I am at php - so if anyone could give me a pointer I would be grateful. Many Thanks Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
tgely Posted January 25, 2014 Posted January 25, 2014 orders_status NOT IN () Look at the php code and find the sql string something like this "orders_status NOT IN " . array() . " The array() was Null or empty in this example. osCommerce based shop owner with minimal design and focused on background works. When the less is more.Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store.
burt Posted January 26, 2014 Posted January 26, 2014 As Gergely stated, this: and orders_status NOT IN (' . implode(unserialize(RCS_PENDING_SALE_STATUS), ',') . ') creates a comma separated string from an array. So you need to ensure that RCS_PENDING_SALE_STATUS is an array. You would get more valuable answers in the relevant support thread of the addon.
Mort-lemur Posted January 26, 2014 Author Posted January 26, 2014 @@burt Thanks for that - there is limited or no support for the RCS mod these days, hence me posting in the general thread. Is this an array ? Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
burt Posted January 26, 2014 Posted January 26, 2014 That's a serialized string. It -should- return "1" based on implode(unserialize(RCS_PENDING_SALE_STATUS), ',')
Mort-lemur Posted January 27, 2014 Author Posted January 27, 2014 @@burt Thanks Gary, I have found what is causing the error, and it really puzzles me.... I was using the latest version of RCS which was tweeked by Matt, so I reverted back to my old cobbled together version of admin/stats_recover_cart_sales.php which worked fine (despite the formatting being shot to hell) The old version containes the following line: // Query database for abandoned carts within our timeframe $conquery = tep_db_query("select * from ". TABLE_SCART ." where dateadded >= '".$ndate."' order by dateadded DESC" ); $rc_cnt = mysql_num_rows($conquery); whith the above I dont get the 1064 error. But if I change it to: // Query database for abandoned carts within our timeframe $conquery = tep_db_query("select * from ". TABLE_SCART ." where dateadded >= '".$ndate."' order by dateadded DESC" ); $rc_cnt = tep_db_num_rows($conquery); Then the 1064 error is back So basically something in Matts version is causing the error? Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
Mort-lemur Posted January 27, 2014 Author Posted January 27, 2014 also if I replace tep_db_num_rows in Matts version with mysql_num_rows it also works as it should. Now Im really confused.... Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
Mort-lemur Posted January 27, 2014 Author Posted January 27, 2014 Hmmm but that gives the following entry in my admin error log - PHP Warning: mysql_num_rows() expects parameter 1 to be resource, object given in /home/username/public_html/admin/stats_recover_cart_sales.php on line 63 with line 63 being the mysql line above Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
MrPhil Posted January 27, 2014 Posted January 27, 2014 whith the above I dont get the 1064 error. But if I change it to: Uh, are those two pieces of code different? If not, it must be something earlier in the code that's different and causing the problem.
Mort-lemur Posted January 27, 2014 Author Posted January 27, 2014 @@MrPhil Thanks phil, yes they are different, one uses the tep_db_num_rows one uses mysql_num_rows the latter works, but with the error log error, the former does not work and causes the 1064 error. All the rest of the code is the same- Im just changing this one line Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
MrPhil Posted January 27, 2014 Posted January 27, 2014 You get a 1064 (MySQL syntax) error if you only change mysql_num_rows() to tep_db_num_rows()? Or are there other differences earlier in the code? Your osC version does have tep_db_num_rows() defined, and it's the current version (mysqli_ code, I believe, but can't get to my copy right now)?
Mort-lemur Posted January 27, 2014 Author Posted January 27, 2014 @@MrPhil using OSC 2.3.3.4 and all the other calls elsewhere in the other files to tep_db_num_rows work as they should. Could this be a problem with a particular table in the database? Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
MrPhil Posted January 27, 2014 Posted January 27, 2014 If tep_db_num_rows() works OK elsewhere, that is probably not your problem. Where exactly are you getting the 1064 error? I don't see how the *_num_rows() call is going to give one. A 1064 is usually something wrong with the SQL query itself (e.g., the empty "IN" list you showed earlier). You're absolutely sure that just changing mysql_num_rows() to tep_db_num_rows(), and nothing else, triggers the 1064 error? If so, I'm stumped.
Mort-lemur Posted January 27, 2014 Author Posted January 27, 2014 @@MrPhil yep - in the same file just changing that line back to tep_db_num_rows goes straight back to the 1064 error.... I am stumped also... Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
MrPhil Posted January 28, 2014 Posted January 28, 2014 In my 2.3.3, tep_db_num_rows() is simply an alias for mysql_num_rows(): function tep_db_num_rows($db_query) { return mysql_num_rows($db_query); } Does your copy (in database.php) look any different? Maybe your copy got corrupted?
burt Posted January 28, 2014 Posted January 28, 2014 Use tep_db_num_rows. Ensure that the actual tep_db_num_rows function (/includes/functions/database.php) looks like this: function tep_db_num_rows($db_query) { return mysqli_num_rows($db_query); } If that still does not work, then the problem is elsewhere in the addon.
MrPhil Posted January 28, 2014 Posted January 28, 2014 If the rest of your version of osC is using MySQLi, then yes, you would want tep_db_num_rows() to use mysqli_num_rows() instead of mysql_num_rows().
Mort-lemur Posted January 28, 2014 Author Posted January 28, 2014 @@burt If that still does not work, then the problem is elsewhere in the addon. reinstalled all the files again - same problem, could it be the way the database table was created? Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
MrPhil Posted January 29, 2014 Posted January 29, 2014 What puzzles me is that a 1064 is an SQL syntax error -- there's something wrong with the SQL query (e.g., ...IN ()...). Simply replacing a call to mysql_num_rows() with tep_db_num_rows(), if that's the ONLY change, should not trigger such an error, AFAIK. There's a slim chance that if you're mixing MySQL and MySQLi calls, that could cause some sort of problem, so check if you're all one or the other. This is especially true if your base osC expects MySQL and the add-on is using MySQLi calls, or vice-versa. In that case, I would harmonize them to be all one or the other, and see if that helps.
Mort-lemur Posted January 29, 2014 Author Posted January 29, 2014 @@MrPhil Im using osc 2.3.3.4 - I will download a copy of the site to my local machine and search all the files for any calls to mysql Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
Mort-lemur Posted January 29, 2014 Author Posted January 29, 2014 Ignore - Post deleted Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.
♥mattjt83 Posted January 30, 2014 Posted January 30, 2014 If you are using the latest version of osC you need to use tep_db_num_rows() and not mysql_num_rows. The version I uploaded was not really "my" version I just edited the install notes a bit and changed the page format to fit with 2.3.1. From: http://addons.oscommerce.com/info/8333 I just made some small adjustments: Fixed a couple of errors in the installation instructions. Fixed stats_recover_cart_sales.php to be 2.3.1 compatible. You may need to check the RCS_PENDING_SALE_STATUS constant before building the query and adjust the query accordingly. Maybe something is going wrong when that constant is being created. Matt
radhavallabh Posted September 1, 2014 Posted September 1, 2014 Hi, I am getting this same above error on my 2.3.4 install of oscommerce for the Recover Cart Sale Module were you able to fix this issue...Kindly could you help me through this tooThank you in advance... 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 ') and s.orders_status_id = o.orders_status and o.date_purchased >= "20140901" an' at line 1select o.orders_id, o.customers_id, o.date_purchased, s.orders_status_name, ot.text as order_total, ot.value from orders o left join orders_total ot on (o.orders_id = ot.orders_id), orders_status s where (o.customers_id = 4758 OR o.customers_email_address like "" OR o.customers_name like "customers name") and orders_status NOT IN () and s.orders_status_id = o.orders_status and o.date_purchased >= "20140901" and ot.class = "ot_total"
radhavallabh Posted September 10, 2014 Posted September 10, 2014 Hi!! On Recover Cart Sales every thing works fine with OSC 2.3.4 From Recovering orders in Tool Section, Email To customers All language files are in place even for the Reports section have got the language file in place.... One Bug... I mailed 4 customers worked fine even emails received -- but in Reports section some issue persists--- Cross-Checked Database: The 4 records have been created under scart. But In Reports section It shows Examined records as 4 But does not display the details of the customers and the carts...... _______________________________________________________________________ Examined Records: 4 Recovered Sales: 0 SCart ID Date Added CUSTOMER NAME Order Date Status Amount Total Recovered: 0% $0.00 __________________________________________________________________________ Please can anyone help me fix this issue..... Thanks in advance..
Recommended Posts
Archived
This topic is now archived and is closed to further replies.