Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #16635 > unrolled thread

Keeping search results random, but same in MySQL

Back to article view | Back to comp.lang.php

Contents

  Keeping search results random, but same in MySQL bit-naughty@hotmail.com - 2016-03-14 22:29 -0700
    Re: Keeping search results random, but same in MySQL Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2016-03-15 17:16 +1100
      Re: Keeping search results random, but same in MySQL Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-15 18:58 +0100
        Re: Keeping search results random, but same in MySQL Jerry Stuckle <jstucklex@attglobal.net> - 2016-03-15 14:40 -0400
        Re: Keeping search results random, but same in MySQL Geoff Muldoon <geoff.muldoon@trap.gmail.com> - 2016-03-16 13:56 +1100
          Re: Keeping search results random, but same in MySQL Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2016-03-16 19:56 +0100
    Re: Keeping search results random, but same in MySQL gordonb.paboe@burditt.org (Gordon Burditt) - 2016-03-15 01:19 -0500
    Re: Keeping search results random, but same in MySQL Arno Welzel <usenet@arnowelzel.de> - 2016-03-15 09:02 +0100
    Re: Keeping search results random, but same in MySQL Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2016-03-15 13:03 -0400
      Re: Keeping search results random, but same in MySQL bit-naughty@hotmail.com - 2016-03-16 10:26 -0700

#16635 — Keeping search results random, but same in MySQL

