php - Select all from MySql and join other

212

I have this codeigniter function

function allClients($orderby = 'clients_company_name', $sort = 'ASC')
{

    //profiling::
    $this->debug_methods_trail[] = __function__;

    //declare
    $conditional_sql = '';

    //check if any specifi ordering was passed
    if (! $this->db->field_exists($orderby, 'clients')) {
        $orderby = 'clients_company_name';
    }

    //check if sorting type was passed
    $sort = ($sort == 'asc' || $sort == 'desc') ? $sort : 'ASC';

    //

It selects all data from table clients. One of them is "client_team_profile_id" - the owner of this client.

I also need to join other table - team profiles. There we can find team_profile_id (there are ids of users in system) and team_profile_full_name - names of users.

{-code-2}

I need to select all data from table CLIENTS (as we can see - Select *) and also get name of team user connected to client and set a parameter for result - AS f.ex. client_owner_name.

I appreciate your help.

339

Answer

sql & benchmarking start

983

Answer

$this->benchmark->mark('code_start'); //_____SQL QUERY_______ $query = $this->db->query("SELECT * FROM clients ORDER BY $orderby $sort"); $results = $query->result_array(); //multi row array //benchmark/debug $this->benchmark->mark('code_end'); $execution_time = $this->benchmark->elapsed_time('code_start', 'code_end'); //debugging data $this->__debugging(__line__, __function__, $execution_time, __class__, $results); //

802

Answer

sql & benchmarking end

Answer

//return results return $results; }|||Table: clients clients_id|clients-company_name|client_address|clients_team_profile_id 1 |Apple |some street |2 2 |Dell |some street |5 Table team_profile team_profile_id | team_profile_full_name| 2 |John | 5 |Bob |

766

Answer

Solution:

So you can add this JOIN to your existing query

SELECT c.*, t.team_profile_full_name as client_owner_name
FROM clients c
    JOIN team_profile t ON t.team_profile_id = c.clients_team_profile_id
ORDER BY $orderby $sort"

You may also need to change this bit of code to use the table alias like so

 //check if any specifi ordering was passed
if (! $this->db->field_exists($orderby, 'clients')) {
    $orderby = 'c.clients_company_name';
}

And also

function allClients($orderby = 'c.clients_company_name', $sort = 'ASC')

People are also looking for solutions to the problem: javascript - How to call ajax from anonymous host, header Access-Control-Allow-Origin: * not working

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question