1064 You have an error in your SQL syntax ...

[autoace]

Might anyone be able to tell me what would cause this?:

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 'order by p.products_sort_order, pd.products_name limit 10' at line 19
in:
[SELECT DISTINCT p.products_image, m.manufacturers_name, p.products_model, p.products_quantity, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (products p LEFT JOIN manufacturers m USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c ) LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 1 WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND ((pd.products_name LIKE '%k333320312%' OR p.products_model LIKE '%k333320312%' OR m.manufacturers_name LIKE '%k333320312%' OR (mtpd.metatags_keywords LIKE '%k333320312%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%k333320312%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%k333320312%') )) order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name limit 10]

It happened after I searched for this Model number, k333320312

[DrByte]

... order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name limit 10]

SQL syntax doesn't allow 2 mentions of an "order by" clause in the same statement.

[autoace]

Ok, can you tell me what file that would be in so I can correct it. Not even sure how it got that got there...

[DrByte]

That's an assembled query that's built based on numerous criteria and logic according to store settings etc.
Have you altered any of the files in the /includes/index_filters/ folder? Maybe you've got a rogue addon that's not working properly?

[autoace]

Ok, thanks for that. Now I know where to look.

[autoace]

@ DrByte

Ok, I ran the Enable Error Logging script from the free addons section and it gave me this result:

PHP Warning: sort() expects parameter 1 to be array, null given in /xxxxxxx/xxxxxxxx/public_html/includes/templates/xxxxxxx/sideboxes/tpl_search_vehicle_sidebox.php on line 226

Here is that entire file:

<?php
/**
 * blank sidebox - allows a blank sidebox to be added to your site
 *
 * @package templateSystem
 * @copyright 2007 Kuroi Web Design
  * @copyright Portions Copyright 2003-2007 Zen Cart Development Team
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: blank_sidebox.php 2007-05-26 kuroi $
 */
 
//$content = (($_SESSION['product']) ? 'You selected:<br />' . $_SESSION['product'] . '<br />': '') . (($_SESSION['make']) ? $_SESSION['make'] . '<br />': '') . (($_SESSION['model']) ? $_SESSION['model'] . '<br />': '') . (($_SESSION['exac_model']) ? $_SESSION['exac_model'] . '<br />': '') . (($_SESSION['engine']) ? $_SESSION['engine'] . '<br /><a href="index.php?main_page='.FILENAME_ADVANCED_SEARCH_RESULT.'&product='. $_SESSION['product'] .'&make='. $_SESSION['make'] .'&model='. $_SESSION['model'] .'&exac_model='. $_SESSION['pexac_model'] .'&engine='. $_SESSION['engine'] .'">View products</a>': '');

