want to merge two tables as on table

Question

I want all rows from this table

The query of that db is

function getmodules(){
    $sql="SELECT * FROM module
    WHERE status=1";            
    $query=$this->db->query($sql);
    return $query;
}

and selected rows from this one as query written

the query for above db is

function getmoduledtls($id){
    $sql="SELECT * FROM module m
    INNER JOIN permissions p
    ON p.moduleId=m.moduleId
    WHERE m.status=1
    AND p.roleId='$id'";                        
    $query=$this->db->query($sql);
    return $query;
}

Please be more specific. What is the result you want to achieve? — Wax Cage, May 26 '17 at 11:55
i need result like `Array ( [0] => stdClass Object ( [moduleId] => 1 [moduleName] => Course [status] => 1 [permissionId] => 24 [roleId] => 1 [view] => 1 [add] => 0 [edit] => 1 [deleteRole] => 0 ) [1] => stdClass Object ( [moduleId] => 2 [moduleName] => Roles [status] => 1 [permissionId] => 37 [roleId] => 1 [view] => 0 [add] => 1 [edit] => 0 [deleteRole] => 0 ) [2] => stdClass Object ( [moduleId] => 3 [moduleName] => Transactions [status] => 1 [permissionId] => [roleId] => 1 [view] => 0 [add] => 0 [edit] => 0 [deleteRole] => 0 ) )` — Kevin, May 26 '17 at 11:59

score 0 · Answer 1 · answered May 26 '17 at 13:14

You can select all fields for a table by using it's alias ie: m.* then select the individual fields from p that you want. Please note if any column in m and p have the same names the values from p will be returned. You will need to give any duplicate fields alias like

'm.status as module_status, p.status as permission_status"

SELECT m.*, p.permissionId, p.view, p.etc FROM module m
    INNER JOIN permissions p
    ON p.moduleId=m.moduleId
    WHERE m.status=1
    AND p.roleId='$id'"

Also please note this query is open to SQL injection. How can I prevent SQL injection in PHP?

want to merge two tables as on table

1 Answers1