Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

SQL problem - What am I missing?


kru

Recommended Posts

I installed the SQL Interface V1.00 Add-On (http://addons.oscommerce.com/info/2342/v,22) but I can't get SQL statements to run correctly.

 

For example:

 

SELECT orders_id FROM orders_status_history WHERE comments LIKE '%12345%'

 

yields a result of:

 

MySQL error 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 '\'%12345%\'' at line 1

 

However, I can run the exact same statement (copied and pasted) in mysqladmin (through the OPS panel of my hosting company) and get one order_id containing the string.

 

Why can I run it through mysqladmin and not through the entry box created by the Add-On?

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

Hi

 

'\'%12345%\'' - looks like it's escaping the ' character ??

 

OK but what do you put in front of it (or around it) to prevent that. I'm assuming there's another way it needs to be written but I guess my point is, I don't know what that is. I wrote the SQL statement like I normally do when I run it in mysqladmin because it ran/runs fine there. So what's different and what do I need to correct to allow it to run in the Add-On?

 

Thanks.

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

I'm assuming this has something to do with running SQL via a php script versus running it in mysqladmin. I've been searching the internet for hours and can't seem to make sense of this. With my luck, it'll end up being something easy and I'll feel like an idiot...

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

Anyone?

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

I'm not familiar with that addon but it needs to use the tep_db_input function when adding to the database and, I'm assuming, it isn't. Compare the code that makes that call to one of the others in the shop to see how that function is used.

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

It's not adding to the database. Merely selecting records. But it does appear there is a problem with the program. When I change the SQL query to get rid of the LIKE clause and select the comments from a particular orders_id, it runs but it doesn't display the actual comments on the screen. It just says "Your query was successful! Rows Affected: 5."

 

If you could make some recommendations on how to modify this program, it would be great. It sounds like exactly what I need to search the comments field from the Admin page. We would like to start storing some information there but can only do so if we can do a quick and easy search to find records with particular strings in that field. Or, if you have an idea of another program that would accomplish this, even better.

 

Here's the code from the php program:

 

<?php

/*

$Id: sql_interface.php,v 1.00 2004/08/13 00:28:44 draven Exp $

*/

 

require('includes/application_top.php');

$text_heading = INITIAL_TITLE;

 

function sqlquery($query) {

$result = mysql_query($query);

global $query_result;

if (mysql_errno()) {

$query_result = "MySQL error ".mysql_errno().": ".mysql_error()."\n\nWhile executing:\n\n$query\n------------------------------------------------------------------------------------------\n\n";

} else {

$query_result = "Your query was successful!\nRows Affected: " . mysql_affected_rows();

}

return $result;

}

 

$action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : '');

 

if (isset($HTTP_POST_VARS['action']) && ($HTTP_POST_VARS['action'] == 'process')) {

sqlquery($HTTP_POST_VARS['query_entry']);

$text_heading = POST_QUERY_TITLE;

$tryagain = TRY_AGAIN_TEXT;

}

?>

<!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 HEADING_TITLE; ?></title>

<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">

<script language="javascript" src="includes/general.js"></script>

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">

<!-- header //-->

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<!-- header_eof //-->

 

<!-- body //-->

<table border="0" width="100%" cellspacing="2" cellpadding="2">

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">

<!-- left_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<!-- left_navigation_eof //-->

</table></td>

<!-- body_text //-->

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2"><?php echo tep_draw_form('sql_interface', 'sql_interface.php', 'post') . tep_draw_hidden_field('action', 'process'); ?>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading" colspan="3"><?php echo HEADING_TITLE; ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">

<tr>

<td class="main" colspan="3"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

<tr>

<td class="main" colspan="2"><?php echo '<b>' . $text_heading . ':</b>'; ?></td>

<td class="main" align="right" colspan="1"><i><?php echo $tryagain; ?></i></td>

</tr>

<tr>

<td class="main" colspan="3"><?php echo tep_draw_textarea_field('query_entry', '', 137, 30, $query_result, '', false); ?></td>

</tr>

<tr>

<td class="main" colspan="3"><?php echo tep_draw_separator('pixel_trans.gif', '1', '10'); ?></td>

</tr>

<tr>

<td width="10"><?php echo tep_draw_separator('pixel_trans.gif', '10', '1'); ?></td>

<td colspan="2"align="right"><?php echo tep_image_submit('button_send.gif', IMAGE_BUTTON_EXECUTE_SQL) . tep_draw_separator('pixel_trans.gif', '10', '1'); ?></form></td>

</tr>

<tr>

<td class="smallText" colspan="3"> </td>

</tr>

</table></td>

</tr>

</table></td>

</tr>

</table></td>

<!-- body_text_eof //-->

</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'); ?>

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

Or reading from - same thing in this case. I haven't tried it but try changing

sqlquery($HTTP_POST_VARS['query_entry']);

to

