dtslobe4694
2019-03-01 17:33
浏览 431

使用MySQL数据构建JSON多维数组

I'm trying to build a multidimensional array in JSON using data from MySQL. I need my JSON to looks like this:

[
    {
        "name": "test name",
        "dates": {
            "Feb 26 2019": 2,
            "Feb 27 2019": 5,
            "Feb 28 2019": 8
        }
    },
        {
        "name": "test name 2",
        "dates": {
            "Feb 22 2019": 2,
            "Feb 24 2019": 5,
            "Feb 28 2019": 8
        }
    }
]

The dates are how many times the name appears in the MySQL table. For example, "test name" shows up 2 times with the date of "Feb 26 2019".

Here is my php:

$query = $wpdb->get_results( "SELECT date, count(date) AS `count` FROM $table_name group by date" );
foreach ($query as $row) {
    $valid_date = date( 'M d Y', strtotime($row->date));
    $data[] = array('date' => $valid_date, 'count' => $row->count);
}


$query1 = $wpdb->get_results( "SELECT DISTINCT cuname FROM $table_name ORDER BY cuname" );
foreach ($query1 as $row) {
    $names[] = array('name' => $row->cuname, 'date' => $data);
}

print json_encode($names);

Here is the JSON that is getting returned:

[  
    {  
        "name":"test name",
        "date":[  
            {  
                "date":"Feb 22 2019",
                "count":"9"
            },
            {  
                "date":"Feb 23 2019",
                "count":"14"
            },
            {  
                "date":"Feb 24 2019",
                "count":"9"
            },
            {  
                "date":"Feb 25 2019",
                "count":"7"
            },
            {  
                "date":"Feb 26 2019",
                "count":"1"
            }
        ]
    },
    {  
        "name":"test name 2",
        "date":[  
            {  
                "date":"Feb 22 2019",
                "count":"9"
            },
            {  
                "date":"Feb 23 2019",
                "count":"14"
            },
            {  
                "date":"Feb 24 2019",
                "count":"9"
            },
            {  
                "date":"Feb 25 2019",
                "count":"7"
            },
            {  
                "date":"Feb 26 2019",
                "count":"1"
            }
        ]
    }
]

The issue is that all dates and the number of times the dates appear in the table are showing up for each "name". I only need the dates for how many times each name appears.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongmu1951 2019-03-01 18:47
    已采纳

    I'd do it with a single query:

    $sql = "
    SELECT 
        `cuname` AS `name`, 
        `date`,
         count(`date`) as `datecount`
    FROM 
        $table_name
    GROUP BY 
        `name`, `date`
    ";
    
    
    $query = $wpdb->get_results($sql);
    
    $res = array();
    $i = 0;
    $name = null;
    foreach ($query as $row) {
        if ($name!=$row->name && $name != null) {
            $i++;
        }
        $res[$i][$row->name] = $row->name;
        $res[$i]['dates'][$row->date] = $row->datecount;
    
        $name = $row->name;
    }
    
    //Verify the array structure:
    
    echo "<pre>";
    print_r($res);
    echo "</pre>";
    
    //Build JSON:
    $json = json_encode($res);
    
    已采纳该答案
    打赏 评论
  • dsgo31121 2019-03-01 17:48

    Try to set your dates and count as key: value

    foreach ($query as $row) {
        $valid_date = date( 'M d Y', strtotime($row->date));
        $data[$valid_date] = $row->count;
    }
    
    打赏 评论

相关推荐 更多相似问题