Selecting random records for use on a web page

Question

HI Guys,

I own a website I have a section called "mobile Section" this section contain many catagories (Nameoftheobject + a picture + little description and a download link) Now the section is considered as an internal section. I want to view random records from that section on the first page (I dont know Like RSS but not Rss) showing the name of the object and to which subcategories it belong and its picture if possible. I hope any one understand what I'm trying to say.

This is my website HERE and this the link of the internal page HERE check the block that say Mobile Section Its kinda a mess. is there any script or a tech to view it in a proper way to view it show me examples if possible

I'm using php and MySQL

Thanx in advance

Answer 1

Do you really want it to be random? Its nice to have most popular on home pages and might turn out to be more useful and informative for your visitors.

That said, it would be really easy to write a method in Microsoft SQL to do so:

SELECT TOP 1 Name, Picture, Description, Link
FROM Software
WHERE Category = 'Mobile Section'
ORDER BY NEWID()

In case you are not using Microsoft SQL, here is a site with random selectors for many databases.

Answer 2

This idea sort of steals from David Thomas Garcia. What you could do is in your database implement a field that would keep track of views of a product (or you could eschew this and just keep track of purchases) and you can select the items from your database as "Top Sellers" or "Hot Products" to then display on your site.

Answer 3

Whatever you do, don't use ORDER BY RAND() in MySQL. It won't scale. Yes, it will seem to work fine for a small number of rows because the data set is small. ORDER BY RAND() requires a full-table scan, which means that the larger your table is the longer the query will take. This will manifest itself like "Gee, the website seems to be slower"...and slower...and slower...

If you've got an id column that happens to be an integer and acts as a primary key, you could generate the maximum value in PHP before querying it'll be much faster because you're actually using an index.

Example could be something like:

$random_row_id = rand(1, $max);

and then...

SELECT * FROM foo WHERE id = $random_row_id

You'll have to figure out how to get your $max, but most likely you can just grab the last row id.

One word of caution here is that if you've deleted records from the table you'll have "holes" where you may ask for a row that doesn't exist anymore. In this case you could just keep probing until you find a match -- it'll still be way faster than a full table scan.