Random &amp; Distinct together?

Random & Distinct together?

frostiecuk

Hello all, new to the forums and relatively new to php/mysql, struggling along slowly and surely tho!

I have a question relating to my mySQL database and wondered if someone could help me out?

Basically, I have a table called ss_offers and within this there are 8 different fields, one of them is called section. What I want is for a page to randomly pull 3 items of the database, but no more than 1 item from any one section. Is this possible?

I am trying to use the following code;
$query = "SELECT DISTINCT section FROM ss_offers ORDER BY RAND() LIMIT 0,3";

If it doesn't make sense, then I have explained a little better below.....

ss_offers is the table in my database. Within this there are 8 fields, one called sections (contains things like Food, Motor, Movie). Each entry in the database is assigned to section that it relates to, and what I want is for the page to randomly select 3 items.

At the moment it works using;
$query = "SELECT * FROM ss_offers ORDER BY RAND() LIMIT 0,3";
but this code sometimes returns 2 items where the section field = movie for example. It is this that I am trying to avoid.

Any help would really be appreciated.

Thanks

BuzzLY

You could try using GROUP BY instead... but I am not sure if the LIMIT is imposed before or after. Problem is, you might only get 1 or 2 returned, instead of 3.

$query = "SELECT section FROM ss_offers GROUP BY section ORDER BY RAND() LIMIT 0,3";

If that doesn't work, my suggestion would be to pull the whole thing using RAND() so that you get an array of 8 sections, randomly ordered, then only use the first 3 values of that array:

$query = "SELECT DISTINCT section FROM ss_offers ORDER BY RAND()";
$result = mysql_query($query) or die (mysql_error());
$numvalues = 3; // change this as needed
if ($numvalues > $mysql_num_rows($result)) $numvalues = $mysql_num_rows($result);
for ($loop = 0; $loop < $numvalues; $loop++) {
    $row = mysql_fetch_row($result);
    $array[] = $row[0];
}

I wrote the code on the fly, so it's untested. Let me know if it works. Of course, if the GROUP BY works, then the code is moot! 🙂

frostiecuk

Buzzly

Thanks a bunch. The code worked but it only extracted the section field information, so by changing it to the following it worked no problems at all;
$query = "SELECT * FROM ss_offers GROUP BY section ORDER BY RAND() LIMIT 0,3";

