Random similar results using PDO

[Albos]

Hello all,

 

I am currently pulling my hair out about this and looking at it i can not really see what i am doing wrong, i think mainly i have been looking at this too long and it may help to have a fresh pair of eyes to look over it.

 

This code should pull out 3 products that have similar keywords to the current product you are looking at, this works without any major errors but its not working as i had hoped it would.

 

It pulls out the first 3 results from the database that it finds and 9 times out of 10 will always display the current product that you are viewing which is not what i had wanted.

 

My end result would need be 3 random results from the database that have similar keywords to the current product being viewed (preferred if it pulled maybe 3 random results each time you view the page). Also would need to block pulling the currently viewed product as well.

 

I have tried WHERE id != '$productid'.... but this does not seem to work and ends up blocking a lot of results coming out for some reason, i am not sure where this would have be in my SQL statement.

 

I tried to use the shuffle function on my PDO query but this throws an error, i could not find any predefined PDO functions to shuffle results, would anyone know how to do this at all?

 

Many Thanks and Regards

Albos

 

<?php

                /**
	 * Create a PDO Connection
	 */
	$dbh = new PDO(PDO Connection Information);

	/**
	 * Set the error reporting attributes
	 */
	$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	$similarsql = $this->generateSimilarSQL($keywords, $productid);

	$sql = ("$similarsql");

	$query = $dbh->query($sql);

	/**
	 * Cycle through the results and print each one out
	 */
	foreach ($query as $row ){

		$filext = $this->fileTypeConvert($row["filetype"]);

		echo '<div class="simthumbs">
			<a href="viewproduct.php?id='.$row["id"].'" title="" alt=""><img src="products/thumbnails/'.$row["id"].'_thumb.'.$filext.'" alt="" title="" /></a><br />
			<span class="title">'.$row["code"].' '.$row["title"].'</span><br />
			</div>';

	}

?>

 

<?php
protected function generateSimilarSQL($keywords, $productid){

	$keywords = explode(", ", $keywords);

	$str = "SELECT * FROM tbl_products WHERE keywords LIKE '%fubar%' ";
	$strend = " LIMIT 3";

	foreach($keywords as $v){

		$wstr = @$wstr."OR ";
		$wstr = $wstr."keywords LIKE '%$v%' ";

	}

	return $str.$wstr.$strend;

}
?>

[fenway]

Are you getting the right results? I only see a single "LIKE".

[Albos]

Yes i get the right results, i have added a rand() which works but i am still getting the current product i am looking at, but WHERE id != '$productid'

 

The rest of the LIKE statements are in the foreach loop which cycles through each keyword.

 

The only problem i am having now is to not display the current product you are viewing in the results that get thrown out

 

<?php

$keywords = explode(", ", $keywords);

	$str = "SELECT * FROM tbl_products WHERE id != '$productid' AND keywords LIKE '%fubar%' ";
	$strend = " ORDER BY RAND() LIMIT 3";

	foreach($keywords as $v){

		$wstr = @$wstr."OR ";
		$wstr = $wstr."keywords LIKE '%$v%' ";

	}

	return $str.$wstr.$strend;

?>

[fenway]

The only problem i am having now is to not display the current product you are viewing in the results that get thrown out

Huh?

[xtopolis]

He's doing a "recommended products" type thing where it finds products similar to what you're looking at based on the name.  He doesn't want the product you're looking at to be displayed in the 3 random recommendations since you're already looking at that product.

 

I suggest: reversing the order of the conditions in WHERE.

..WHERE keywords LIKE '%fubar%' AND id != '$productid'

[aschk]

Where are these "keywords" coming from exactly?

It seems to me that the current product being viewed contains one of the keywords in question.

i.e. product is "sony tv", and you've listed it with keywords "sony".

If you do a keyword search for "sony" it's going to come back ;-)

 

But i also suggest your schema is wrong if you're storing all the keywords in 1 field for each row, LIKE '%blah%' is highly inefficient.

 

Also to answer your question regarding limiting the results that don't contain the productid. Try:

id <> '$productid'

Also, echo the sql statement (fully formed) to see what it's actually running, and is the productid an integer or a string?