OOP Challenge How to nest using foreach in php mys

Question

I am having problem in retrieving mysql records. What i am trying to achieve is to execute mysql query once or maximum twice,store the results in an array and then loop through the array using foreach to store result as mentioned below..

My main database table (partner_redirect) has got thousands of records like this:

 id     member     partnerid         Status                       datePost
 1       87887       1                 1                         2014-08-09  
 2       988         3                 0                         2014-08-09
 3       4545        1                 1                         2014-08-09  
 4       8892        5                 0                         2014-08-09 
 5       8891        5                 0                         2014-08-09 
 6       8894        5                 0                         2014-08-09 
 7       889         5                 0                         2014-08-02 
 8       2341        3                 1                         2014-08-09    
 9       8893        1                 0                         2014-08-01 
 10      23224       3                 1                         2014-08-01   
 11      88913       5                 0                         2014-08-03 
 12      2324        3                 0                         2014-08-04   

....................................................................... .......................................................................

Here is my code:

db.class.php

  class Database {
  ..............................
  ........................
  #-#############################################
  # desc: returns all the results (not one row)
  # param: (MySQL query) the query to run on server
  # returns: assoc array of ALL fetched results
  public function fetch_all_array($sql) {
    $query_id = $this->query($sql);
    $out = array();

    while ($row = $this->fetch_array($query_id)) {
        $out[] = $row;
    }

    $this->free_result($query_id);
    return $out;
  }#-#fetch_all_array()


} 

Implementation:

 include_once ('Database.class.php');

  $sql="select id,name from partner ";

$col=$db->fetch_all_array($sql);

 foreach($col as $part)
{
   $id=$part['id'];
   $name=$part['name'];

   $sql ="select partnerid,datePost as date ,Status from  partner_redirect_link where partnerID=".$id;

   $partner_redirect=$db->fetch_all_array($sql);

  foreach($partner_redirect as $part_red)
  {

    $data['id']=$part_red['partnerID'];
    $data['date']=$part_red['date'];
    if(isset($previous) &&($previous!=$part_red['date']))
    {
    while($previous==$part_red['date'])
    {
    $redirected=$part_red['redirectedStatus'];


    #if redirected status =0 then
    if ($redirected==0)
    {
        $totalNotredirect=$totalNotredirect+1;
        $data['totalNotredirect']=$totalNotredirect;
        #$data['totalredirect']=0;
    }else{# if 1
        $totalredirected=$totalredirected+1;
        $data['totalredirect']=$totalredirected;
        #$data['totalredirect']=0;
    }

  }

   }else{
       $previous=$data['date'];

        $redirected=$part_red['redirectedStatus'];


        #if redirected status =0 then
        if ($redirected==0)
        {
            $totalNotredirect=$totalNotredirect+1;
            $data['totalNotredirect']=$totalNotredirect;
            #$data['totalredirect']=0;
        }else{# if 1
            $totalredirected=$totalredirected+1;
            $data['totalredirect']=$totalredirected;
            #$data['totalredirect']=0;
        }

     $data['date']=$previous;
      $date[]=$previous;
    }

   }
}

I am struggling to store data but i dont know how can i correspond and count each record saving /comparing each date in the array.

All i want to display results by executing two queries using foreach like this:

 Date          Partner  TotalRecords(per partner)  count(status=1)  count(status=0)     
 2014-08-09    1         2                              2                  0
 2014-08-09    3         2                              1                  1
 2014-08-09    5         3                              0                  3
 2014-08-04    3         1                              0                  1
 2014-08-03    5         1                              0                  1
 2014-08-01    1         1                              0                  1
 2014-08-01    3         1                              1                  1

UPDATE:

All i want to retrieve data from database by executing queries for minimum number of times so that it won't slow down my huge online database.My idea was to make one or two mysql queries to get all results and then display records according to above mentioned desired output but i failed to achieve this!Is there anyway i could achieve this????

I would appreciate your help in this regard.

Answer 1

Rather than PHP, I'd recommend using SQL to do all that heavy lifting. And it only requires one query.

SQL

Based on your desired output of a 5 column table the following SQL query does the trick. I tested it on my local box with the data you supplied. The date column could be omitted as you don't know what row the date is actually coming from.

select 
    datePost,
    partnerid,
    count(*) as `partner_totalrecords`,
    sum(case when `status` = 0 then 1 else 0 end) `count_status_0`,
    sum(case when `status` = 1 then 1 else 0 end) `count_status_1`
from partner_redirect
group by partnerid;

Thanks to this SO answer for how to get the two status count columns. I didn't know how to do that until just now. Neat trick.

PHP

From that one query you now have a simple flat table to output. Forgive the brevity, but I think it gets the point across.

$rows=$db->fetch_all_array($sql);

foreach($rows as $row) {
    var_dump($row);
}

Results (from MySQLWorkbench)