Select multiple lines with PHP JSON

Question

I’m using SELECT to collect multiple lines from my database.

Using the query format below I have the expected result.

while ($this->result = $this->pQuery->fetch(PDO::FETCH_ASSOC)) {
    $this->json[] = $this->result;
}

outworking:

[
    {
        "evaluate_rq_id" : "10",
        "evaluate_rq_user_id" :"5", 
        "evaluate_rq_client" : "[email protected]",
        "evaluate_rq_comment" : "Por favor, nos avalie.", 
        "evaluate_rq_date" : "2014-12-27 22:21:23"
    },
    {
        "evaluate_rq_id" : "11",
        "evaluate_rq_user_id" : "5",
        "evaluate_rq_client" : "[email protected]",
        "evaluate_rq_comment" : "Por favor, nos avalie.",
        "evaluate_rq_date" : "2014-12-27 22:23:33"
    },
    { 
        "evaluate_rq_id" : "12", 
        "evaluate_rq_user_id" : "5", 
        "evaluate_rq_client" :"[email protected]",
        "evaluate_rq_comment" : "Por favor, nos avalie.",
        "evaluate_rq_date" : "2014-12-28 10:45:05"
    }

]

Using this format I have only 1 return line.

while($this->result = $this->pQuery->fetchAll(PDO::FETCH_ASSOC)) {
    foreach($this->result as $row) {
        $this->json['evaluate_rq_id']      = $row['evaluate_rq_id'];
        $this->json['evaluate_rq_user_id'] = $row['evaluate_rq_user_id'];
        $this->json['evaluate_rq_client']  = $row['evaluate_rq_client'];
        $this->json['evaluate_rq_comment'] = $row['evaluate_rq_comment'];
        $this->json['evaluate_rq_date']    = $row['evaluate_rq_date'];
    }   
}

outworking:

{
    "evaluate_rq_id": "12",
    "evaluate_rq_user_id": "5",
    "evaluate_rq_client": "[email protected]",
    "evaluate_rq_comment": "Por favor, nos avalie.",
    "evaluate_rq_date": "2014-12-28 10:45:05"
}

My idea in using the second format is to configure the field date for the format d-m-Y before generating the result in the format JSON, as below:

$this->json['evaluate_rq_date'] = date("d-m-Y",  strtotime($row['evaluate_rq_date']));

Any idea how to generate multiple lines using the second format?

Answer 1

The second format also traverses all the lines, however, appears only the last one because in every loop you are assigning new value to the variables.

Using this would already be enough to format the date as you want and the content of $this->json will be all lines:

while($this->result = $this->pQuery->fetchAll(PDO::FETCH_ASSOC)) {
    $this->result['evaluate_rq_date'] = date("d-m-Y",  strtotime($row['evaluate_rq_date']));
    $this->json[] = $this->result;
}

Answer 2

The second way is doing correctly what you want.

The problem is when extracting the data from the database, as you are extracting all at once instead of one by one.

Try the following change in the second method:

Of:

while($this->result = $this->pQuery->fetchAll(PDO::FETCH_ASSOC)) {

To:

while($this->result = $this->pQuery->fetch(PDO::FETCH_ASSOC)) {

This way you will extract each row from the bank correctly.