getting duplicated results from nested foreach loop

Question

I'm trying to get the following output from mysql for Google Line Chart API:

[["product","diameter","width"],["Product 1","2","4"],["Product 2","4","8"]]

I have set up several input checkboxes to send field names (e.g width,diameter) to the database via $_POST["info"] and retrieve the values from those fields. Here's the part that generates the data from mysql:

$result = $users->fetchAll();
$comma = "";
$data="";
$data[0] = array_merge(array(product),$info);   
$i = 1;
foreach ($result as $r)
{
    foreach($_POST["info"] as $p)
    {    
        $d .= $comma.$r[$p];     // trying to get "$r["width"],$r["diameter"]"            
    }
    $comma = ",";
    $data[$i] = array($r["name"], $d);
    $i++;
}   
echo json_encode($data);

My desired output should be like this:

[["product","diameter","width"],["Product 1","2","4"],["Product 2","4","8"]]

But that code is generating duplicated results like this

[["product","diameter","width"],["Product 1","24"],["Product 2","24,4,8"]]

I guess I shouldn't be using the nested foreach to loop over $_POST. Can anyone tell me how to fix that?

Full PHP Code:

$info = $_POST["info"]; // It contains an array with values like width,diameter,thickness etc...
$comma = "";
foreach($info as $in)
{ 
    $field .= "".$comma."b.".$in."";
    $comma = ",";
}

$sql = "
        SELECT {$field},a.user_id,a.name
        FROM `product_detail` a INNER JOIN
        `attr` b ON a.model = b.model 
        WHERE a.user_id = ?
        GROUP BY a.model
       ";
$users = $dbh->prepare($sql);
$users->bindValue(1, $_SESSION["user_id"]);  
$users->execute();
$result = $users->fetchAll();
$comma = "";
$data="";
$i = 1;
$data[0] = array_merge(array(product),$info);       
foreach ($result as $r)
{
    foreach($_POST["info"] as $p)
    {    
        $d .= $comma.$r[$p];                
    }
    $comma = ",";
    $data[$i] = array($r["name"], $d);
    $i++;
}   
echo json_encode($data);

$_POST["info"] Content:

Array
(
    [0] => diameter
    [1] => width
)

Please post the content of $_POST["info"]; and $result - otherwise it's hard to predict what's going on. — Hirnhamster
– Hirnhamster, Commented Aug 28, 2014 at 14:50
@Hirnhamster, sorry, please wait, someone has edited out my $POST content — RedGiant
– RedGiant, Commented Aug 28, 2014 at 14:58

CodeBird · Accepted Answer · 2014-08-28 15:17:36Z

1

try it like this:

$result = $users->fetchAll();
$data="";
$data[0] = array_merge(array(product),$info);   
$i = 1;
foreach ($result as $r)
{
    $d[]=$r["name"];
    foreach($_POST["info"] as $p)
    {    
        $d[]= $r[$p];
    }
    $data[$i] = $d;
    $d=array(); //set $d to empty not to get duplicate results
    $i++;
}   
echo json_encode($data);

edited Aug 28, 2014 at 15:17

answered Aug 28, 2014 at 14:59

CodeBird

3,8132 gold badges22 silver badges38 bronze badges

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

2 Comments

RedGiant Over a year ago

your solution seems to be closer, but the output is missing double quotes separating the values between diameter and width: [["product","diameter","width"],["Product 1","2,4"],["Product 2","4,8"]]

RedGiant Over a year ago

CodeBird, It works. I use echo json_encode($data,JSON_NUMERIC_CHECK) at the end because I just found out the API doesn't allow the number wrapped in quotes.

jeroen · Accepted Answer · 2014-08-28 15:00:49Z

0

The end result you are looking for, is valid JSON. You should not try to manually generate that.

Instead you should make an array of arrays in php and use json_encode($array) to get the result you are looking for.

Also note that by injecting your POST variables directly in your query, you are vulnerable to sql injection. When accepting fields, you should check them against a white-list of allowed values.

answered Aug 28, 2014 at 15:00

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

Comments

Mehdi Karamosly · Accepted Answer · 2014-08-28 15:01:08Z

0

Try the below solution:

$result = $users->fetchAll();
$data="";
$data[0] = array_merge(array(product),$info);   
$i = 1;
foreach ($result as $r)
{
    $d = array();
    foreach($_POST["info"] as $p)
    {    
        $d[] = $r[$p];     // trying to get "$r["width"],$r["diameter"]"            
    }
    $data[$i] = array($r["name"]) +$d;
    $i++;
}   
echo json_encode($data);

answered Aug 28, 2014 at 15:01

Mehdi Karamosly

5,4382 gold badges34 silver badges50 bronze badges

2 Comments

RedGiant Over a year ago

I just tested your solution. It can only get the values from the width field, like this: [["product","diameter","width"],["Product 1","4"],["Product 2","8"]]

Mehdi Karamosly Over a year ago

after creating your query try to output it to the screen and then run it in MySql client use var_dump($sql); and make sure it has both width and diameter

Collectives™ on Stack Overflow

getting duplicated results from nested foreach loop

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related