PHP Fatal error: 1064

Hi

I discovered this error in my log file and I am struggling to work out what could be the cause.

My hosting company recently upgraded the server to php 5.4 and I subsequently updated the necessary patches as provided by them and now I am starting to see the below error which may or may not be related.

PHP Fatal 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 ''-1''' at line 1 :: select categories_id from categories where categories_id='-1'' in /home/admin/public_html/includes/classes/db/mysql/query_factory.php on line 120

What I am trying to work out is where the error originates, which having read loads of threads I understand is'nt immediately obvious.

What I find puzzling is the query is looking for a category ID of -1 which obviously doesnt exist. A second error log has categories_id='63'A=0'
so does anyone have any idea where I should start my search or is it instead a server issue?

Thanks in advance.

Cheers
Brinley

Start by making the core file changes (to be included in ZC 1.5.3) identified by this (http://www.zen-cart.com/showthread.php?211396-Use-debug_backtrace%28%29-to-determine-query_factory-caller-on-error&p=1230478#post1230478) posting. Then you'll be able to see who the actual culprit is!

P.S. Don't include the "naughty" file in functions/extra_functions!

Thx for the help lat9

Cant believe I missed that thread, its perfect!

Cheers
Brinley

No problem, happy to help!

I installed the debug_backtrace which was fab as it showed what file was causing the problem which was tpl_index_product_list.php on line 169

I then checked the file against the original copy and its the same which leads me to believe its the actual -1 category number that's the issue.

Here's the error again

[08-Jun-2014 05:25:19 Europe/London] PHP Fatal 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 ''-1''' at line 1 :: select categories_id from zen_categories where categories_id='-1'' ==> (as called by) /home/public_html/includes/templates/templates/tpl_index_product_list.php on line 169

So my question is, can anyone shed some light on how to I go about tracking down where this -1 would be?

I have checked the categories table and the ceon URI mapping table which I have installed and found nothing, searched with the Dev Tools kit, still nothing.

Cheers
Brinley

An out-of-the-box version of tpl_index_product_list.php, lines 167-169 read

<?php
    if ($show_display_category->fields['configuration_key'] == 'SHOW_PRODUCT_INFO_LISTING_BELOW_FEATURED_PRODUCTS') { ?>
<?php

so I'm not sure what version of the file you have. I agree that it's the -1 value for the category ID that's causing the debug-log to be generated.

Hi Lat9

Yeah! My file has additional lines of code for CEON URI MAPPING but I ran the file in BeyomdCompare and the relevant lines are the exactly the same - but well spotted!

Cheers
Brinley

Could you post the code on/around your file's line 169 (say 20-50 lines before and after)?

Hi Lat9

Here u go

<?php
  echo $form;
  echo zen_draw_hidden_field('main_page', FILENAME_DEFAULT);
  echo zen_hide_session_id();
?>
<?php
  // draw cPath if known
  if (!$getoption_set) {
    echo zen_draw_hidden_field('cPath', $cPath);
  } else {
    // draw manufacturers_id
    echo zen_draw_hidden_field($get_option_variable, $_GET[$get_option_variable]);
  }

  // draw music_genre_id
  if (isset($_GET['music_genre_id']) && $_GET['music_genre_id'] != '') echo zen_draw_hidden_field('music_genre_id', $_GET['music_genre_id']);

  // draw record_company_id
  if (isset($_GET['record_company_id']) && $_GET['record_company_id'] != '') echo zen_draw_hidden_field('record_company_id', $_GET['record_company_id']);

  // draw typefilter
  if (isset($_GET['typefilter']) && $_GET['typefilter'] != '') echo zen_draw_hidden_field('typefilter', $_GET['typefilter']);

  // draw manufacturers_id if not already done earlier
  if ($get_option_variable != 'manufacturers_id' && isset($_GET['manufacturers_id']) && $_GET['manufacturers_id'] > 0) {
    echo zen_draw_hidden_field('manufacturers_id', $_GET['manufacturers_id']);
  }

// BEGIN CEON URI MAPPING 2 of 2
    }