Many thanks for your help, am sure I will be back asking a few more questions over the coming days (and hope to offer my knowledge/advice in return, but don't expect too much!)

Thanks again
😃

frostiecuk

OK I have checked it a couple of times, and its not working as I had hoped.

At the moment, there are 21 rows within 4 sections. The random generator picks any 3 of those sections (Credit, Travel, Food or Motor), but it always picks the same row.

Would it be easier if I gave the website URL? Not sure if you should give this out or not.

Thanks

BuzzLY

Not sure what you mean... are you saying that it properly picks the random 3 sections? Or are you saying that it picks the same 3 sections every time you run it?

frostiecuk

Buzzly I have sent a PM to you with the URL that may help explain it a little better (never was one of my strong points).

Many thanks

BuzzLY

Ok, I understand you now. It is properly picking three random sections, but it always presents the same company for each section -- for example, "McDonald's" for Food.

I don't mind the PM, especially since you may not have wanted to post the URL. I'm gonna keep the conversation here, though, so others can benefit (assuming we come up with a solution 😃).

Here's what I think is happening -- bear with me. Talking through it will sometimes reveal the solution 🙂. The query I suggested is properly grouping the rows based on section, which essentially will always group them in the same order. It then randomly selects 3 of those rows. The problem is, you want order them randomly, THEN group them up by section, so that the same company isn't always available for each section.

I'm not sure if this is possible with one query. I don't know if SQL operates on it's queries sequentially, but you might try rewriting it like this:

$query = "SELECT * FROM ss_offers ORDER BY RAND() GROUP BY section LIMIT 0,3;

If that doesn't work, you may have to use 4 queries -- one to randomly select 3 sections, then one each to randomly select a row with the additional condition "WHERE section = 'whatever'". 4 queries isn't the most efficient thing, but it shouldn't kill your server.

You may wish to do the manipulations via PHP rather than SQL -- pull all of the rows with your query in random order, then simply pull each row using mysql_fetch_array until you get three different sections. I can help you write the code for any of these if you get stuck.

Good Luck!

frostiecuk

I have tried playing around and I think 4 queries or 6 are the only option. Below is what I have come up with at the moment, but still no joy:

$query1 = "SELECT section FROM ss_offers GROUP BY section ORDER BY RAND() LIMIT 0,1";
$query2 = "SELECT section FROM ss_offers GROUP BY section ORDER BY RAND() LIMIT 0,1";
$query3 = "SELECT section FROM ss_offers GROUP BY section ORDER BY RAND() LIMIT 0,1";

$result1 = mysql_query($query1)
or die ("Sorry, problem connecting to table.");
$result2 = mysql_query($query2)
or die ("Sorry, problem connecting to table.");
$result3 = mysql_query($query3)
or die ("Sorry, problem connecting to table.");

$query4 = "SELECT FROM ss_offers WHERE section='$result1' ORDER BY RAND() LIMIT 0,3";
$query5 = "SELECT FROM ss_offers WHERE section='$result2' ORDER BY RAND() LIMIT 0,3";
$query6 = "SELECT * FROM ss_offers WHERE section='$result3' ORDER BY RAND() LIMIT 0,3";

$result4 = mysql_query($query4)
or die ("Sorry, problem connecting to table.");
$result5 = mysql_query($query5)
or die ("Sorry, problem connecting to table.");
$result6 = mysql_query($query6)
or die ("Sorry, problem connecting to table.");

while ($row = mysql_fetch_array($result4))
{
extract ($row);
echo "<img src='$company_logo' width='60' height='60' alt='$company - $offer_title' border='0'>$details";
}

while ($row = mysql_fetch_array($result5))
{
extract ($row);
echo "<img src='$company_logo' width='60' height='60' alt='$company - $offer_title' border='0'>$details";
}

while ($row = mysql_fetch_array($result6))
{
extract ($row);
echo "<img src='$company_logo' width='60' height='60' alt='$company - $offer_title' border='0'>$details";
}

Thanks again for your offer of help. 😕

BuzzLY

🙂 Ok, I can't write the code completely right now, but I think the PHP extraction method (my last suggestion) would be best. However, in the code you wrote, you don't have to do 3 different queries to pull 3 random sections. In fact, your code MIGHT pull duplicate sections, which you wanted to avoid in the first place. Instead, do this:

$query = "SELECT section FROM ss_offers GROUP BY section ORDER BY RAND() LIMIT 0,3";

get your result set, grab each row, and perform the next queries on each value. If you still need help with this by the time I get home late tonight, I'll help you.

But I think the best method is to pull all of the data from the table with:

$query = "SELECT * FROM ss_offers ORDER BY RAND()";

No GROUP BY, no LIMIT. Then, using the appropriate PHP commands like mysql_fetch_array within a while loop, keep selecting rows and storing them in an array until you get three of them with different sections. If you pull a row that has a section you already have, discard it and pull the next one.

This will work fine, because the ORDER BY RAND() will change the order of rows every time it's run, so you should end up with a truly random sampling.

Try your hand at the code for this -- I'll be back in about 9 hours to see if you have had any luck.

Good Luck!

frostiecuk

Buzzly, I have it now as follows;

$query = "SELECT * FROM ss_offers ORDER BY RAND()";
$result = mysql_query($query)
or die ("Sorry, problem connecting to table.");

$numvalues = 3;

if ($numvalues > mysql_num_rows($result)) $numvalues = mysql_num_rows($result);

for ($loop = 0; $loop < $numvalues; $loop++)

{
$row = mysql_fetch_row($result);
$array[] = $row[0];

echo "<img src='$company_logo' width='60' height='60' alt='$company - $offer_title' border='0'>$details";

}

It brings up 3 layouts, but no data is being extracted or populated onto the webpage, such as $company_logo or $details. The only thing I changed from what you wrote above was $mysql_num_rows to mysql_num_rows (without the leading $). Are we almost there?

BuzzLY

🙂 Almost there... but think about it. If you use $row = mysql_query_row($query);, it will return an array of values called $row[0], $row[1], etc... not "$company_logo". Also, if you restrict your loop to 3, you might get duplicate sections. You would be right back where you were using LIMIT 0,3!

Try this instead (your columns might have different names -- change them accordingly, ok?):

$query = "SELECT * FROM ss_offers ORDER BY RAND()"; 
$result = mysql_query($query) 
or die ("Sorry, problem connecting to table: " . mysql_error());

$num_rows = 3; // max number of unique rows we want to return

$a_sections = array(); // to be used for checking purposes
$a_data = array(); // to be used to hold our data

while (($array = mysql_fetch_array($result)) && (count($a_sections) < $num_rows)) {
	if (!in_array($array['section'], $a_sections)) {
		$a_sections[] = $array['section'];
		$a_data[] = $array;
	}
}

// Now, $a_data should contain all the data we need.  We access
// the data by referring to $a_data[x]['column_name']
// to view the array's keys and values, uncomment the next line:
// print_r($a_data);

$total_rows = count($a_data);

for ($loop = 0; $loop < $total_rows; $loop++) {
	$company_logo = $a_data[$loop]['company_logo'];
	$company = $a_data[$loop]['company'];
	$offer_title = $a_data[$loop]['offer_title'];
	$details = $a_data[$loop]['details'];

echo "<img src='$company_logo' width='60' height='60' ";
echo "alt='$company - $offer_title' border='0'>$details"; 
echo "<br><br>";
}

This is untested... hopefully I didn't write any bugs here. The only thing I am not completely sure about is the while statement. I think the logic is correct, but not 100%. Anyway, test it out, and let me know if it works. I am assuming that the echoed HTML statements will be just a LITTLE bit more involved...?

If you need me to explain any part of the code, let me know, but it should be pretty self-explanatory. Make sure you don't just use the code as is -- it's important that you understand how/why it works.

frostiecuk

Still getting an error;
Fatal error: Call to undefined function: inarray() in index.php on line 129

It is failing here;
--> while (($array = mysql_fetch_array($result)) && (count($a_sections) < $num_rows)) {
if (!inarray($array['section'], $a_sections)) {
$a_sections[] = $array['section'];
$a_data[] = $array;
}
}

😕

frostiecuk

Sorry, it is failing here;

if (!inarray($array['section'], $a_sections)) {

BuzzLY

Whoops. That should have been "in_array". I have fixed the code in the original.

A couple of points, Frostie...

First of all, I really don't mind helping you out with your code and your problem, but do try to do a little troubleshooting and RTFMing, ok? I know PHP isn't your strong suit, but I think you could have figured out the problem with "inarray", since the error told you that inarray() was an invalid function 😉

Second, when listing code in your posts, please use vBulletin's

 tag.  It makes the code much easier to read:

[code=php]
<?php
//code would go here
?>
[/ PHP] (without the space)

Now... replace inarray() with in_array() in your code, and let's see if this bugger works! :D

frostiecuk

OK, it now works - many thanks. As for RTFMing, believe be I am planning on doing this over the coming weeks, and also attending a course (unfortunalely work will not pay for it). I have never used the array function before so wasn't soo sure what I was looking for.

Many thanks again for help out so much.

BuzzLY

Sweet... is it implemented? I'd like to see it in action 🙂

[edit] Ah, I see that it is. Make sure you use align=left and hspace=5 (or so). Other than that, looks good. You may want to post the url here so that others can see it in action, too... I'm sure jstarkey won't have a problem with that 😃[/edit]