Why are two separate MySQL statements duplicating each other?

Question

I've got a require file that I use to display random products. On the home page it gets used twice, once in the main content section and then again in the sidebar.

The problem I'm having is the second require doesn't overwrite the first sql results, it adds to them? The results I'm actually getting for the 2nd set of results are the 1st set plus the 2nd.

Here's the first bit of code I use to select the products:

SELECT id FROM prods ORDER BY RAND() LIMIT 10

Then this uses those to fill out the rows:

SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM prods WHERE id IN ('.$sqlIn.') GROUP BY name ORDER BY RAND()

I'm sure I've overlooked something but can't think what it is.

Answer 1

I believe the SELECT should be without the *. Also, why are you ordering by RAND() in the query as well.

SELECT MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM prods 
WHERE id IN ('.$sqlIn.') GROUP BY name

---

In the first query, you are doing ORDER BY RAND() - this is awfully slow - do you absolutely need to do this? Google for other solutions of picking random rows from a table without shooting the server down.

Answer 2

Are you assigning the two queries/results to the same variable? If that is the case the resulting PHP array will append any new results. Try assigning to either a different variable, or using unset($results).

Answer 3

There's not enough information in your question to provide any real help.

What variable are you storing the results in? Are you running the SQL statement twice and thus loading the variable twice? According to PHP include/require documentation:

( http://php.net/manual/en/function.include.php )

When a file is included, the code it contains inherits the variable scope of the line on which the include occurs. Any variables available at that line in the calling file will be available within the called file, from that point forward. However, all functions and classes defined in the included file have the global scope.

If that is your problem, can you put an IF check around the variable so that you only load it once? i.e.

if (isset($myResults))
{
  load
}
// else do nothing because already loaded in the first require