StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm assuming you have a database table containing information about these ads. You could add, if you don't already have it, a <code>views</code> field to that table. Then, whenever you need to display an ad, you just grab the one with the lowest view count, add 1 to it's view counter, and display the ad.</p> <p>Edit:</p> <p>The problem with @MarvinLabs solution, as I explain in the comments, is that it's giving a huge bonus to a single record.</p> <p>Let's say you have 50 separate ads in your system. Let's also say that your highest scoring record has a score of 9.9/10, and your second highest scoring record has a score of 9.8/10. Both of these are very high scoring items, but if you use @MarvinLabs code, the highest scoring record will get 30% of all views, while the second highest scoring record will get 1.4% of all views (70 percent of all views divided across the 49 non-highest scoring ads).</p> <p>What you might want to consider is allowing for a larger range of high scoring ads to be included. You can do this in any one of three ways:</p> <p>First, you can set a threshold, or multiple thresholds, which divide a certain percentage of views to certain ranges of scores. For example, you could have it so that ads which score more than 9/10 get 30% of all views. You would do that like this:</p> <pre><code>$random = rand(1,100); if ($random > 30) { $sql = "SELECT * FROM ads WHERE score >= 9 ORDER BY views ASC"; } else { $sql = "SELECT * FROM ads WHERE score < 9 ORDER BY views ASC"; } </code></pre> <p>The problem with this is that if you don't have any ads with a score above 9, you won't get anything back. For that reason, you probably don't want to use this method.</p> <p>Second, you could spread your 30% of views across the top 5 or 10 ads:</p> <pre><code>SELECT * FROM ads WHERE id IN (SELECT id FROM ads ORDER BY score DESC LIMIT 10) ORDER BY views ASC; </code></pre> <p>This solves the problem of "what if I don't have any records above the threshold" while still spreading the "high score bonus" across more than just a single record. The problem with this, if you consider it a problem that is, is that it doesn't scale with the volume of ads you have on record. Whether you have 10 records or 10,000 records, you'll still give the bonus to just 10 (or 20, or 50.. whatever you set) records.</p> <p>If you want to scale, you'll want the third solution.</p> <p>The third solution is to set your limit based on a percentage of the total number of records in the table. Since MySQL doesn't have a built-in way of handling this, you'll need to workaround this in one of two ways:</p> <p>One way to do it the lazy way and run two queries - one to get the current record count, and another to create a query based on it. For example:</p> <pre><code>$query1 = "SELECT COUNT(*) FROM ads"; //store result in $count $percentage = round($count * 0.10); //get 10% of records $query2 = "SELECT * FROM ads WHERE id IN " . "(SELECT id " . " FROM ads " . " ORDER BY score DESC " . " LIMIT " . $percentage . ") " . "ORDER BY views ASC" </code></pre> <p>A better way would be to avoid the second round-trip to the database and use a prepared statement:</p> <pre><code>SELECT @percentage := ROUND(COUNT(*) * 10/100) FROM ads; PREPARE PERCENTAGE FROM SELECT * FROM ads WHERE id IN (SELECT id FROM ads ORDER BY score DESC LIMIT ?) ORDER BY views ASC; EXECUTE PERCENTAGE USING @percentage; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload