php to display all mysql records, shows only one row

Question

php to display all mysql records, shows only one row. Please can anyone help me with this?

PHP CODE:

require_once('dbConnect.php');

$sql = "SELECT vendorID,joint_name,vendor_contact,vendor_name,vendor_place,joint_lat,joint_long,vendor_profile_pic,time_in,time_out,days_in,date FROM vendor ORDER BY vendorID desc ";

$r = mysqli_query($con,$sql);

$res = mysqli_fetch_array($r);

$result = array();

array_push($result,array(
                "joint_name"=>$res['joint_name'],
                "vendor_contact"=>$res['vendor_contact'],
                "vendor_name"=>$res['vendor_name'],
                "vendor_place"=>$res['vendor_place'],
                "joint_lat"=>$res['joint_lat'],
                "joint_long"=>$res['joint_long'],
                "vendor_profile_pic"=>$res['vendor_profile_pic'],
                "time_in"=>$res['time_in'],
                "Ttime_out"=>$res['time_out'],
                "days_in"=>$res['days_in'],
                "date"=>$res['date']                

         )
         );

echo json_encode(array("result"=>$result));
mysqli_close($con);

"
RESULT:
"
{"result":[{"joint_name":null,"vendor_contact":null,"vendor_name":null,"vendor_place":null,"joint_lat":null,"joint_long":null,"vendor_profile_pic":null,"time_in":null,"Ttime_out":null,"days_in":null,"date":null}]}

Possible duplicate of [Php MySQL not displaying all rows](http://stackoverflow.com/questions/26340523/php-mysql-not-displaying-all-rows) — Jocelyn, Jul 22 '16 at 11:04

score 0 · Answer 1 · answered Jul 22 '16 at 11:00

Try This

while($res = mysqli_fetch_array($r)){
    array_push($result,array(
            "joint_name"=>$res['joint_name'],
            "vendor_contact"=>$res['vendor_contact'],
            "vendor_name"=>$res['vendor_name'],
            "vendor_place"=>$res['vendor_place'],
            "joint_lat"=>$res['joint_lat'],
            "joint_long"=>$res['joint_long'],
            "vendor_profile_pic"=>$res['vendor_profile_pic'],
            "time_in"=>$res['time_in'],
            "Ttime_out"=>$res['time_out'],
            "days_in"=>$res['days_in'],
            "date"=>$res['date']                

     )
     );
}

If my answer helped you please tick as right answer so other can also take benefit of it. — Vinie, Jul 22 '16 at 11:25

score 0 · Answer 2 · answered Jul 22 '16 at 11:03

That's because you're executing mysqli_fetch_array function only once. Put it in a while loop to fetch as many rows as result-set have.

You can put it into while loop to get all the rows from table.

Like this,

while($res = mysqli_fetch_array($r)){
      array_push($result,array(
                "joint_name"=>$res['joint_name'],
                "vendor_contact"=>$res['vendor_contact'],
                "vendor_name"=>$res['vendor_name'],
                "vendor_place"=>$res['vendor_place'],
                "joint_lat"=>$res['joint_lat'],
                "joint_long"=>$res['joint_long'],
                "vendor_profile_pic"=>$res['vendor_profile_pic'],
                "time_in"=>$res['time_in'],
                "Ttime_out"=>$res['time_out'],
                "days_in"=>$res['days_in'],
                "date"=>$res['date']                

         )
         );
}

$result will have all the rows from result-set.

mysqli_fetch_array() returns an array that corresponds to the fetched row or NULL if there are no more rows for the resultset represented by the result parameter.

So the while loop stops working as soon as there are no more rows to fetch from result-set.

score 0 · Answer 3 · answered Jul 22 '16 at 11:15

You can get all results in an array with mysqli_fetch_all. You also don't need to copy the results manually. Your code then simplifies to:

require_once('dbConnect.php');
$sql = "SELECT vendorID,joint_name,vendor_contact,vendor_name,vendor_place,joint_lat,joint_long,vendor_profile_pic,time_in,time_out,days_in,date FROM vendor ORDER BY vendorID desc ";
$r = mysqli_query($con,$sql);
echo json_encode("result" => mysqli_fetch_all($r));

Of course, you should do error checking.

As a side note, you should not let dbConnect.php define the global variable $con. Better provide a factory function.

php to display all mysql records, shows only one row

3 Answers3