CODEWITHSUNDEEP
phpmysql
Samuel Dervis
Samuel Dervis

Reputation: 387

Select a random distinct row

I have a table in mysql called drugs with 3 columns (id,illness,drug).
An illness may occur several times in the column but with different drug.

I need a code to select a distinct ilness but it should use a random criteria so that it can display different drug.

Example in mydb... 

id | illness | drug
------------------------------
1  |malaria  |panadol    
2  |malaria  |hedex  
3  |malaria  |tripple action    
4  |fever    |panadol

i tried this but it wasn't random in picking the rows for drug. selected only a single drug for malaria all the time.

$quotes="SELECT drug,remedy FROM drugs group by drug";

i need this outputs

 1   | malaria   | panadol or hedex or tripple action **//any but random**  
 2   | fever     | panadol **//random drug if i add other rows of fever**

Upvotes: 0

Views: 90

Answers (2)

Ilesh Patel
Ilesh Patel

Reputation: 2155

Try this

$quotes="SELECT drug,remedy FROM drugs WHERE id = (1+ RAND()*(SELECT MAX(id) FROM drugs)) group by drug";

Upvotes: 1

Seti
Seti

Reputation: 2314

Or in mysql $select = 'select * from drugs order by rand() limit 1'; That will get the rows at random, and then limit answer just for one of it. You can add where clause to the query as well.

Upvotes: 1

Related Questions