How to make a custom search?

**OopsIbrickedIT** · 19 May 2019, 01:45 PM

ZC v150(production,v1.5.5f testing), PHP 5.6

I am trying to make a custom search where query keywords comes from ajax- form.

I don't know much of sql or php so consider below as a sort of pseudo code. Main target is to achieve following functionality:

When customer searches with product number or free query text or both search queries following: Product description,Product name,Product model, Product metadata description,Product metadata keywords,Product metadata title. Reason for this is that we have lots of data also in metadata fields and possibly soon in custom fields.

Output contains search results with product image and price included. I aim also integrate this in the future to product template to show related products.

Code:

<?php
require 'includes/application_top.php';
$keyword = $_POST['pn'];
global $db;

if ($_SERVER['REQUEST_METHOD'] == 'POST') { 
		if (isset($_POST['pn']) === true && empty($_POST['pn']) === false) {
		$sql = 'select * from ' . TABLE_PRODUCTS . ' where products_model like :modelID:';
		$sql = $db->bindVars($sql, ':modelID:', '%' . $theProductId . '%', 'string');
		$result = $db->Execute($sql);
		echo '<head> <script src="magictesti.js"></script> <!-- load our javascript file --></head>';
		 // Below 1 attempt
		
		$sql = "$db->Execute(SELECT FROM TABLE_PRODUCTS.products_name, TABLE_PRODUCTS.products_description, 'modelID' as type FROM TABLE_PRODUCTS WHERE products_model LIKE '%" . 
           $keyword . "%' OR products_description LIKE '%" . $keyword ."%') 
          UNION
           (SELECT metatags_keywords, metatags_title, 'metadata' as type FROM meta_tags_products_description WHERE metatags_keywords LIKE '%" . 
           $keyword . "%' OR metatags_title LIKE '%" . $keyword ."%')";

echo $sql;
// 1 attempt end	   
// Below 2 attempt	   
		   $sql = $db->Execute("SELECT * FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON(p.products_id = pd.products_id)" WHERE products_description LIKE '%" . $keyword ."%');
$items = array();
while(!$sql->EOF) {
    $items[] = $sql->fields;
    $sql->MoveNext();
}

// now let's output it
foreach($items as $item) {
    echo '<p><a href="index.php?main_page=product_info&products_id='. $item['products_id'] .'"><img src="images/'. $item['products_image'].'" alt="'. $item['products_name'].'" title="'. $items['products_name'].'" /></a>';
    echo $items['products_price'] . '</p>';
}
// 2 attempt end	   
		   
	/*	   
		   
		 $result = $sql;
		 
// 3rd attempt Begin
	//	   $result = mysql_query($sql);	
		
		
		
	echo '<table class="prodtable" id="prodtable tr">';
		echo '<tr class="tbhr"><th width=\'15%\'>PN:</th><th width=\'35%\'> Desc</th><th width=\'5%\'>&nbsp;</th></tr>';
		if ($result->RecordCount() > 0) {
			while (!$result->EOF) {
				echo '<tr>';
				echo '<td>';
			    echo '<p>' . $result->fields['products_model'].'</p>';
			  	echo '</td>';
				echo '<td>';
				//	$sql = 'select * from ' . TABLE_PRODUCTS_DESCRIPTION . ' where products_id = :productID:';
				//	$sql = $db->bindVars($sql, ':productID:', $result->fields['products_id'] , 'string');
				//	$result1 = $db->Execute($sql);
				echo $result->fields['products_description'];
					echo $result '<br>';
					//echo $result->fields['metatags_keywords'];
				echo '</td>';
				echo '</tr>';
			  $result->MoveNext();
			}
		echo '</table>';
		} else {
			echo 'Sorry, no record found for product number ' . $theProductId;
		}
		} else {
			echo 'Please enter a valid part number';
		}
}

}
}
*/
// 3rd attempt end
?>

So if anyone has any ideas how to get this working? Work in progress