Codeigniter4 How i can convert this builder query to normal ?

Hello,

I have a builder query in codeigniter4 looks like :

Code:

        $this->builder()->select('a.id as empid, a.first_name as name, a.last_name as last, a.emp_code as emp_code, b.name as division, c.name as department, e.name as designation, d.name as status');
        $this->from('employees_master a'); // I use aliasing make joins easier
        $this->join('divisions as b', 'a.division_id = b.id', 'INNER');
        $this->join('departments as c', 'a.department_id = c.id', 'INNER');
        $this->join('employees_status_types d', 'a.employee_status_id = d.id', 'INNER');
        $this->join('designations e', 'a.designation_id = e.id', 'INNER');
        $this->like('a.first_name', $search);
        $this->orLike('a.last_name', $search);
        $this->groupBy('a.id');

I tried

Code:

select a.id as emp_id, a.first_name as name, a.last_name as last, a.emp_code as emp_code, b.name as division, c.name as department, e.name as designation, d.name as status FROM employees_master AS a, departments as c, employees_status_types as d, designations e
INNER JOIN divisions ON a.division_id = b.id
INNER JOIN a.department_id = c.id
INNER JOIN a.employee_status_id = d.id
INNER JOIN a.designation_id = e.id

Error :

Code:

←
phpMyAdmin
Home Empty session data phpMyAdmin documentation MySQL Documentation Navigation panel settings Reload navigation panel
Collapse all Unlink from main panel
NewNew
Expand/CollapseDatabase operationsalgt-hr
Type to filter these, Enter to search all
NewNew
Expand/CollapseStructureair_fares
Expand/CollapseStructureassets
Expand/CollapseStructureattendences
Expand/CollapseStructureauth_groups_users
Expand/CollapseStructureauth_identities
Expand/CollapseStructureauth_logins
Expand/CollapseStructureauth_permissions_users
Expand/CollapseStructureauth_remember_tokens
Expand/CollapseStructureauth_token_logins
Expand/CollapseStructureavailable_banks
Expand/CollapseStructurebank_accounts
Expand/CollapseStructurecollars
Expand/CollapseStructurecountries
Expand/CollapseStructuredeductions
Expand/CollapseStructuredepartments
Expand/CollapseStructuredesignations
Expand/CollapseStructuredivisions
Expand/CollapseStructuredocuments
Expand/CollapseStructuredocuments_type
Expand/CollapseStructureemployees_master
Expand/CollapseStructureemployees_status_types
Expand/CollapseStructureentities
Expand/CollapseStructureincentives
Expand/CollapseStructureleave_adjustment
Expand/CollapseStructureleave_applications
Expand/CollapseStructureleave_types
Expand/CollapseStructuremigrations
Expand/CollapseStructurenationalities
Expand/CollapseStructurepayment_types
Expand/CollapseStructurepay_group
Expand/CollapseStructuresalary
Expand/CollapseStructuresettings
Expand/CollapseStructureusers
Expand/CollapseDatabase operationsbeatroute
Expand/CollapseDatabase operationsinformation_schema
Expand/CollapseDatabase operationsmysql
Expand/CollapseDatabase operationsperformance_schema
Expand/CollapseDatabase operationssys
Server: localhost:8889
Database: algt-hr
Table: employees_master
Browse Browse
Structure Structure
SQL SQL(current)
Search Search
Insert Insert
Export Export
Import Import
Privileges Privileges
Operations Operations
Triggers Triggers
Indicates that you have made changes to this page; you will be prompted for confirmation before abandoning changes Page-related settings Click on the bar to scroll to top of page
SQL Query Console Console
ascendingdescendingOrder:Debug SQLExecution orderTime takenOrder by:Group queries
Some error occurred while getting SQL debug info.
OptionsSet default
Always expand query messages
Show query history at start
Show current browsing query
Execute queries on Enter and insert new line with Shift+Enter. To make this permanent, view settings.
Switch to dark theme
Run SQL query/queries on table algt-hr.employees_master: Documentation
select a.id as emp_id, a.first_name as name, a.last_name as last, a.emp_code as emp_code, b.name as division, c.name as department, e.name as designation, d.name as status FROM employees_master AS a, departments as c, employees_status_types as d, designations e
INNER JOIN divisions ON a.division_id = b.id
INNER JOIN a.department_id = c.id
INNER JOIN a.employee_status_id = d.id
INNER JOIN a.designation_id = e.id
1
select a.id as emp_id, a.first_name as name, a.last_name as last, a.emp_code as emp_code, b.name as division, c.name as department, e.name as designation, d.name as status FROM employees_master AS a, departments as c, employees_status_types as d, designations e
2
INNER JOIN divisions ON a.division_id = b.id
3
INNER JOIN a.department_id = c.id
4
INNER JOIN a.employee_status_id = d.id
5
INNER JOIN a.designation_id = e.id
Bind parameters Documentation
Columnsidfirst_namelast_namedate_of_joiningemp_codeprofile_picemaildobreporting_tohome_addruae_addrhome_phoneuae_phonelast_working_dategendermartial_statusnationality_idpayment_type_idemployee_status_identity_iddivision_iddepartment_iddesignation_idcreated_atupdated_atcreated_bydeleted_atair_fares_idcountry_idupdated_bypay_group_idplace_of_birthprobation_expirygdrfa_nolabour_card_nolabour_card_expmol_noleave_balanceswork_locationeid_noeid_expirypassport_statuspassport_nopassport_expiryinsurance_provider
Delimiter
Delimiter
;
Show this query here again
Retain query box
Rollback when finished
Enable foreign key checks
Error
SQL query: Copy Documentation