$sql = tep_db_prepare_input($HTTP_POST_VARS['query_entry']);
sqlquery(tep_db_input($sql);

Support Links:

For Hire: Contact me for anything you need help with for your shop: upgrading, hosting, repairs, code written, etc.

All of My Addons

Get the latest versions of my addons

Recommended SEO Addons

Link to comment
Share on other sites

Or reading from - same thing in this case. I haven't tried it but try changing

sqlquery($HTTP_POST_VARS['query_entry']);

to

$sql = tep_db_prepare_input($HTTP_POST_VARS['query_entry']);
sqlquery(tep_db_input($sql);

 

Tried it. Got this:

 

Parse error: syntax error, unexpected ';' in /blahblahblah/yadayada/admin/sql_interface.php on line 24

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

Added the missing paren. Page loaded this time but the error after I submit it is the same as before:

 

MySQL error 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 '\'%1Z4F5F250352214642%\'' at line 1

 

While executing:

 

SELECT orders_id FROM `orders_status_history` WHERE `comments` LIKE \'%1Z4F5F250352214642%\'

 

 

This is after pasting the following code into the text box and submitting it (hitting SEND button):

 

SELECT orders_id FROM `orders_status_history` WHERE `comments` LIKE '%1Z4F5F250352214642%'

 

 

What is the following code from lines 9-13 doing? Does it need re-worked? The error message is being generated there.

 

function sqlquery($query) {

$result = mysql_query($query);

global $query_result;

if (mysql_errno()) {

$query_result = "MySQL error ".mysql_errno().": ".mysql_error()."\n\nWhile executing:\n\n$query\n

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

Aside from the issue of how to format the text string for the LIKE statement, it is also a problem that it doesn't write the results of a successful query to the screen. A query like:

 

SELECT * FROM `orders_status_history` WHERE `orders_id` = 31166

 

generates a success message and indicates there are 5 records affected but it doesn't show me the results on the screen.

 

 

This Add-On is not described very well. Maybe he never intended it to return results. Maybe he was just using it for updates. It may not be what we're looking for but surely there's something out there that will suit our needs. I was hoping for a report but this Add-On had me thinking we could submit an SQL query from the Admin panel instead.

 

Specifically, we are trying to search the comments field in the Orders table. Ideally it would be a report with a text field where we enter a value, click a button to run it and it searches all records for a match, returning them to the screen. There are several 'canned' reports in osCommerce (Products Viewed, etc) but they run automatically when you click on the link. None allow an input value for a search. If I could find a report that allowed an input value, I could possibly modify it for our purposes. But I've searched several times with no luck.

 

Anyone have any ideas or want to generate a report that would do something like this? I'm sure others would find it useful to search order comments too...

 

Thanks.

Add-Ons personally installed:

Step by Step Manual Order -- Request Reviews -- Reviews in Product Listing -- Reviews in Product Display -- Review Approval System -- Leverage Browser Cache --
Header Tag Controller -- Multilayer SEO Pop Out Menu -- Follow Us Box -- View All Products -- USPS Shipping Labels -- UPS Shipping Labels -- Monthly Sales/Tax Report --
htacess Optimisation -- Remove Unused Images -- Master Password -- Admin Change Customer Password -- Database Backup Manager -- Zero Stock Report --
Searchbox Search In Descriptions -- Easy Populate 2.76i -- Barcode Rendering -- Admin Sort By Model -- Products Purchased Report

Add-Ons personally developed:

Search for email address, etc in Orders -- Discontinue Product

Add-Ons installed by others:

View Counter -- Site Monitor -- Image Thumbnailer -- Database Optimizer -- Recaptcha -- Discount Coupons -- Add More Fields

Link to comment
Share on other sites

If you get a report of something like

...comments LIKE \'%12345%\'

that usually means that at least twice your string has been "prepped" for the database, something like \\'%12345%\\'. You want the actual string in the end to be ...comments LIKE '%12345%', but it's got some \ unnecessarily added to it. Usually this is caused by a programmer who isn't properly keeping track of whether they've already prepped the string for database use, or maybe "do it one [more] time just to be sure", or even not realizing when they do and don't need to "prep".

 

It might also be something to do with PHP "magic quotes" settings on your server, that it's automatically adding escapes \ to the string. You might read up on that, especially as the behavior has changed with recent PHP 5.x releases. Hopefully this will give you a few more places to look...

Link to comment
Share on other sites

This tester

 

$result = mysql_query('SELECT countries_iso_code_3 FROM countries WHERE countries_name LIKE "Austr%"');

 

 

is working all right for me, in that it is returning

 

AUS

AUT

 

so it seems to be helpful to put the comparator string inside double-quotes.

Link to comment
Share on other sites

Either

'SELECT countries_iso_code_3 FROM countries WHERE countries_name LIKE "Austr%"'

or

"SELECT countries_iso_code_3 FROM countries WHERE countries_name LIKE 'Austr%'"

would work (I believe that the second version, using ' within the SQL query, is somewhat preferred).

 

Note that this is a direct call to mysql_query(). In the OP's case, they appear to be entering a query string which is being processed in some way (adding \ to ') before being passed to mysql_query(), and that's a problem. I was unable to find where this was happening with a quick look at the code given.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...