How to fetch value from Database in Random order using MySQL and PHP

Question

I need one help.i need to fetch value from table in Random order at each time of call using MySQL and PHP.I am explaining my code below.

$subcat_id=$_GET['subcat_id'];
$quad_id=$_GET['quad_id'];
$day_id=$_GET['day_id'];
$data=array();
if(!empty($quad_id) && !empty($day_id)){
$sqlqry=mysqli_query($connect,"select * from db_restaurant_detail where subcat_id='".$subcat_id."' and day_id='".$day_id."'");
if(mysqli_num_rows($sqlqry) > 0){
    while($row=mysqli_fetch_array($sqlqry)){
        $member_id=$row['member_id'];
        $qry="SELECT c.member_id,c.rest_name,c.quadrant,c.city,c.proviance,c.postal,c.address,c.country,c.person,c.mobile,c.url,c.  premium,c.image,c.multiple_image,c.business_phone_no,q.quadrant AS quadrant_name FROM db_restaurant_basic AS c LEFT JOIN db_quadrant AS q ON c.quadrant=q.quad_id WHERE c.member_id='".$member_id."' and c.quadrant='".$quad_id."' and c.status=1";
        $fetchqry=mysqli_query($connect,$qry);
        if(mysqli_num_rows($fetchqry) > 0){
         while($row1=mysqli_fetch_array($fetchqry)){
                      if($row1['multiple_image']==''){
                          $available_image=false;
                      }else{
                           $available_image=true;
                      }
                      $data[]=array("day_id"=>$row['day_id'],"comment"=>$row['comment'],"restaurant_name"=>$row1['rest_name'],"member_id"=>$row1['member_id'],"available_image"=>$available_image,"quadrant"=>$row1['quadrant_name'],"address"=>$row1['address'],"city"=>$row1['city'],"proviance"=>$row1['proviance'],"postal_code"=>$row1['postal'],"country"=>$row1['country'],"person"=>$row1['person'],"mobile"=>$row1['mobile'],"url"=>$row1['url'],"premium"=>$row1['premium'],"image"=>$row1['image'],"business_phone_no"=>$row1['business_phone_no']);
                  }
                  $result=array("data"=>$data,"imagepath"=>$imagepath);
                  }else{
                     $result=array("data"=>$data,"imagepath"=>$imagepath); 
                  }
              }
            }else{
                $result=array("data"=>$data,"imagepath"=>$imagepath);
            }

When user is invoking this localhost/spesh/mobileapi/categoryproduct.php?item=1&acn=2&subcat_id=4&quad_id=5 the above code is executing.Here I need when user is calling that url each time the required data should come randomly not in a asc or desc order.Please help me.

on a sidenote: please make your code more readable - I can't even think about working with code formatted like yours! — low_rents, Apr 20 '16 at 06:52
I agree with @low_rents, please make the code more readable. You'll get more people answering you that way. Good for you, good for our eyes, good for our long lives... now that was a master rhyme. — Webeng, Apr 20 '16 at 06:55
@Webeng yeah - seems like many PHP beginners are afraid of using line breaks in their code. you can use a line break almost anywhere in your PHP code as long as you don't use them inside words (e.g. variables) or operators. you have got the semicolon (`;`) to mark the end of a command in PHP! — low_rents, Apr 20 '16 at 07:01
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Jun 01 '17 at 12:30

score 3 · Answer 1 · answered Apr 20 '16 at 06:52

3

Use

ORDER BY rand()

This will generate random results.

MySQL Reference

answered Apr 20 '16 at 06:52

Pupil

23,834
6
44
66

i used this but no changes at each time of call. – Apr 20 '16 at 06:56
@ Pupil : i used like this `$qry="SELECT c.member_id,c.rest_name,c.quadrant,c.city,c.proviance,c.postal,c.address,c.country,c.person,c.mobile,c.url,c. premium,c.image,c.multiple_image,c.business_phone_no,q.quadrant AS quadrant_name FROM db_restaurant_basic AS c LEFT JOIN db_quadrant AS q ON c.quadrant=q.quad_id WHERE c.member_id='".$member_id."' and c.quadrant='".$quad_id."' and c.status=1 ORDER BY rand()";`.but its not changing any order in each time of call that url. – Apr 20 '16 at 07:02
Please check how many records you have in database. Also, print the query and execute in MySQL client like PHPMyAdmin. The syntax of `ORDER BY RAND()` is perfect and it must work. – Pupil Apr 20 '16 at 07:03

score 1 · Answer 2 · answered Apr 20 '16 at 06:53

1

You can add "ORDER BY rand() " in your query. So it will fetch random data.

answered Apr 20 '16 at 06:53

Dipanwita Kundu

1,637
1
9
14

score 1 · Answer 3 · answered Apr 20 '16 at 06:54

1

Yes

ORDER BY rand()

It will generate random results

answered Apr 20 '16 at 06:54

Ravi Kumar

443
3
10

How to fetch value from Database in Random order using MySQL and PHP

3 Answers3