select a.id as emp_id, a.first_name as name, a.last_name as last, a.emp_code as emp_code, b.name as division, c.name as department, e.name as designation, d.name as status FROM employees_master AS a, departments as c, employees_status_types as d, designations e
(INNER JOIN divisions ON a.division_id = b.id
INNER JOIN a.department_id = c.id
INNER JOIN a.employee_status_id = d.id
INNER JOIN a.designation_id = e.id) LIMIT 0, 25
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(INNER JOIN divisions ON a.division_id = b.id
INNER JOIN a.department_id = c.id' at line 2

How i can change this to normal query ?, Can anyone help with me ?

Thanks

you're missing the from part
"....as d, designations e from employees_master a inner join .divisions ...."

Well, Chatgpt says:

PHP Code:

SELECT a.id as empid, 
      a.first_name as name, 
      a.last_name as last, 
      a.emp_code as emp_code, 
      b.name as division, 
      c.name as department, 
      e.name as designation, 
      d.name as status
FROM employees_master a
INNER JOIN divisions as b ON a.division_id = b.id
INNER JOIN departments as c ON a.department_id = c.id
INNER JOIN employees_status_types d ON a.employee_status_id = d.id
INNER JOIN designations e ON a.designation_id = e.id
WHERE a.first_name LIKE '%$search%'
  OR a.last_name LIKE '%$search%'
GROUP BY a.id; 


I hope it works

In addition to this, if you don't want bunch of SQL code in your php file, I suggest adding this in a stored procedure then using builder you can just CALL `GetEmployeeDetails` ('search_String');

PHP Code:

DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(
    IN search VARCHAR(255)
)
BEGIN
    SELECT 
        a.id AS empid, 
        a.first_name AS name, 
        a.last_name AS last, 
        a.emp_code AS emp_code, 
        b.name AS division, 
        c.name AS department, 
        e.name AS designation, 
        d.name AS status
    FROM 
        employees_master a
    INNER JOIN 
        divisions AS b ON a.division_id = b.id
    INNER JOIN 
        departments AS c ON a.department_id = c.id
    INNER JOIN 
        employees_status_types d ON a.employee_status_id = d.id
    INNER JOIN 
        designations e ON a.designation_id = e.id
    WHERE 
        a.first_name LIKE CONCAT('%', search, '%')
        OR a.last_name LIKE CONCAT('%', search, '%')
    GROUP BY 
        a.id;
END //

DELIMITER ; 


Web developer creating tools for streamers

Countdown Tools: https://obscountdown.com

Online Polls https://streampollmaster.com