// END CEON URI MAPPING 2 of 2

  // draw sort
  echo zen_draw_hidden_field('sort', $_GET['sort']);

  // draw filter_id (ie: category/mfg depending on $options)
  if ($do_filter_list) {
    echo zen_draw_pull_down_menu('filter_id', $options, (isset($_GET['filter_id']) ? $_GET['filter_id'] : ''), 'onchange="this.form.submit()"');
  }

  if (defined('PRODUCT_LISTING_LAYOUT_STYLE_CUSTOMER') and PRODUCT_LISTING_LAYOUT_STYLE_CUSTOMER == '1') {
    echo '<div id="viewControl">' . zen_draw_pull_down_menu('view', array(array('id'=>'rows','text'=>PRODUCT_LISTING_LAYOUT_ROWS),array('id'=>'columns','text'=>PRODUCT_LISTING_LAYOUT_COLUMNS)), (isset($_GET['view']) ? $_GET['view'] : (defined('PRODUCT_LISTING_LAYOUT_STYLE')? PRODUCT_LISTING_LAYOUT_STYLE: 'rows')), 'onchange="this.form.submit()"') . '</div>';
  }
  // draw alpha sorter
  require(DIR_WS_MODULES . zen_get_module_directory(FILENAME_PRODUCT_LISTING_ALPHA_SORTER));
?>
</form>
<?php
  }
?>
<br class="clearBoth" />

<?php
/**
 * require the code for listing products
 */
 require($template->get_template_dir('tpl_modules_product_listing.php', DIR_WS_TEMPLATE, $current_page_base,'templates'). '/' . 'tpl_modules_product_listing.php');
?>

<?php
// categories_description - moved by brinley
    if ($current_categories_description != '') {
?>
<div id="indexProductListCatDescription" class="content"><?php echo $current_categories_description;  ?></div>
<?php } // categories_description ?>

<?php
//// bof: categories error
if ($error_categories==true) {
  // verify lost category and reset category
  $check_category = $db->Execute("select categories_id from " . TABLE_CATEGORIES . " where categories_id='" . $cPath . "'");
  if ($check_category->RecordCount() == 0) {
    $new_products_category_id = '0';
    $cPath= '';
  }
?>

<?php
$show_display_category = $db->Execute(SQL_SHOW_PRODUCT_INFO_MISSING);

while (!$show_display_category->EOF) {
?>

<?php
  if ($show_display_category->fields['configuration_key'] == 'SHOW_PRODUCT_INFO_MISSING_FEATURED_PRODUCTS') { ?>
<?php
/**
 * display the Featured Products Center Box
 */
?>
<?php require($template->get_template_dir('tpl_modules_featured_products.php',DIR_WS_TEMPLATE, $current_page_base,'templates'). '/tpl_modules_featured_products.php'); ?>
<?php } ?>

<?php
  if ($show_display_category->fields['configuration_key'] == 'SHOW_PRODUCT_INFO_MISSING_SPECIALS_PRODUCTS') { ?>
<?php
/**
 * display the Special Products Center Box
 */
?>
<?php require($template->get_template_dir('tpl_modules_specials_default.php',DIR_WS_TEMPLATE, $current_page_base,'templates'). '/tpl_modules_specials_default.php'); ?>
<?php } ?>

Cheers
Brinley

line: 169 is

  $check_category = $db->Execute("select categories_id from " . TABLE_CATEGORIES . " where categories_id='" . $cPath . "'");  

Check your PMs

I tried entering the SQL query directly into my localhost testbed's phpMyAdmin

SELECT categories_id FROM categories WHERE categories_id='-1';

which returned an empty result, not an error. Good old line 169

  $check_category = $db->Execute("select categories_id from " . TABLE_CATEGORIES . " where categories_id='" . $cPath . "'");

appears, to my eyes, to be providing the correct formatting.

There's something else going on, but I'm not sure what ... yet.

I'm not entirely with you on this as my php skills are definitely little league.

I assumed that the coding is OK and that either CEOn or something I added was causing the issue - but lost as to how to determine what exactly

Sorry to be obtuse:blush:. Your issue is a puzzler because (1) using a properly-formatted SQL query with a categories_id of -1 (and single-quotes around the value) do not cause errors when issued directly and (2) the formatting provided by your copy of the .php file (to my eyes) looks proper.

One thing that you could try, for grins and giggles, is to make the following (temporary) change to line 169 in tpl_index_product_list.php:

  $the_query = "select categories_id from " . TABLE_CATEGORIES . " where categories_id='" . $cPath . "'";
 error_log (__FILE__ . ': ' . $the_query);
 $check_category = $db->Execute($the_query);

Replace that single line with the three above, repeat the transaction that failed, restore the code to the single-line version and then look to see what the debug-log files have to say. The error_log statement will output the failing query as part of the log's record.

Hi Lat9

Thanks for the reply .... all very mysterious.

I will quite happily amend the code as you suggested,however the problem I have is that I don't know what triggers the error. You mentioned a failed transaction, so I assume you think it is happening when adding to cart?

Cheers
Brinley