php - MySQL Query equivalent in CodeIgniter

790

MySQL Query is below

select PB.*, P.*, WhoCreated.*, WhoPlacedBid.* from tblprojectbid PB
Inner Join tblproject P on P.projectid = PB.projectid
Inner Join tbluser WhoCreated WhoCreated.UserID = P.WhoCreated
Inner Join tbluser WhoPlacedBid WhoPlacedBid.UserID = PB.WhoCreated
Where PB.FreelancerAwardedProjectStatusID in (4, 5)
and WhoCreated.UserID = 3
and PB.Reviewgiven = 0

Below is the query written in CodeIgnitor.

$this->_ci->db->select('PB.*, P.*, WhoCreated.*, WhoPlacedBid.*');
$this->_ci->db->from('tblprojectbid PB');
$this->_ci->db->join('tblproject P', 'P.projectid = PB.projectid');
$this->_ci->db->join('tbluser WhoCreated', 'WhoCreated.UserID = P.WhoCreated');
$this->_ci->db->join('tbluser WhoPlacedBid', 'WhoPlacedBid.UserID = PB.WhoCreated');
$this->_ci->db->or_where('PB.FreelancerAwardedProjectStatusID', 4);
$this->_ci->db->or_where('PB.FreelancerAwardedProjectStatusID', 5);
$this->_ci->db->where('WhoCreated.UserID', 5);
$this->_ci->db->where('PB.Reviewgiven', 5);
$query = $this->_ci->db->get();

Can you please help in correcting the above query to make it equivalant to MYSQL query above ?

194

Answer

Solution:

$this->db->select() accepts an optional second parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks. You don't need to repeat $this->_ci->db-> untill you don't want to add join or conditions with a conditional statement. Use where_in() for checking the same column name with more than one value . And you can optimize the code like this:

$this->_ci->db->select('PB.*, P.*, WhoCreated.*, WhoPlacedBid.*',FALSE)
            ->from('tblprojectbid PB')
            ->join('tblproject P', 'P.projectid = PB.projectid')
            ->join('tbluser WhoCreated', 'WhoCreated.UserID = P.WhoCreated')
            ->join('tbluser WhoPlacedBid', 'WhoPlacedBid.UserID = PB.WhoCreated')
            ->where_in('PB.FreelancerAwardedProjectStatusID', array(4,5))
            ->where(array('WhoCreated.UserID'   =>  5, 'PB.Reviewgiven' =>  5))        
            ->get();
139

Answer

Solution:

Little corrections:

1) add second parameterfalse to select() to avoid backticks.
2) usedwhere_in

$this->_ci->db->select('PB.*, P.*, WhoCreated.*, WhoPlacedBid.*',false);
$this->_ci->db->from('tblprojectbid PB');
$this->_ci->db->join('tblproject P', 'P.projectid = PB.projectid');
$this->_ci->db->join('tbluser WhoCreated', 'WhoCreated.UserID = P.WhoCreated');
$this->_ci->db->join('tbluser WhoPlacedBid', 'WhoPlacedBid.UserID = PB.WhoCreated');
$this->_ci->db->where_in('PB.FreelancerAwardedProjectStatusID', array(4,5));
$this->_ci->db->where('WhoCreated.UserID', 5);
$this->_ci->db->where('PB.Reviewgiven', 5);
$query = $this->_ci->db->get();

let me know if it's working.

People are also looking for solutions to the problem: config - Proper way to set php max_execution_time

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

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.