CodeIgniter Forums
Active record, help needed. - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Active record, help needed. (/thread-52624.html)



Active record, help needed. - El Forum - 06-19-2012

[eluser]the_unforgiven[/eluser]
I'm not fully sure how i can acheive the following php code in active record in my model then retrieve the data as and when i require in my view file.

I need help converting this to active record in my model can some one help me please?
Code:
//Search for all agencies
  $CATquery = "SELECT * FROM  `tblclients` WHERE `Categories` = 'AGE'";
  $AGEresult = mysql_query($CATquery);
  echo "<table width='100%'>";
  echo "<tr><td>Agency</td><td>Contact</td><td>Phone</td><td>Areas</td><td>Books</td></tr>";//Column titles
  while ($AGE_array = mysql_fetch_assoc($AGEresult))
   {
    $colorquery = "SELECT `Color` FROM `tblcategory` WHERE `CategoryCode` = '".$AGE_array['Categories']."'";
    $colorresult = mysql_query($colorquery);
    while ($color_array = mysql_fetch_assoc($colorresult))
    {
     echo "<tr><td width='10%'><font color='#".$color_array['Color']."'>".$AGE_array['Company']."</font></td>";//Display Agency name
    }
    //Cross reference the contact
    echo "<td width='10%'>";
    $Contactquery = "SELECT DISTINCT `Name` FROM `tblcontacts` WHERE `AutoKey` = '".$AGE_array['AutoKey']."'";
    $Contactresult = mysql_query($Contactquery);
     while ($Contact_array = mysql_fetch_assoc($Contactresult))
     {
      echo $Contact_array['Name'];//Display any contacts
     }
     echo "</td>";
     echo "<td width='10%'>".$AGE_array['Phone']."</td>";
      //Crossreference the areas. Requires matching area code to agency then grabbing area names, therefore requires two queries
      $AreaIDquery = "SELECT DISTINCT `AreaID` FROM `tblagencyareas` WHERE `AgencyID` = '".$AGE_array['AutoKey']."'";
      $AreaIDresult = mysql_query($AreaIDquery);
      echo "<td width='10%'>";
      while ($AreaID_array = mysql_fetch_assoc($AreaIDresult))
      {
       $Areaquery = "SELECT `AreaName` FROM `tblarea` WHERE `AreaID` = '".$AreaID_array['AreaID']."'";
       $Arearesult = mysql_query($Areaquery);
       while ($Area_array = mysql_fetch_assoc($Arearesult))
       {
        echo $Area_array['AreaName'].", ";//Display the areas
       }
       mysql_free_result($Arearesult);
      }
    //Display Agencies Books.
    $Bookquery = "SELECT BookName FROM `tblbook` WHERE `AgencyID` = '". $AGE_array['AutoKey']."'";
    $Bookresult = mysql_query($Bookquery);
    echo "<td = '40%'>";
    while ($Book_array = mysql_fetch_assoc($Bookresult))
     {
      echo $Book_array['BookName'].", ";
     }
    
    echo "</td></tr>";//Display Agency name
    mysql_free_result($AreaIDresult);
    
    mysql_free_result($Contactresult);  
   }
   echo "</table>";
  mysql_free_result($AGEresult);

Help greatly appreciated...

Smile


Active record, help needed. - El Forum - 06-19-2012

[eluser]the_unforgiven[/eluser]
Not bothered about the tables part need everything in an array or something so i can just call $item['AreaID']; or whatever in my view file, please.


Active record, help needed. - El Forum - 06-19-2012

[eluser]boltsabre[/eluser]
I don't mean to be rude or anything, but have you even tried to do this yourself?

If so please post the active records code you already have and explain what is not working.


Active record, help needed. - El Forum - 06-19-2012

[eluser]the_unforgiven[/eluser]
Apologies...

Code:
$data = array();
  $key = $this->uri->segment(3);
  
  $this->db->where('AgencyID', $key);
  $q = "SELECT DISTINCT 'AreaID' FROM 'tblAgencyAreas' WHERE 'AgencyID' = $key";
  $qa = "SELECT * FROM  'tblClients' WHERE 'Categories' = 'AGE'";
  
  //$q = $this->db->get('tblMeeting');
  
  if ($q->num_rows() > 0){
      $data = $q->row_array();
  }
  if ($qa->num_rows() > 0){
      $data = $qa->row_array();
  }


  $q->free_result();