title: OOP Challenge How to nest using foreach in php mysql result set?
body: <p>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..</p>
  
  <p>My main database table (partner_redirect) has got thousands of records like this:</p>
  
  <pre><code> 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   
  </code></pre>
  
  <p>.......................................................................
  .......................................................................</p>
  
  <p>Here is my code:</p>
  
  <p>db.class.php</p>
  
  <pre><code>  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-&gt;query($sql);
      $out = array();
  
      while ($row = $this-&gt;fetch_array($query_id)) {
          $out[] = $row;
      }
  
      $this-&gt;free_result($query_id);
      return $out;
    }#-#fetch_all_array()
  
  
  } 
  </code></pre>
  
  <p><strong>Implementation:</strong></p>
  
  <pre><code> include_once ('Database.class.php');
  
    $sql="select id,name from partner ";
  
  $col=$db-&gt;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-&gt;fetch_all_array($sql);
  
    foreach($partner_redirect as $part_red)
    {
  
      $data['id']=$part_red['partnerID'];
      $data['date']=$part_red['date'];
      if(isset($previous) &amp;&amp;($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;
      }
  
     }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>All i want to display results by executing  two queries using foreach like this:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>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????</p>
  
  <p>I would appreciate your help in this regard.</p>