....if I have a table on which I'm doing a MySQL query, for which I want the search results in a *random* order, but the *same order everytime I do the search* (i.e. say it's a "SELECT name FROM table WHERE thingbought='oranges' "; ), what do I do? Does that make sense? If a row gets added to "table" which also matches that query, then it should add that to the search results , at the end probably (but I *don't want the results sorted by time*). 
I'm probably a bit confused, but I hope you guys get the gist of my question.


Thanks.

[toc] | [next] | [standalone]

#16636

On Mon, 14 Mar 2016 22:29:55 -0700 (PDT), bit-naughty@hotmail.com wrote:

> ....if I have a table on which I'm doing a MySQL query, for which I want the search results in a *random* order, but the *same order everytime I do the search* (i.e. say it's a "SELECT name FROM table WHERE thingbought='oranges' "; ), what do I do? Does that make sense? If a row gets added to "table" which also matches that query, then it should add that to the search results , at the end probably (but I *don't want the results sorted by time*). 
> I'm probably a bit confused, but I hope you guys get the gist of my question.

Abandon all hope.  

Databases do not do "random but fixed" ordering.  They either order
ascending/descending according to some stipulated attribute, or do not
order at all.  

If you don't set an ORDER BY you will have no control over the randomness
of the return data provided from different executions of the same query.

GM

[toc] | [prev] | [next] | [standalone]

#16646

Geoff Muldoon wrote:

> Abandon all hope.
> 
> Databases do not do "random but fixed" ordering.  They either order
> ascending/descending according to some stipulated attribute, or do not
> order at all.

First of all, it depends on whether there are indexes on the table.
Second, MySQL’s ORDER BY supports expressions.
Third, this has nothing to do with PHP.
 
> If you don't set an ORDER BY you will have no control over the randomness
> of the return data provided from different executions of the same query.

Incorrect.

-- 
PointedEars
Zend Certified PHP Engineer 
<http://www.zend.com/en/yellow-pages/ZEND024953> | Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#16647

On 3/15/2016 1:58 PM, the well-known troll Thomas 'Pointed Head' Lahn wrote:
> Geoff Muldoon wrote:
> 
>> Abandon all hope.
>>
>> Databases do not do "random but fixed" ordering.  They either order
>> ascending/descending according to some stipulated attribute, or do not
>> order at all.
> 
> First of all, it depends on whether there are indexes on the table.

Incorrect.  The presence of absence of indexes has nothing to do with
the order in which the rows are retrieved.

> Second, MySQL’s ORDER BY supports expressions.

True.

> Third, this has nothing to do with PHP.

True.

>  
>> If you don't set an ORDER BY you will have no control over the randomness
>> of the return data provided from different executions of the same query.
> 
> Incorrect.
> 

His statement is correct.  Without an ORDER BY clause you have no
control over the order in which the data is returned.

But this is much more appropriate in comp.databases.mysql, where it is
on topic.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#16649

On Tue, 15 Mar 2016 18:58:41 +0100, Thomas 'PointedEars' Lahn wrote:

> Geoff Muldoon wrote:
> 
>> Abandon all hope.
>> 
>> Databases do not do "random but fixed" ordering.  They either order
>> ascending/descending according to some stipulated attribute, or do not
>> order at all.
> 
> First of all, it depends on whether there are indexes on the table.

Irrelevant.

> Second, MySQL’s ORDER BY supports expressions.

It certainly does, but what does that have to do with "fixed randomness"?

> Third, this has nothing to do with PHP.

Yep, should have been asked in the appropriate RDBMS group.

>> If you don't set an ORDER BY you will have no control over the randomness
>> of the return data provided from different executions of the same query.
> 
> Incorrect.

Say why?

GM

[toc] | [prev] | [next] | [standalone]

#16654

Geoff Muldoon wrote:

> On Tue, 15 Mar 2016 18:58:41 +0100, Thomas 'PointedEars' Lahn wrote:
>> Geoff Muldoon wrote:
>>> Abandon all hope.
>>> 
>>> Databases do not do "random but fixed" ordering.  They either order
>>> ascending/descending according to some stipulated attribute, or do not
>>> order at all.
>> 
>> First of all, it depends on whether there are indexes on the table.
> 
> Irrelevant.

No, apparently InnoDb tables, as opposed to MyISAM tables, are sorted 
according to the last defined PRIMARY KEY, INDEX or UNIQUE index in MySQL 
5.6.25-3 on Debian GNU/Linux at least.  That is, the table records appear to 
be rewritten in ascending index order when the index is defined, as the 
order of the rows is not in the previous insertion order even after the 
relevant index has been deleted.
 
>> Second, MySQL’s ORDER BY supports expressions.
> 
> It certainly does, but what does that have to do with "fixed randomness"?

You can use RAND(…) in the expression. see also Lew Pitcher’s follow-up 
<news:0gXFy.9304$ln5.73@fx39.iad>.

>>> If you don't set an ORDER BY you will have no control over the
>>> randomness of the return data provided from different executions of the
>>> same query.
>> Incorrect.
> 
> Say why?

I misread.  You are probably correct there as I cannot think of a way other 
than “ORDER BY … RAND(…) …” to achieve "fixed randomness".

-- 
PointedEars
Zend Certified PHP Engineer 
<http://www.zend.com/en/yellow-pages/ZEND024953> | Twitter: @PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#16637

> ....if I have a table on which I'm doing a MySQL query, for which
> I want the search results in a *random* order, but the *same order
> everytime I do the search* (i.e. say it's a "SELECT name FROM table
> WHERE thingbought='oranges' "; ), what do I do? Does that make
> sense? 

Not really.

> If a row gets added to "table" which also matches that query,
> then it should add that to the search results , at the end probably
> (but I *don't want the results sorted by time*).

> I'm probably a bit confused, but I hope you guys get the gist of my question.

For how long do you want the results to be the same?  You might,
for example, repopulate a random column with different random numbers
a week after it was first used.

One way to accomplish what I think you asked for, but not necessarily
what you meant, is to add randNNNNN columns to each table, one for
each existing query and maybe a few thousand columns for future
queries.  Each query might end with "ORDER BY rand57628", making
sure to use each "rand" column for only one query.

You would also need to either modify all your queries that insert
records to populate the random columns, or use a trigger to do that.
You can depend on the order (usually) changing if you delete a
selected record, then re-insert it.

I'm not sure how you deal with random orders of rows derived from
joins with a whole bunch of tables, unless there's a primary key
for the result, which is unlikely.

[toc] | [prev] | [next] | [standalone]

#16638

bit-naughty@hotmail.com schrieb am 2016-03-15 um 06:29:

> ....if I have a table on which I'm doing a MySQL query, for which I
> want the search results in a *random* order, but the *same order
> everytime I do the search* (i.e. say it's a "SELECT name FROM table
> WHERE thingbought='oranges' "; ), what do I do? Does that make sense?

If you want sorted results, you have to sort them, usually by using
ORDER BY in your query.

But was has this to do with PHP?


-- 
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de
http://fahrradzukunft.de

[toc] | [prev] | [next] | [standalone]

#16644

On Tuesday March 15 2016 01:29, in comp.lang.php, "bit-naughty@hotmail.com"
<bit-naughty@hotmail.com> wrote:

> ....if I have a table on which I'm doing a MySQL query, for which I want the
> search results in a *random* order, but the *same order everytime I do the
> search* (i.e. say it's a "SELECT name FROM table WHERE thingbought='oranges'
> "; ), what do I do? Does that make sense? If a row gets added to "table"
> which also matches that query, then it should add that to the search results
> , at the end probably (but I *don't want the results sorted by time*). I'm
> probably a bit confused, but I hope you guys get the gist of my question.

You can use the MySQL mathematical function rand() in the ORDER BY clause.
While the values returned will be "random", they will be reproducible if you
use the same seed value for rand() on each query.

So, your query would change to
  SELECT name FROM table WHERE thingbought='oranges' ORDER BY RAND(10) ASC;

Example:
mysql> select LEFT(recipe_name,25), CURRENT_TIME from recipe order by rand(10)
asc;
+---------------------------+--------------+
| LEFT(recipe_name,25)      | CURRENT_TIME |
+---------------------------+--------------+
| Cedar Planked Salmon      | 13:02:22     | 
| All-in-one Roasted Vegeta | 13:02:22     | 
| Slow-Cooker BBQ Short Rib | 13:02:22     | 
| Beef and Corn Bake        | 13:02:22     | 
| Irish Coffee              | 13:02:22     | 
| Coconut Creme Brulee      | 13:02:22     | 
| Pavlova                   | 13:02:22     | 
| Rockin Dry Rub            | 13:02:22     | 
| Bran Muffins              | 13:02:22     | 
| Teriyaki Sauce            | 13:02:22     | 
| Salmon Dry Rub Seasoning  | 13:02:22     | 
| Pulled Beef               | 13:02:22     | 
| aaaaaaaaaaaaaaaaaaaaaaaaa | 13:02:22     | 
| Apple-Raisin Pancakes     | 13:02:22     | 
| Broccoli Pasta Casserole  | 13:02:22     | 
| Coffee Rubbed Steak       | 13:02:22     | 
| Crepes                    | 13:02:22     | 
| Teriyaki Roast            | 13:02:22     | 
| Basic White Bread - 1.5 l | 13:02:22     | 
| Sweet and Sour Meatloaf ( | 13:02:22     | 
| Coffee Rub                | 13:02:22     | 
| Egg Salad                 | 13:02:22     | 
| Slow-cooker BBQ Pork Sand | 13:02:22     | 
| Barbecued Spare Ribs      | 13:02:22     | 
| Country Spinach Salad     | 13:02:22     | 
+---------------------------+--------------+
25 rows in set (0.00 sec)

mysql> select LEFT(recipe_name,25), CURRENT_TIME from recipe order by rand(10)
asc;
+---------------------------+--------------+
| LEFT(recipe_name,25)      | CURRENT_TIME |
+---------------------------+--------------+
| Cedar Planked Salmon      | 13:02:26     | 
| All-in-one Roasted Vegeta | 13:02:26     | 
| Slow-Cooker BBQ Short Rib | 13:02:26     | 
| Beef and Corn Bake        | 13:02:26     | 
| Irish Coffee              | 13:02:26     | 
| Coconut Creme Brulee      | 13:02:26     | 
| Pavlova                   | 13:02:26     | 
| Rockin Dry Rub            | 13:02:26     | 
| Bran Muffins              | 13:02:26     | 
| Teriyaki Sauce            | 13:02:26     | 
| Salmon Dry Rub Seasoning  | 13:02:26     | 
| Pulled Beef               | 13:02:26     | 
| aaaaaaaaaaaaaaaaaaaaaaaaa | 13:02:26     | 
| Apple-Raisin Pancakes     | 13:02:26     | 
| Broccoli Pasta Casserole  | 13:02:26     | 
| Coffee Rubbed Steak       | 13:02:26     | 
| Crepes                    | 13:02:26     | 
| Teriyaki Roast            | 13:02:26     | 
| Basic White Bread - 1.5 l | 13:02:26     | 
| Sweet and Sour Meatloaf ( | 13:02:26     | 
| Coffee Rub                | 13:02:26     | 
| Egg Salad                 | 13:02:26     | 
| Slow-cooker BBQ Pork Sand | 13:02:26     | 
| Barbecued Spare Ribs      | 13:02:26     | 
| Country Spinach Salad     | 13:02:26     | 
+---------------------------+--------------+
25 rows in set (0.00 sec)

mysql> select LEFT(recipe_name,25), CURRENT_TIME from recipe;
+---------------------------+--------------+
| LEFT(recipe_name,25)      | CURRENT_TIME |
+---------------------------+--------------+
| aaaaaaaaaaaaaaaaaaaaaaaaa | 13:03:00     | 
| All-in-one Roasted Vegeta | 13:03:00     | 
| Apple-Raisin Pancakes     | 13:03:00     | 
| Barbecued Spare Ribs      | 13:03:00     | 
| Basic White Bread - 1.5 l | 13:03:00     | 
| Beef and Corn Bake        | 13:03:00     | 
| Bran Muffins              | 13:03:00     | 
| Broccoli Pasta Casserole  | 13:03:00     | 
| Cedar Planked Salmon      | 13:03:00     | 
| Coconut Creme Brulee      | 13:03:00     | 
| Coffee Rub                | 13:03:00     | 
| Coffee Rubbed Steak       | 13:03:00     | 
| Country Spinach Salad     | 13:03:00     | 
| Crepes                    | 13:03:00     | 
| Egg Salad                 | 13:03:00     | 
| Irish Coffee              | 13:03:00     | 
| Pavlova                   | 13:03:00     | 
| Pulled Beef               | 13:03:00     | 
| Rockin Dry Rub            | 13:03:00     | 
| Salmon Dry Rub Seasoning  | 13:03:00     | 
| Slow-cooker BBQ Pork Sand | 13:03:00     | 
| Slow-Cooker BBQ Short Rib | 13:03:00     | 
| Sweet and Sour Meatloaf ( | 13:03:00     | 
| Teriyaki Roast            | 13:03:00     | 
| Teriyaki Sauce            | 13:03:00     | 
+---------------------------+--------------+
25 rows in set (0.00 sec)



-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

[toc] | [prev] | [next] | [standalone]

#16652

Thanks Lew!

That's awesome!! :)

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web