Randomise strings in mysql

Question

I've a string in mysql that i want to randomise so that every time i run the query, i get different results.

The sample string

$str = '4_127','2_84','2_85';

So, i will pass it to the ORDER BY clause to randomise results.

SELECT `MY_SEARCH_PARAMS` FROM `mytable` WHERE `MY_WHERE_CONDTIONS` ORDER BY
FIELD( CONCAT( property_id,"_",catalog_id ), '4_127','2_84','2_85' ), `id` ASC;

I need a way so that the order of the string contents is changed everytime.

Thanks.

Is $str = '4_127','2_84','2_85'; the actual string or you have tree strings which are 4_127, 2_84 and 2_85? — Álvaro González
– Álvaro González, Commented Feb 8, 2013 at 14:00
do you have ` '4_127','2_84','2_85'` values in your table??? — jcho360
– jcho360, Commented Feb 8, 2013 at 14:12
@jcho360 No, they are the values of concating... property_id catalog_id as shown in the query. 4_127: 4 is property_id, 127 is catalog_id and so on... — web-nomad
– web-nomad, Commented Feb 8, 2013 at 14:15
what you are asking doesn't make sense in MySQL, you are trying to order by 3 items that don't belong to the Table, I guess it would be better do it with help of another programming language like PHP — jcho360
– jcho360, Commented Feb 8, 2013 at 14:21

Lothar · Accepted Answer · 2013-02-08 13:54:34Z

1

Really? That's very confusing behaviour. What are you trying to achieve?

Nevertheless even if you use code like this it's at least better to randomise them in the php script (I am assuming you are using php).

$str_array = array('4_127','2_84','2_85');
shuffle($str_array);
$str = implode(", ",$str_array)

answered Feb 8, 2013 at 13:54

Lothar

5271 gold badge5 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

web-nomad Over a year ago

Confusing it is, sure. But that's how projects change :) Yes, if nothing else, i might do it in PHP.

Lothar Over a year ago

Why do you want that to be handled by MySQL ?

web-nomad Over a year ago

There are quite a few other fields/conditions that depend on it.

Collectives™ on Stack Overflow

Randomise strings in mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related