how to sort query result in php?

Question

Hi,

I am trying to sort query result into an array.

I have following query.

"select s.*, a.* from students left join addresses as a";

it returns

s.id
s.name
a.id
a.student_id
a.address

I would like to make above result into an array with s and a keys.

array(
  "s"=>array("id"=>"value","name"=>"value")
  ,"a"=>array("id"=>"value","student_id"=>"value","address"=>"value)
)

do I have to make my own function to do it or is there an internal function?

Answer 1

You have to do it yourself if for no other reason than the table isn't identifiable in PHP from the query result. You only get the column name.

Answer 2

Aside: In your current code I believe s.id will get overwritten by a.id if you select rows as associative arrays.

Now, on to one possible approach...

One aspect of how the results are returned is you lose the table from which the column came. If you find yourself wanting to do post-processing on the resultset, perhaps you could alias each column by renaming it like so:

$query = "SELECT
    s.id         AS s_id
    s.name       AS s_name
    a.id         AS a_id
    a.student_id AS a_student_id
    a.address    AS a_address
    ... ";

Once you have that you can organize the data based on substr($key,0,1) and get the "underlying column name with substr($key,2).

Once you have those you can loop inside each row to create the data structure you want to work with.