加入mysql php函数代替foreach循环 [英] JOIN mysql php function instead foreach loop

问题描述

我需要在我的mysql数据库活动中传递2014年9月3日和2014年3月12日的战绩:

I need to passing trought 09.03.2014 and 12.03.2014 in my mysql database activity:

 SELECT * FROM activity;

 ID timestamp_day name        details
  1 1394319600    Meeting     Meeting with Ann about job
  2 1394406000    Travel      Go to New York
  3 1394492400    Work        Do some work...
  4 1394578800    Vacation    Prepare all necessary stuff
  5 1394319600    Car repair  Go to store to buy new parts for car

我需要获取的是一个JSON数据来创建datatable.net表，如下所示:

What I need to get is a JSON data to create datatable.net Table like this:

  Datum     | Name
  ----------+-----
  Sunday    |
  09/03     |
  ----------+-----
  Monday    |
  10/03     |
  ----------+----- 
  Tuesday   |
  11/03     |
  ----------+-----
  Wednesday |
  12/03     |
  ----------+-----

我想做什么:

PHP:

$dateString = '09.03.2014';
$startDate = new DateTime($dateString);

$period = new DateInterval('P1M');
$endDate = clone $startDate;
$endDate->add($period);

$interval = new DateInterval('P1D');
$daterange = new DatePeriod($startDate, $interval ,$endDate);
$i=1;
foreach($daterange as $date){
    $temp = array();
    // the following line will be used to slice the Pie chart
    $temp['ID'] = $i;
    $dejt = $date->format("l") . PHP_EOL;
    $temp['datum'] = '<strong>'.$dejt.'</strong></br>'.$date->format("d/m") . PHP_EOL;


        $rs1 = $db->prepare('SELECT name FROM activity WHERE timestamp=:ts');
        $rs1->bindParam(':ts', $timestamp); 
        $rs1->execute();
        $naz = $rs1->fetchColumn();
                      if ($naz != false) {
              $temp['name'] =  $naz;
                      } else {
                         $temp['vrsta'] =  '';
                      }


        $output['data'][] = $temp;
        $i++;
        $jsonTable = json_encode($output);
}

    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }
    echo $jsonTable;

和JQuery:

$('#example').dataTable( {
        "ajax": "table1.php",
        "columns": [
            { "data": "ID" },
            { "data": "datum" },
            { "data": "name" },

        ], etc.

我的代码工作正常，但是当存在相同的timestamp_day值时，我也多次使用查询访问数据库，然后我才获得第一个...

My code work fine but I hit database with queries many times also when is there same timestamp_day values then I get just first...

所以您看到每个日期我都通过查询命中mysql数据库.我想创建其他方式吗?使用JOIN mysql函数时，效果如何...?同样在这里您可以看到我的ID 1和ID 5带有相同的时间戳，因此我需要将这些行的名称放在一个单元格中并为此数据创建单独的HTML.

So you see that I hit mysql database with quesry for each date. I want to create other way to do that? How this will looks like with JOIN mysql functions and so... ? Also here you can see that I have ID 1 and ID 5 with same timestamp so names of this rows I need to put in one cell and create separate HTML for this data.

推荐答案

我不熟悉datatable.net，仅向您展示一个选项，可通过一个数据库调用来获取结果.必要时进行调整.

Not familiar with datatable.net, I'll just show you an option to fetch your results with one database call. Make adjustments where necessary.

$rs1 = $db->prepare('SELECT * FROM activity;');    
$rs1->execute();
$rows = $rs1->fetchAll();
$daterange = new DatePeriod($startDate, $interval ,$endDate);
$activities = array();
if (count($rows) > 0)
{
    foreach ($rows as $row)
    {
        $activities[$row['timestamp_day']][] = $row['name'];
    }
    foreach($daterange as $date)
    {
        $formattedData = $data  //format your date to timestamp here
        if (!array_key_exists($formattedDate, $activities))
        {
             $activities[$formattedDate] = array();
        }
    }
}

为您提供以下格式的数组:

Gives you an array with the following format:

$activities = array(
    [1394319600] => array('Meeting', 'Car repair') 
    [1394406000] => array('Travel') 
    [1394492400] => array('Work') 
    [1394578800] => array('Vacation') 
)

这篇关于加入mysql php函数代替foreach循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！