How to combine two SQL tables in codeigniter without using SQL queries?

codeigniter join 2 tables
select query in codeigniter controller
codeigniter delete query return value
join query in codeigniter
codeigniter query
left join in codeigniter
codeigniter select distinct multiple columns
select query with where condition in codeigniter

Here is my codes,

//FROM MY CONTROLLER

$companies = $this->Uploads_model->getallcompanies();
$general = $this->Uploads_model->getallcontract();

$this->data['companieslist'] = $companies; 
$this->data['uploads'] = $general;

$this->render('contracts/index_view');

// MY VIEW
foreach($companieslist as $company){
    $general_c[] = $company->company_id;
    $general_c[] = $company->company_name;
}

foreach ( $uploads as $key => $con ) { 
?>
    <tr>
        <td class="center">
            <label class="pos-rel">
                <input type="checkbox" class="ace"/>
                <span class="lbl"></span>
            </label>
        </td>
        <td>
            <a href="#">
            <?php 
                if($con->Company_id == $general_c[$company_id]){  // MY QUESTION IS HERE
                    echo $con->Company_name;
                }
            ?>
            </a>
        </td>

So, am working on this web app where am supposed to display in table all contract files followed with company owned this contract. I have two tables in my Database as follow Companies_tbl and contract_tbl. I have Company_id as a foreign key in contract_tbl.

Create a custom function to filter your contracts based on Company_id

function getContractsForId($contracts,$Company_id) {
  return array_filter($contracts,function(){
      return $contracts->Company_id === $Company_id;
  });
}

template loop:

foreach( getContractsForId($uploads,$Company_id) as $key => $con) {
//...html...
//remove the if
}

Select from two different tables with same fieds name, I am Trying to implement a select and join using the Active Record class from two tables, I use Active Record, but I do not use the query composition methods. Then you can use $this->db->query('SQL query string'); One  The joins return combinations of matches. If you join two tables, one containing 5 row, and the other 10, the result may contain anywhere from 0 to 50 rows depending on the join condition. Unions. A UNION is used to combine the rows of two or more queries into one result. The Union is called a set operator.

You are not building the array of companies the right way.

foreach($companieslist as $company){
    $general_c[] = $company->company_id;
    $general_c[] = $company->company_name;
}

After you looped once, the $general_c array will look like this:

$general_c = [
    0 => 1, //company id
    1 => 'Company name'
]

After the second time:

$general_c = [
    0 => 1, //company id
    1 => 'Company name',
    2 => 2, //company id
    3 => 'Company name 2'
]

What you want is the following:

foreach($companieslist as $company){
    $general_c[$company->company_id] = $company->company_name;
}

And below:

<?php 
if($general_c[$con->Company_id]) {
    echo $general_c[$con->Company_id];
} ?>

This will let you output the company name based on ID.

But i suggest you do a MySQL join in your model. Add something like this to your Uploads_model:

function getContractsWithCompany() {
    $query = $this->db->select('*')
            ->from('contract_tbl')
            ->join('Companies_tbl', 'Companies_tbl.id = contract_tbl.Company_id')
            ->get();
    return $query->result();
}

Query Builder Class, In some cases only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file​. The following functions allow you to build SQL SELECT statements. $query = $this->db->get(); // Produces: // SELECT * FROM blogs JOIN comments ON  CodeIgniter User Guide. Queries $this->db->query(); To submit a query, use the following function:

You need first to group files by Company_id then list the specific company files

$companies = $this->Uploads_model->getallcompanies();
$general = $this->Uploads_model->getallcontract();

$this->data['companieslist'] = $companies; 
$this->data['uploads'] = array();
//group file list by Company_id

foreach ($general as $key => $file) {
    if(!isset($this->data['uploads'][$file->Company_id ]))
         $this->data['uploads'][$file->Company_id] = array();
    $this->data['uploads'][$file->Company_id][] = $file;
}

// in your view
foreach($companieslist as $company){
    $general_c[] = $company->company_id;
    $general_c[] = $company->company_name;

    if(isset($uploads[$company->company_id])) {
        foreach ( $uploads[$company->company_id] as $key => $con ) {
            ?>
             <tr>
                <td class="center">
                    <label class="pos-rel">
                        <input type="checkbox" class="ace"/>
                        <span class="lbl"></span>
                    </label>
                </td>
                <td>
                    <a href="#"><?=$company->company_name?></a>
                </td>
            </tr>
            <!-- and the rest of logic  -->

    <?php 
        }
    }   
}

Query Builder Class, In some cases, only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file. The following functions allow you to build SQL SELECT statements. $query = $builder->get(); // Produces: // SELECT * FROM blogs JOIN comments ON  Here is the syntax to use CROSS JOIN to combine two tables when a particular condition is met” SELECT tableA.Column1, tableB.Column1 . FROM tableA. CROSS JOIN tableB. WHERE tableB.Column1 = ‘value’; You can go through this SQL tutorial by Intellipaat to learn more such SQL tips and tricks.

How to use joins in codeIgniter with left,right and full join, In some cases only one or two lines of code are necessary to perform a database action. CodeIgniter does not require that each database table be its own class file​. It instead provides The following functions allow you to build SQL SELECT statements. $this->db->get() Permits you to write the JOIN portion of your query​:. You could also use a CTE to grab groups of information you want and join them together, if you wanted them in the same row. Example, depending on which SQL syntax you use, here: WITH group1 AS ( SELECT testA FROM tableA ), group2 AS ( SELECT testB FROM tableB ) SELECT * FROM group1 JOIN group2 ON group1.testA = group2.testB --your choice of join ;

How to Join Two Tables in MySQL, joins in Codeigniter either its left join or right join, we always use joins to retrieve the data from multiple tables? how to use left join in Codeigniter. In SQL you always write your query like this SELECT * from users INNER JOIN comments ON users.u_id Your email address will not be published. Required  SQL Joins Tutorial for Beginners - Inner Join, Left Join, Right Join, Full Outer Join - Duration: 18:04. Joey Blue 356,377 views

Codeigniter App Part 9 - Relational Table Joins, They are used to join two or more different tables on a point in which both the From Laravel to CodeIgniter, We Have Got You Covered On All PHP Frameworks​! Now open index.php and replace $sql query with the above. Joins are not only used for two tables only and you can join more than two  They are shown as green in the diagram. If you want to synchronize the target table with the data from the source table, then you will need to use this match condition to delete rows from the target table. SQL Server MERGE statement example. Suppose we have two table sales.category and sales.category_staging that store the sales by product category.

Comments
  • I dont think this is going to work out, because as far as i can tell the $con does only contain the FK Company_id. And not the company name (which he gets from the $companieslist array entries variable).
  • Table schema will help to get the right answer. Instead of $con->Company_name can be used $company->company_name