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 :)
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).
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.
SELECT id
FROM PRODUCT
WHERE URL_TITLE != "coffee"
ORDER BY RAND()
LIMIT 1;
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
