CODEWITHSUNDEEP
phpmysqlsql
Eyad Fallatah
Eyad Fallatah

Reputation: 1948

Pick random item from database

I have a database that keeps track of books. The books table looks like this:

-----------------------------------------------
   BookID   |    UserID      |     SecID
-----------------------------------------------
     66     |      1         |      3
-----------------------------------------------
     67     |      1         |      2
-----------------------------------------------
     68     |      1         |      5
-----------------------------------------------
     69     |      2         |      2
-----------------------------------------------

I want to select a random book that is owned by a specific user from an array of possible sections

For example if $userID = 1 and $arrayA = array(1,2,3); the output should be either book 66 or 67

I could have just used array_rand($arrayA, 1); and then injected whatever random number the function returns to pull the book with this sql statement

SELECT * FROM `books` WHERE userID = '1' AND typeID = '$randomSection' LIMIT 1

However, if the array_rand function returns 1, the sql statement returns nothing. How can I write a better sql statement that guarantees a result as long as the user has at least one book listed in one of the given sections?

Upvotes: 1

Views: 2093

Answers (3)

Rikesh
Rikesh

Reputation: 26431

Just a bit change in your query:

SELECT * FROM 'books' WHERE userID = '1'  order by rand() LIMIT 1

Upvotes: 3

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

select * from t1
where userid = '1' and secid in ('1', '2', '3')
order by rand()
limit 1

Of course, the 1 and (1, 2, 3) will be parameters there.

Upvotes: 1

deceze
deceze

Reputation: 522110

  SELECT *
    FROM `books`
   WHERE userID = 1
     AND typeID IN (1, 2, 3)
ORDER BY RAND()
   LIMIT 1

The ORDER BY RAND() is rather expensive if you have a lot of results matching the condition, but is inconsequential for small result sets and the simplest way to do it.

Upvotes: 3

Related Questions