Zen Cart Logo
Forums / General Questions / 1064 You have an error in your SQL syntax ...

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

Views: 2,811

Results 1 to 8 of 8
5 Feb 2011, 4:44 PM
#1
autoace avatar

autoace

Totally Zenned

Join Date:
Jun 2009
Posts:
618
Plugin Contributions:
0

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

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

6 Feb 2011, 7:00 PM
#2
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,513
Plugin Contributions:
177

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

autoace:

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

8 Feb 2011, 2:26 AM
#3
autoace avatar

autoace

Totally Zenned

Join Date:
Jun 2009
Posts:
618
Plugin Contributions:
0

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

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

8 Feb 2011, 3:29 AM
#4
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,513
Plugin Contributions:
177

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

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?

8 Feb 2011, 1:51 PM
#5
autoace avatar

autoace

Totally Zenned

Join Date:
Jun 2009
Posts:
618
Plugin Contributions:
0

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

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

9 Feb 2011, 2:19 AM
#6
autoace avatar

autoace

Totally Zenned

Join Date:
Jun 2009
Posts:
618
Plugin Contributions:
0

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

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

9 Feb 2011, 11:35 AM
#7
drbyte avatar

drbyte

Sensei

Join Date:
Jan 2004
Posts:
63,513
Plugin Contributions:
177

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

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.

9 Feb 2011, 2:21 PM
#8
autoace avatar

autoace

Totally Zenned

Join Date:
Jun 2009
Posts:
618
Plugin Contributions:
0

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

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.