jquery - JOIN mysql php function instead foreach loop

Question

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

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     |
  ----------+-----

What I try to do:

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;

and JQuery:

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

        ], etc.

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...

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.

Answer 1

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') 
)