在一个变量中使用Foreach循环生成多个查询结果

Question

I'm working on a php project using codeigniter but I'm stuck on mysql query since the last 2 days without finding any solution. The problem is that I have 02 Mysql tables that I want to get results from using a variable from the url but I need to generate multiple results from the first query and get multiple results on the second query using the first query results, it's a little bit complicated because I can't find out a better way to describe the problem but here's the first table :

Table  

+---------------+-------------+------+-----+---------+
| Field         | Type        | Null | Key | Default |
+---------------+-------------+------+-----+---------+
| nApplication  | int(11)     | NO   | MUL | NULL    |
| nProduct      | int(11)     | NO   |     | 1       |
+---------------+-------------+------+-----+---------+

I've multiple nApplication values that have the same nProduct (the variable from the url) and I want to get all the row from this table :

Table : tapplication

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| nApplication  | int(11)             | NO   | PRI | NULL    | auto_increment |
| nStatus       | tinyint(3) unsigned | NO   | MUL | 1       |                |
| nManufacturer | int(11)             | NO   | MUL | 1       |                |
| nSerie        | int(11)             | NO   | MUL | 1       |                |
| sType         | varchar(48)         | NO   |     | #       |                |
| sDate1        | varchar(6)          | NO   |     | #       |                |
| sDate2        | varchar(6)          | NO   |     | #       |                |
| sEngine       | varchar(48)         | NO   |     | #       |                |
| sKiloWatt     | varchar(6)          | NO   |     | #       |                |
| sHorsePower   | varchar(6)          | NO   |     | #       |                |
+---------------+---------------------+------+-----+---------+----------------+

That have the same nApplication from the previous query.

The Query I have so fare (Model)

function getApplication($product_id){
      $this->db->from('tapplicationproduct')
               ->where('nProduct',$product_id);
               $query = $this->db->get();
               foreach ($query->result() as $row) {
                $ret['napp'] = $row['nApplication'];
               }
      return $ret;
    }

I can't figure out to get how to get the Application results from the tapplication tables using the above query results

Answer 1

Your problem seems to results in what you're storing and returning:

           foreach ($query->result() as $row) {
            $ret['napp'] = $row['nApplication'];
           }

That will results in array with a single key napp and a single value of the last row nApplication in your return value.

What you want is probably build an array of the data with the row?

           foreach ($query->result() as $row) {
            $ret[] = $row;
           }

Or using nApplication value as key?

           foreach ($query->result() as $row) {
            $ret[$row['nApplication']] = $row;
           }

Now looks like you want to have the data from the nApplication table as well. If you change the query to this you will have all the information in 1 query:

function getApplication($product_id){
  $this->db->from('tapplicationproduct')
           ->join('nApplication','nApplication.nApplication = tapplicationproduct.nApplication')
           ->where('nProduct',$product_id);
           $query = $this->db->get();
           foreach ($query->result() as $row) {
            $ret[] = $row;
           }
  return $ret;
}