[ create a new paste ] login | about

Link: http://codepad.org/FKDgo7ph    [ raw code | fork ]

C, pasted on Mar 8:
<?php
try 
{
	$dbh = new PDO('mysql:host=localhost;dbname=xxxx', 'xxxxx', 'xxxxx');
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$dbh->setAttribute(PDO::ATTR_PERSISTENT, true);
	$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
	$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
}
catch (PDOException $e) 
{
	print "Error!: " . $e->getMessage() . "<br/>";
	die;
}

//these are the details for the current product
$product_id = 1;//the current product has the pk 1
$product_name = 'Lorem ipsum dolor sit amet'; // and the title lorem ipsum dolor sit amet

//let's split the words found in this title.. you can improve on this if you need to
$words = explode(' ', $product_name);

$search_conditions = array();//prepare an array for search conditions
$score = array();//prepare an array for the search score

foreach ($words as $key=>$word)
{
	$search_conditions[] = ' product_name LIKE "%' . $word . '%" ';//add each word as an alternate search condition 
	$score[] = 'IF (product_name LIKE "%' . $word . '%", 1, 0)'; // add 1 "point" for each word matched
}

//add points to get a final score
$score = '( ' . implode('+', $score) . ') as score ';

// join word conditions by and OR.. and make sure that the current product is not part of the results 
$search_conditions = '(' . implode (' OR ', $search_conditions) . ') AND product_id <> ' . $product_id;

///create the query.. using the score.. ordering by it..
$query = '
			SELECT 
				*,
				'.$score.' 
			FROM 
				products 
			WHERE 
				' . $search_conditions . '
			ORDER BY 
				score DESC 
			LIMIT 
				4 
';
//you can adjust the limit the way you want.. 

try
{
	$stmt = $dbh->query($query);//run the query
}
catch(PDOException $e)
{
	echo $e->getMessage();
	die;
}

//prepare an array for the possible suggestions
$suggestions = array();
$min = strlen($product_name); //this is the maximum difference
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
	$suggestions[$row['product_id']] = $row;//save the details for this product
	$difference = levenshtein($row['product_name'], $product_name);//get this value to know the similarity between the two titles
	$suggestions[$row['product_id']]['difference'] = $difference;//save this value
	if ($difference < $min)
	{
		$min = $difference;//update the minimum
	}
}

//suggest products
echo 'suggested products: <br />';
foreach ($suggestions as $key=>$value)
{
	if ($value['difference'] == $min)//the current product has the best similarity
	{
		echo '<pre>';
		print_r($value);
		echo '</pre>';
	}
}


Create a new paste based on this one


Comments: