display random data in php

[davids_media]

I have a page which displays an item based on a search. Below is the code.

 

<?php

error_reporting(E_ALL ^ E_NOTICE);
ini_set("display_errors", 1);

require ('includes/config.inc.php');

include ('./includes/header.html');

require (MYSQL);

include ('./includes/main.html');

if($id = isset($_GET['prodID']))
{
$query = "SELECT `prodID`, `product`, `prod_descr`, `image`, `price` FROM product WHERE `prodID`='{$_GET['prodID']}'";
$r = mysqli_query($dbc, $query);

$showHeader = true;
echo "<div id='right'>";

while($row = mysqli_fetch_array($r))
{
if($showHeader)
{
            //Display category header
echo "<h1>" . "<span>" . "# " . "</span>" . $row['product'] .  "<span>" . " #" . "</span>" . "</h1>";

echo "<div id='item'>"; // div class 'item'

echo "<div class='item_left'>";

echo "<p>";
echo $row['prod_descr'];
echo "</p>";

echo "<p>";
echo "&pound" . $row['price'];
echo "</p>";

echo "</div>";

echo "<div class='item_right'>";

echo "<img src='db/images/".$row['image']."' />";
$showHeader = false;


echo "<br />";
echo "You may also like....";
echo $row['product'];

echo "</div>";

}
}

?>

<p>
<form target="paypal" action="https://www.sandbox.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick">
<input type="hidden" name="item_name" value="<?php echo $row['product']; ?>">
<input type="hidden" name="item_number" value="<?php echo $row['prodID']; ?>">
<input type="hidden" name="amount" value="<?php echo $row['price'] ?>" />
<input type="hidden" name="currency_code" value="GBP">
<input type="hidden" name="hosted_button_id" value="7UCL9YCYYXL3J">
<input type="image" src="https://www.sandbox.paypal.com/en_US/i/btn/btn_cart_LG.gif" border="0" name="submit" alt="PayPal - The safer, easier way to pay online!">
<img alt="" border="0" src="https://www.sandbox.paypal.com/en_US/i/scr/pixel.gif" width="1" height="1">
</form>
</p>

<?php

echo "</div>"; // End of div class 'item'

echo "</div>";

}

include ('./includes/footer.html');

?>

 

What I want to achieve is just below the product content, is a feature with one or two random records which will essentially say to the user "you may also like" and display random but similar products.

 

How do I achieve this?

[FishBird]

First of all:

 

Dont ever do this:

$query = "SELECT `prodID`, `product`, `prod_descr`, `image`, `price` FROM product WHERE `prodID`='{$_GET['prodID']}'";

 

$query = "SELECT `prodID`, `product`, `prod_descr`, `image`, `price` FROM product WHERE `prodID`='".mysql_real_escape_string($_GET['prodID'])."'";

Is a lot better.

 

To get al random item from your DB, just run a second query

$query = "SELECT `prodID`, `product`, `prod_descr`, `image`, `price` FROM product ORDER BY RAND() LIMIT 2";

If you have less than 50.000 rows in your db and have proper indexes, or:

$rand = array( 
mt_rand(0, $number_of_items_in_db), mt_rand(0, $number_of_items_in_db), mt_rand(0, $number_of_items_in_db), mt_rand(0, $number_of_items_in_db), mt_rand(0, $number_of_items_in_db) );
$query = "SELECT `prodID`, `product`, `prod_descr`, `image`, `price` FROM product WHERE `prodID`= IN('".implode("'", $rand)."') LIMIT 2";

If you have more than 50.000 rows

[Jessica]

Rather than escaping a string which should be an integer value, just cast it as an int.

[FishBird]

Rather than escaping a string which should be an integer value, just cast it as an int.

 

True

 

WHERE `prodID`='".mysql_real_escape_string($_GET['prodID'])."'";

 

Should be

 

WHERE `prodID`='".(int)$_GET['prodID']."'";

 

But in this case i tried to learn him something, and how could I know prodID is an INT in the database? He may have used a string ;-)

[MMDE]

You don't need the ' when you use numbers in MySQL.

 

I actually wrote a pretty long text about how you could do this, but I imagine exactly how to do it efficient was what I didn't figure out straight away, and so it would be very abstract and maybe not useful at all. I could give it another try, because I think I just figured out how to do it way more efficient.

 

You need to find something similar with the products. I would guess this is some kind of store? If it is a store, then you keep a purchase record, and if it's not a store, you can keep a click record (log). You can at random pick one of the products, maybe some of the more popular, or the current one viewing, or just one at random. It doesn't really matter what product it is, as long as it qualifies for the next step. The next step is to check who else liked this product, or rather, who else purchased or clicked on this product, and see what else they have clicked on or purchased. So what is needed to be qualified for this step is to have any other products that people who has purchased this also have purchased, or clicked on. You can count the number of rows and get one random of those rows. You can also get the most popular out of them, or even set a random value that is lower than the amount of matches of people who has purchased that product and the other, and then only get one at random that is higher than that number. This would make the less popular products show up more rarely, but they would still show up once in a while.

 

This should be able to do with some few and very neatly thought out MySQL queries and a little PHP magic. xD

I know the hardest choices here can be done in 2 MySQL queries (probably one combined, but still in reality 2), and then use only one other function in php. I don't have the time for writing this for you, so have fun, but I hope I helped you a little even though it's somewhat abstract!