mallen Posted September 17, 2012 Share Posted September 17, 2012 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"; Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 17, 2012 Share Posted September 17, 2012 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? Quote Link to comment Share on other sites More sharing options...
mallen Posted September 17, 2012 Author Share Posted September 17, 2012 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; ?> Quote Link to comment Share on other sites More sharing options...
mikosiko Posted September 17, 2012 Share Posted September 17, 2012 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) Quote Link to comment Share on other sites More sharing options...
mallen Posted September 17, 2012 Author Share Posted September 17, 2012 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? Quote Link to comment Share on other sites More sharing options...
Jessica Posted September 17, 2012 Share Posted September 17, 2012 Read this please: http://forums.phpfreaks.com/index.php?topic=365029.msg1730174#msg1730174 Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted September 17, 2012 Share Posted September 17, 2012 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. Quote Link to comment Share on other sites More sharing options...
mallen Posted September 17, 2012 Author Share Posted September 17, 2012 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 " ; Quote Link to comment Share on other sites More sharing options...
mikosiko Posted September 17, 2012 Share Posted September 17, 2012 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 ... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.