Welcome Guest, Not a member yet? Register   Sign In
second query based on first query
#1

[eluser]sniperscope[/eluser]
Hello All
seems i am going to be a permenant visitor of this form.

I have some problem which i want to create a inner query based on outer query

My previous Sql query was below and i want to know how can i make it run in CI

Regards

Code:
$sql_price_master = mysql_query("SELECT price_id, price_name, price_remark FROM price_master ORDER BY price_id ASC");
  while($row_price_master = mysql_fetch_array($sql_price_master))
  {
   echo '
   <table class="system">
    <tr><td colspan="2" class="tdTitle">' .$row_price_master['price_name']. '</td></tr>';
    
    // SUB PRICE START
    $sql_sub = mysql_query("SELECT price_child_title, price_child_price FROM price_child WHERE price_master_id = '" .$row_price_master['price_id']. "' ORDER BY price_child_id DESC");
    while($row_sub = mysql_fetch_array($sql_sub))
     echo '
    <tr><th>' .$row_sub['price_child_title']. '</th><td>' .$row_sub['price_child_price']. '円</td></tr>';
    echo '
    <tr><td colspan="2" class="tdBiko">' .nl2br($row_price_master['price_remark']). '</td></tr>
   </table>';
  }
#2

[eluser]sniperscope[/eluser]
I solved my problem.
I am posting solution and hope it might help to someone else too.

Code:
class Model_System extends CI_Model
{
function Category()
{
  $dsp = "";
  $sql = "SELECT price_id, price_name, price_remark FROM price_master ORDER BY price_id ASC";
  $query = $this->db->query($sql);
  
  if($query->num_rows > 0)
  {
   $row = $query->result_array();
   foreach($row as $row)
   {
    $dsp .= '
    <table class="system">
     <tr><td colspan="2" class="tdTitle">' .$row['price_name']. '</td></tr>';
    
    $sub_category = "SELECT price_child_title, price_child_price FROM price_child WHERE price_master_id = '" .$row['price_id']. "' ORDER BY price_child_id DESC";
    $sub_category = $this->db->query($sub_category);
    if($sub_category->num_rows > 0)
    {
     $sub = $sub_category->result_array();
    
     foreach($sub as $sub)
     {
      $dsp .= '
      <tr><th>' .$sub['price_child_title']. '</th><td>' .$sub['price_child_price']. '円</td></tr>';
     }
    }
    
     $dsp .= '
     <tr><td colspan="2" class="tdBiko">' .$row['price_remark']. '</td></tr>
    </table>';
   }
  }
  $query->free_result();
  return $dsp;
}
#3

[eluser]vitoco[/eluser]
Just FYI , the idea of MVC it's to separate the data retrieving ( logic ) from their presentation, so it's good that you run the database queries inside the model, but not to create the html code( a table in this case ) in the same function, just retrieve the data to the controller, and then pass that data to the view.

In the long run ( not so long really ) it will be a mess if you combine both in the same code.




Theme © iAndrew 2016 - Forum software by © MyBB