Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Found 1064 Sql error


Mort-lemur

Recommended Posts

Posted

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.

Posted

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.

:blink:
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.

Posted

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.

Posted

@@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 ?

post-253268-0-89859300-1390778948_thumb.png

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.

Posted

That's a serialized string.

 

It -should- return "1" based on

implode(unserialize(RCS_PENDING_SALE_STATUS), ',')

 

 

Posted

@@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.

Posted

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.

Posted

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.

Posted

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.

Posted

@@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.

Posted

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)?

Posted

@@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.

Posted

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.

Posted

@@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.

Posted

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?

Posted

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.

Posted

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().

Posted

@@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.

Posted

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.

Posted

@@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.

Posted

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.

Posted

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

  • 7 months later...
Posted

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 too
Thank 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 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 >= "20140901" and ot.class = "ot_total"

  • 2 weeks later...
Posted

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..

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...