How can I generate nested associative array in PHP?

Question

I asked a question here, so many thanks to Gordon Linoff for his complete answer on SQL section of it.

Problem

I have a sql table ( with PRIMARY KEY = [ ip & id ] ) , like this:

ip | id | visits

and I want to generate a php array from that table, like this:

product_rating_for_ip = array(
  ip=>array(
    id=>rating, id=>rating, ...
  ),
  ip=>array(
    id=>rating, id=>rating, ...
  ),
  .
  .
  .
);

for example:

product_rating_for_ip = array(
  "78.125.15.92"=>array(
    1=>0.8,2=>0.2,3=>1.0
  ),
  "163.56.75.112"=>array(
    1=>0.1,2=>0.3,3=>0.5,8=>0.1,12=>1.0
  ),
  .
  .
  .
);

Gordon Linoff suggested this SQL query which works fine:

SQL Query

select v.ip, group_concat(concat(v.id, ':', v.visits / iv.maxvisits)) as ratings
from visit v join
  (SELECT ip, id, visits, max(visits) as maxvisits
  FROM visit
  GROUP BY ip
  ) iv
 on v.ip = iv.ip
group by v.ip;

which produces this:

+-------------+--------------------------+
|ip           |ratings                   |
+-------------+--------------------------+
|154.18.72.15 |2:0.1667,1:1.0000,3:0.1667|  
|162.16.18.22 |1:1.0000,3:0.3750,2:0.1250|  
|142.128.1.14 |2:1.0000,1:0.2500,3:0.5000|  
|78.15.92.131 |1:0.1429,3:1.0000,2:0.2857|  
+-------------+--------------------------+

Question in summary

How can I put this result set into this PHP array?

    product_rating_for_ip = array(
      ip=>array(
        id=>rating, id=>rating, ...
      ),
      ip=>array(
        id=>rating, id=>rating, ...
      ),
      .
      .
      .
    );

My problematic answer

I think I should do as this (may be incorrect or inefficient):

$stmt = mysqli_prepare($con, "select ...");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt,$ip,$ratings);
while (mysqli_stmt_fetch($stmt)){
  $value_array = explode(",",$ratings);
  product_rating_for_ip = array_fill_keys($ip, $value_array);
}

It is not a duplicate. as the SQL part has an answer and I accepted it, nobody would come and help on php part. So I made a second question to concentrate on php part. — Peyman Mohamadpour
– Peyman Mohamadpour, Commented Jul 28, 2013 at 15:29
Hrm, bit by the "one accepted answer per question" problem. It's still annoying to see the same question posted twice, though I'd undo my flag if I could. I'll take an invalid flag if necessary. ;) — user2609094
– user2609094, Commented Jul 28, 2013 at 15:44
@Nate I removed the php part from the first question. Is that OK now? — Peyman Mohamadpour
– Peyman Mohamadpour, Commented Jul 28, 2013 at 15:55
@Nate please consider revising your revision on this question, as I managed the conflict with the prior question, if possible. thanks :) — Peyman Mohamadpour
– Peyman Mohamadpour, Commented Jul 28, 2013 at 16:14
Sadly it's not possible to undo a flag - but I did delete the "this is a duplicate" comment. Whoever does come to moderate will most likely see these comments and leave your post alone. Sorry about that, too trigger happy on that one. — user2609094
– user2609094, Commented Jul 28, 2013 at 16:34

Expedito · Accepted Answer · 2013-07-28 16:25:35Z

2

mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt,$ip,$ratings);
$product_rating_for_ip = array();
while (mysqli_stmt_fetch($stmt)){
    $value_array = explode(",",$ratings);
    foreach ($value_array as $key => $value){
        $parts = explode(':', $value);
        $product_rating_for_ip[$ip][$parts[0]] = $parts[1];
    }
}

edited Jul 28, 2013 at 16:25

answered Jul 28, 2013 at 15:27

Expedito

7,8095 gold badges32 silver badges45 bronze badges

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

3 Comments

Peyman Mohamadpour Over a year ago

Thanks, but I think it just does the same as my own code. it outputs the array as ip=>array("id:value","id:value",...) but What I want is ip=>array(id=>value,id=>value,...)

Peyman Mohamadpour Over a year ago

It has a kind of minor problem. ids are product ids like 1, 2 , 8 for example, but this code returns 0, 1 , 2

Expedito Over a year ago

That should have fixed it.

Collectives™ on Stack Overflow

How can I generate nested associative array in PHP?

Problem

SQL Query

Question in summary

My problematic answer

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Problem

SQL Query

Question in summary

My problematic answer

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related