GEOFFREY MWANGI GEOFFREY MWANGI - 7 months ago 30
PHP Question

Outputting concatenated data from mysql to json as json object

This is my database:

enter image description here

Am trying to get all from_id which to_id is 4 to a json object where ill be able to show all messages from a from_id in a stack sorted by their different time_sent

I have tried:

CODE:
DATABASE QUERY CLASS(THIS IS IN THE DBASE CLASS)

public function query($sql){

$this->_last_query = $sql;
$result = mysqli_query($this->_conndb, $sql);
$this->displayQuery($result);
return $result;

} // end of query

public function displayQuery($result){
if(!$result){
$output = "database query failed :". mysqli_error($this->_conndb);
$output .= "last sql query was: ".$this->_last_query;
die($output);
}
else{
$this->_affected_rows = mysqli_affected_rows($this->_conndb);

}
} //End of query results

public function fetchAll($sql){

$result = $this->query($sql);
$out = array();
while($row = mysqli_fetch_assoc($result)){
$out[] = $row;
}
mysqli_free_result($result);
return $out;
}


MESSAGE CLASS:

private $_table = 'messages';
public function getadminMessage(){
$sql = "SELECT group_concat(messg, time_sent, from_id) as from_id from {$this->_table} where to_id = 4 group by from_id";
return $this->db->fetchAll($sql);//IN THE DBASE CLASS ABOVE

}


In the file getadminmessage.php

$message = new Message();
$results = $message-> getadminMessage();
echo json_encode($results);


I then use getjson

$.getJSON("http://127.0.0.1/tum_old/custom/php/getadminmsg.php",

function(response){
console.log(response);

});


On my log i get

enter image description here

The data seems group concatenated but i would also like the various messages to be in object form that is the messages to be displayed as objects such that i can easily do:

data.object[0].from_id[0].msg to get TRUE LOVE as the message

scais::::



enter image description here

Answer

for select you need

$sql = "SELECT group_concat(messg) as msg, time_sent, from_id
  from {$this->_table} where to_id = 4 group by from_id"; 

for server side you need json_encode the array

public function fetchAll($sql){

  $result =  $this->query($sql);
  $out =  array();
  $cnt =0;
  while($row =  mysqli_fetch_assoc($result)){
    $out[$cnt]['msg'] =  $row['msg'];
    $out[$cnt]['time_sent'] =  $row['time_sent'];
    $out[$cnt]['from_id'] =  $row['from_id'];

    $cnt++;
  }
  mysqli_free_result($result);
  return json_encode($out);
}