Fixing a SQL statement

[mallen]

Is there anything here in my code that could cause my search not to work. I checked and double checked my table names are correct. I tried removing the ` but didn't work.

$searchQuery = $_REQUEST['query'];

$src = "SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_mainImage, prod.prod_model, prod.catalog FROM `myproducts` as prod LEFT JOIN category_assoc AS assoc ON assoc.prod_id = prod.prod_id LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id WHERE LOWER (prod.prod_name) LIKE '% ".$searchQuery ." %' OR LOWER (prod.prod_description) LIKE '% ".$searchQuery ."%' OR LOWER(prod.prod_model) LIKE '% ".$searchQuery ."%')  AND prod.prod_active = '1' ORDER BY prod.prod_model ASC";

[Jessica]

What does "doesn't work" mean?

You only posted 2 lines of code so we can't tell. Does the query run or fail? Does it work in mysql directly or not? Did you echo the query to see if the search term is filled in?

[mallen]

Yes I did an echo on the search and it displays the search entered. I tried the SQL in PHPMyadmin and I get #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version.

What I mean by it does not work, it returns "Your search returned no results.  Please try refining your search"

 

$searchQuery = $_REQUEST['query'];

$src = "SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_mainImage, prod.prod_model, prod.catalog FROM `myproducts` as prod LEFT JOIN category_assoc AS assoc ON assoc.prod_id = prod.prod_id LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id WHERE LOWER (prod.prod_name) LIKE '% ".$searchQuery ." %' OR LOWER (prod.prod_description) LIKE '% ".$searchQuery ."%' OR LOWER(prod.prod_model) LIKE '% ".$searchQuery ."%')  AND prod.prod_active = '1' ORDER BY prod.prod_model ASC";

echo $searchQuery;

$results = $wpdb->get_results($src, ARRAY_A);



	if(count($results) > 0)

	{

		foreach($results as $res)

		{



		<div class="productRow">



			<div  class='thumbs'>



<img src='<?php echo $res['prod.prod_mainImage'];?>' alt='MainThumbnail'/></div>

<h2><a href='?page_id=26&singleProduct=<?php echo $res['prod.prod_id'];?>'><?php echo $res['prod.prod_name']; ?></a></h2><br />
<p style='margin:2px; 0 15px;'><strong>Product Code :</strong> <?php echo $res['prod.prod_model'];?></p><br />

<div class='click'>

<a href='?page=<?php echo $res['prod.prod_id'];?>'>Click to view</a>

</div></div>

	<?php	

		}

	}

	else

		echo "<p>Your search returned no results.  Please try refining your search</p>\n"; 



else:

	echo "<p>Please enter a query in the search box at the top of the page</p>\n";



	endif;

?>

[mikosiko]

well... the message that you are getting... "I tried the SQL in PHPMyadmin and I get #1064 - You have an error in your SQL syntax" should give you an idea about were to look for the problem, if you can't see the problem at least you should always work with php's error_reporting set to E_ALL (or better a -1) during development, and  display_errors should be ON; on a production server display_errors should be OFF and log_errors should be ON; you can do those changes in your php.ini file.  Another simple way to debug your code is echoing your sql sentence and look for evident errors on it (you have one in yours)

[mallen]

Thanks. I echoed the sql sentence and looked for errors but all I am doing is printing the line I see already typed. The error in Phpmyadmin just quotes the beginning of the entire statement and not the specific error. Can you give me a hint as to my error you see? Is it a typo, a space? The order?

[Jessica]

Read this please: http://forums.phpfreaks.com/index.php?topic=365029.msg1730174#msg1730174

[PFMaBiSmAd]

I wish you would post the complete and actual mysql error you got. What looks like the start of the sql statement to you, might give us a clue as to what is causing the error.

[mallen]

It's working now. I see the issue was  '% ".$searchQuery ." %    and changed it to '$searchQuery'

$src="SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_description,prod.prod_mainImage, prod.prod_model, catalog, prod_active FROM `products` as prod LEFT JOIN `category_assoc` AS assoc ON assoc.prod_id = prod.prod_id LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id WHERE prod.prod_name LIKE '$searchQuery' OR prod.prod_description LIKE '$searchQuery' OR prod.prod_model LIKE '$searchQuery' AND prod.prod_active = '1' ORDER BY prod_model ASC "  ;

[mikosiko]

the original sentence that you posted:

$src = "SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_mainImage, prod.prod_model, prod.catalog FROM `myproducts` as prod LEFT JOIN category_assoc AS assoc ON assoc.prod_id = prod.prod_id LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id WHERE LOWER (prod.prod_name) LIKE '% ".$searchQuery ." %' OR LOWER (prod.prod_description) LIKE '% ".$searchQuery ."%' OR LOWER(prod.prod_model) LIKE '% ".$searchQuery ."%')  AND prod.prod_active = '1' ORDER BY prod.prod_model ASC"

 

as an orphan closing parenthesis ... most likely you want the opening one after the WHERE ...