$content = '';

  $content .= '
    <script type="text/javascript">
        var url = "./getmake.php?id=";
        var url2 = "./getmodel.php?id=";
        var url3 = "./getexac_model.php?id=";
        var url4 = "./getengine.php?id=";
    var prod3 = "./getproducts.php?id=";
    
       function handleHttpResponse() {    
        if (http.readyState == 4) {
              if(http.status==200) {
                  var results=http.responseText;
              document.getElementById(\'make\').innerHTML = results;
              document.getElementById(\'load1\').innerHTML = "";
              }
              }
        }

       function handleHttpResponse2() {    
        if (http.readyState == 4) {
              if(http.status==200) {
                  var results=http.responseText;
              document.getElementById(\'model\').innerHTML = results;
              document.getElementById(\'load2\').innerHTML = "";
              }
              }
        }
       function handleHttpResponse3() {    
        if (http.readyState == 4) {
              if(http.status==200) {
                  var results=http.responseText;
              document.getElementById(\'exac_model\').innerHTML = results;
              document.getElementById(\'load3\').innerHTML = "";
              }
              }
        }
       function handleHttpResponse5() {    
        if (http.readyState == 4) {
              if(http.status==200) {
                  var results=http.responseText;
              document.getElementById(\'engine\').innerHTML = results;
              document.getElementById(\'load5\').innerHTML = "";
              }
              }
        }
       function handleHttpResponse4() {    
        if (http.readyState == 4) {
              if(http.status==200) {
                  var results=http.responseText;
              document.getElementById(\'products\').innerHTML = results;
              document.getElementById(\'load4\').innerHTML = "";
              document.getElementById(\'products2\').innerHTML = "";
              }
              }
        }
        
        function requestCustomerInfo() {      
            var free_product = document.getElementById("product").value;
            var free_make = document.getElementById("make2").value;
            var free_model = document.getElementById("model2").value;
            var free_exac_model = document.getElementById("exac_model2").value;
        document.getElementById(\'engine\').innerHTML = \'<select name="engine" style="width:200px;" id="engine2"><option value="null">Engine</option></select>\';
        if(free_product!="free" && free_make!="free" && free_model!="free" && free_exac_model!="free"){

        document.getElementById(\'engine\').innerHTML = \'<select name="engine" style="width:200px;" id="engine2"><option value="null">Engine</option></select>\';
        document.getElementById(\'exac_model\').innerHTML = \'<select name="exac_model" style="width:200px;" id="exac_model2"><option value="null">Submodel</option></select>\';
        document.getElementById(\'model\').innerHTML = \'<select name="model" style="width:200px;" id="model2"><option value="null">Model</option></select>\';
        document.getElementById(\'make\').innerHTML = \'<select name="make" style="width:200px;" id="make2"><option value="null">Make</option></select>\';
        }
        
            var sId = document.getElementById("product").value;
//            document.getElementById(\'load1\').innerHTML = \'<img src="./xxxxxx/xxxxxxx/xxxxxxxx/images/loading2.gif" border="0">\';
            http.open("GET", url + escape(sId), true);
            http.onreadystatechange = handleHttpResponse;
            http.send(null);
            }
        
        function requestCustomerInfo2() {      
            var sId = document.getElementById("product").value;
            var sId2 = document.getElementById("make2").value;
//            document.getElementById(\'load2\').innerHTML = \'<img src="./xxxxxx/xxxxxxx/xxxxxxxx/images/loading2.gif" border="0">\';
            http.open("GET", url2 + escape(sId) + "&id2=" + escape(sId2), true);
            http.onreadystatechange = handleHttpResponse2;
            http.send(null);
            }
        function requestCustomerInfo3() {      
            var sId = document.getElementById("product").value;
            var sId2 = document.getElementById("make2").value;
            var sId3 = document.getElementById("model2").value;
//            document.getElementById(\'load3\').innerHTML = \'<img src="./xxxxxx/xxxxxxx/xxxxxxxx/images/loading2.gif" border="0">\';
            http.open("GET", url3 + escape(sId) + "&id2=" + escape(sId2) + "&id3=" + escape(sId3), true);
            http.onreadystatechange = handleHttpResponse3;
            http.send(null);
            }
        function requestCustomerInfo4() {      
            var sId = document.getElementById("product").value;
            var sId2 = document.getElementById("make2").value;
            var sId3 = document.getElementById("model2").value;
            var sId4 = document.getElementById("exac_model2").value;
//            document.getElementById(\'load5\').innerHTML = \'<img src="./xxxxxx/xxxxxxx/xxxxxxxx/images/loading2.gif" border="0">\';
            http.open("GET", url4 + escape(sId) + "&id2=" + escape(sId2) + "&id3=" + escape(sId3) + "&id4=" + escape(sId4), true);
            http.onreadystatechange = handleHttpResponse5;
            http.send(null);
            }
        function requestproducts3() {      
            var sId = document.getElementById("make").value;
            var sId2 = document.getElementById("model2").value;
            var sId3 = document.getElementById("year2").value;
            var sId4 = document.getElementById("product2").value;
            var sId5 = document.getElementById("engine2").value;
        
//            document.getElementById(\'load4\').innerHTML = \'<img src="./xxxxxx/xxxxxxx/xxxxxxxx/images/loading2.gif" border="0">\';
            http.open("GET", prod3 + escape(sId) + "&id2=" + escape(sId2) + "&id3=" + escape(sId3) + "&id4=" + escape(sId4) + "&id4=" + escape(sId4), true);
            http.onreadystatechange = handleHttpResponse4;
            http.send(null);
            }

        
function getHTTPObject() {
  var xmlhttp;
 
  if(window.XMLHttpRequest){
    xmlhttp = new XMLHttpRequest();
  }
  else if (window.ActiveXObject){
    xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
    if (!xmlhttp){
        xmlhttp=new ActiveXObject("Msxml2.XMLHTTP");
    }
    
}
  return xmlhttp;

  
}
var http = getHTTPObject(); // We create the HTTP Object
</script>
  ';
  $content .= '<div id="' . str_replace('_', '-', $box_id . 'Content') . '" class="sideBoxContent">';


  $content .= '<div id="' . str_replace('_', '-', $box_id . 'Content') . '">';

$content .= '<div style="text-align:left; padding:0px;margin:0px;">
<form name="search_results" action="./index.php" method="get">
<input type="hidden" name="main_page" value="'.FILENAME_ADVANCED_SEARCH_RESULT.'">
<div class="" style="padding-right:0px;">
<div><span id="load1"></span></div>
<font id="pawel_product">
<select name="product" onchange="requestCustomerInfo()" style="width:200px;" id="product">
<option value="free">select year...</option>';

$sql = "select distinct se_product  from ". TABLE_SEARCH_ENGINE_MAKE ."
                           where se_product > '0'
               order by se_product ASC";
$result=mysql_query($sql);
$nazwa_product1 = '';
$p ='0';
while ($row=mysql_fetch_array($result)){

$product=$row["se_product"];

$array_product = explode(',', $product);

//print_r($array_product);

if($k=='0'){
$array_product_final = $array_product;
$size_of_final = sizeof($array_product_final);
}

for ($i=0; $i<sizeof($array_product); $i++) {

if($p=='0'){
$add='yes';
}
else{
$add='';
for ($j=0; $j<sizeof($array_product_final); $j++) {

if($array_product[$i]==$array_product_final[$j]){
$add='no';
}
}

if($add=='no'){
}
else{
$add='yes';
}

}


if($add=='yes'){

if($p<=$size_of_final){
}
else {
$array_product_final[] = $array_product[$i];
}

}

}

$p++;
}

//print_r($array_product_final);
sort($array_product_final);// THIS IS LINE 226
for ($w=0; $w<sizeof($array_product_final); $w++) {

$content .= '<option value="' . $array_product_final[$w] .'" '. (($_SESSION['product']==$array_product_final[$w]) ?  'selected' . '': '')  .'>'. $array_product_final[$w]. $add_year.'</option>' . "\n";

}

//$make=$row["se_product"];
//$content .= '<option value="' . $make .'">'. $make. '</option>' . "\n";

$content .= '</select>
</font>
</div>

<div style="padding-right:0px;">
<div><span id="load2"></span></div>
<font id="make">
<select name="make" style="width:200px;" id="make2">
<option value="free">'. (($_SESSION['make']) ?  $_SESSION['make'] . '': 'select make...') .'</option>
</select>
</font>

</div>

<div style="padding-right:0px;">
<div><span id="load3"></span></div>
<font id="model">
<select name="model" style="width:200px;" id="model2">
<option value="free">'. (($_SESSION['model']) ?  $_SESSION['model'] . '': 'select model...') .'</option>
</select>
</font>

</div>


<div style="padding-right:0px;">
<div><span id="load4"></span></div>
<font id="exac_model">
<select name="exac_model" style="width:200px;" id="exac_model2">
<option value="free">'. (($_SESSION['exac_model']) ?  $_SESSION['exac_model'] . '': 'select submodel...') .'</option>
</select>
</font>
</div>
<br class="clearBoth" />

<div style="padding-right:0px;">
<div><span id="load5"></span></div>
<font id="engine">
<select name="engine" style="width:200px;" id="engine2">
<option value="free">'. (($_SESSION['engine']) ?  $_SESSION['engine'] . '': 'select engine...') .'</option>
</select>
</font>
</div>
<br class="clearBoth" />

</form>
</div>' . (($_SESSION['engine']) ? '<div id="viewProducts"><a href="index.php?main_page='.FILENAME_ADVANCED_SEARCH_RESULT.'&product='. $_SESSION['product'] .'&make='. $_SESSION['make'] .'&model='. $_SESSION['model'] .'&exac_model='. $_SESSION['exac_model'] .'&engine='. $_SESSION['engine'] .'"><font style="color:#fff;">Search</font></a></div>': '');//<div id="ymmeSearch">Search</div>


  $content .= '</div>';

  $content .= '</div>';
?>

I marked line 226 with a comment in ALL CAPS. Its towards the bottom, about 3/4 of the way down. Can you please point out to me what the problem is? I've been troubleshooting for the past few hours and just can not figure it out.

[DrByte]

You're passing the sort() the name of a variable that's showing as null ... ie: has no value, most likely has never been set at all by the time that line is executed.

[autoace]

Ok, thanks -very helpful. I think I know what the problem is. I have a test site set up and everything is working fine, including the search. So I've been comparing settings and files. Now I believe I know what is wrong.