Question 183788 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – PHP: How to convert multicolumn sql query result into array

I’ve got sql database table which looks like this:

login time_start
name1 17:30:00
name2 18:15:00
name3 16:30:00
name2 17:30:00

And i want to convert it into array with hour as id associated with all logins coresponding to that hour:

Array ( 
[ 18:15:00 ] => Array ( [login] => name2 ) 
[ 17:30:00 ] => Array ( [login] => (name1, name3) ) 
[ 16:30:00 ] => Array ( [login] => name3 )
)

I tried something like this:

if ($result && $result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $aReturn[$row['time_start']] = array(
            'login' => $row['login']
        );
   }
}

but it returned only 3 rows with only one login:

Array ( 
[16:30:00] => Array ( [login] => name3 ) 
[17:30:00] => Array ( [login] => name1 ) 
[18:15:00] => Array ( [login] => name2 ) 
)
Tags:

2

Answers


  1. 0 
    //get the SQL to convert the time into the hour

$sql = 'SELECT HOUR(time_start) as hour, login';
$results = $con->query($sql);
$allRows = $result->fetch_all();

foreach ($allRows as $row) {
    $aReturn[ $row['hour'] ]['login'][] => $row['login'];
}

    RESULT should be like

    Array
(
    [17] => Array
        ( [login] => Array ( [0] => name1, [1] => name4 ) )

    [18] => Array
        ( [login] => Array( [0] => name2 )  )

    [16] => Array
        ( [login] => Array  ( [0] => name3 ) )

)

    OR, if you didnt mean you wanted to reduce the Time to only the Hour then simply

    while($row = $result->fetch_assoc()) {
    $aReturn[ $row['time_start'] ]['login'][] = $row['login'];
}

    Note removed the IF as if there is nothing to do the wjile loop will do nothing which is the same result as with the IF there.

    Login or Signup to reply.
  2. 0

    This will do it

    if ($result && $result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $aReturn[substr($row['time_start'], 0, 2)]['login'][] = $row['login'];
    }
}

    you could do this for clarity …

    if ($result && $result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
         if (!isset($aReturn[$hour = substr($row['time_start'], 0, 2)])) {    
            $aReturn[$hour] = ['login'=>[]];
         }
         $aReturn[$hour]['login'][] = $row['login'];
    }
}

    but it’s not functionally necessary in php

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search