php mysql query returns multiple rows, save data to object

Question

I am trying to extract data from a mysql query that returns multiple rows. The code is currently coded up to handle only one row. What do I need to do to save the multiple rows of data into the instance variables of the object Date?

<?php
session_start();
require 'database.php';

class Date {
    private $id = '';
    private $titl = '';
    private $tim = '';
    private $dat = '';
    private $usern = '';

}

if ($_SESSION['loginstatus']!=1){
    die;
}

$username=$_SESSION['username'];
$date=$_POST['dateChosen'];

$stmt = $mysqli ->prepare("select ID, title, time from events where date=? and creator=?");    
if (!$stmt){
    $error = $mysqli->error;
    $string="Query Prep Failed:" . $error; 
    echo json_encode(array(
        "message"=> $string));
    exit;
}


$stmt -> bind_param('ss',$date,$username);
$stmt -> execute();
$stmt ->bind_result($ID, $title,$time);
$count = 0;
while ($stmt->fetch()){
    $count += 1;

    echo json_encode(array("id"=>$ID,
        "title"=> $title, "time"=>$time));
}
if ($count == 0){
    echo json_encode(array(
        "title"=> "NOT SET", "time"=>"NOT SET"));
}
$stmt->close();
header("Content-Type: application/json");
?>

Marc B · Accepted Answer · 2015-07-16 17:21:49Z

1

You're creating multiple separate/independent JSON strings, which is incorrect. You need to build an array in your loop, then encode the array ONCE, after the loop completes.

e.g.

$data = array();
while($stmt->fetch()) {
   $data[] = array('id' => $ID, 'title' => etc....);
}
echo json_encode($data);

Right now you're producing

{"id":1,"title":"foo",...}{"id":2,"title":"bar",...}{etc...}

which is a syntax error. It should be

[{"id":1,"title":"foo",...},{"id":2,"title":"bar",...},{etc...}]

answered Jul 16, 2015 at 17:21

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Osuwariboy · Accepted Answer · 2015-07-16 17:21:20Z

0

change your while loop:

while($res = $stmt->fetch())
{
    //do whatever, data is in the $res variable
}

answered Jul 16, 2015 at 17:21

Osuwariboy

1,3851 gold badge14 silver badges29 bronze badges

Collectives™ on Stack Overflow

php mysql query returns multiple rows, save data to object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related