Welcome Guest, Not a member yet? Register   Sign In
Active record, help needed.
#1

[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
#2

[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.
#3

[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.
#4

[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();




Theme © iAndrew 2016 - Forum software by © MyBB