CODEWITHSUNDEEP
phpmysql
Miomir Dancevic
Miomir Dancevic

Reputation: 6852

Mysql query one random?

I have issuse with displaying random item from table, is there way to display just one random item, but not the item i preffered to not to be displayed?

I have table PRODUCT

and rows

ID TITLE URL_TITLE

and

$link = "coffee";

I want to display random just one product from table PRODUCT, but not the the same i have in $link

What i want to say i just want random item from table, but not when in $link="coffee" to get that random element

p.s $link in in URL_TITLE row :)

Upvotes: 0

Views: 79

Answers (4)

vroomfondel
vroomfondel

Reputation: 3106

This should help:

SELECT ID, TITLE, URL_TITLE 
FROM PRODUCT 
WHERE URL_TITLE != "coffee" 
ORDER BY RAND() 
LIMIT 1;

Note that in some versions of SQL, != is written <>.

Obviously, you want to select all rows or a different subset, just use SELECT * or whatever you need.

Edit: as per HamZa's comment and James' answer, using ORDER BY RAND() is bad practice on large tables. You could potentially generate a random ID and select that, checking that it's not your illegal one, but if you have a whole bunch that you can't select, you could potentially call this query a ton of times (which is bad).

Upvotes: 3

James
James

Reputation: 4803

Using rand() in queries is not ideal, especially in large tables, but even small ones as you never know when they'll grow (ie site or service exceeds your expectations etc).

You can use it like this:

select item from product where item != '$link' ORDER BY rand() LIMIT 1

But you should use something better, like temp tables, or you could select all the IDs from the database in one query, use PHP to select a random one from them all, then use the ID selected by PHP and grab the data from the database.

Upvotes: 1

Barranka
Barranka

Reputation: 21067

Although I don't know PHP, I think a simple way to do it is to pass the $link value as part of a where condition.

Using 'pure' SQL:

select *
from product
where URL_TITLE <> 'coffee'
order by rand()
limit 1

Upvotes: 0

jterry
jterry

Reputation: 6269

SELECT id
FROM PRODUCT
WHERE URL_TITLE != "coffee"
ORDER BY RAND()
LIMIT 1;

Upvotes: 0

Related Questions