I am having some problems with sql. I think that this is due to something I don't understand re: working with more than one table at the same time.
Below I am stepping through the titles table. I get the $title and I am trying to record how many times that title appears in the employees table. I have 4 where statements to use on the employees table. Is there some way for me to inform SQL which table I am referring to? I thought that count_all_results would actually run the query. It always comes up with zero.
I can manually view more than one re-occurrance of the title. In the 3 lines below that I am trying to set all of the rows who meet the filter with employee_count = number of employees in the employee_table who have that same title.
PHP Code:
$query = $this->db->query("SELECT * FROM titles;");
foreach ($query->result() as $title_res)
{
$id=$title_res->id;
$title=$title_res->title;
$this->db->where("qualified='yes'");
$this->db->where("employee_title='$title'");
$this->db->where('email', $_SESSION['userid']);
$this->db->where('campaign', $_SESSION['campaign']);
$num_results = $this->db->count_all_results('employees');
$this->db->set('employee_count', $num_results);
$this->db->where('id', $id);
$this->db->update('titles');
How do i tell SQL which table I am referring to?
Next I am trying to sum all of the w2_wages fields for a specific title in the employees table.
This is causing an error: "Unknown column 'employee_title' in 'where clause'". But, employee_title is, infact, a field in the employees table. This makes me think that SQL thinks that I am referring to the titles table instead. I do want to update the titles table at the bottom, but with data that I summed from the employees table.
PHP Code:
$total_salaries=0;
$this->db->where("qualified='yes'");
$this->db->where("employee_title='$title'");
$this->db->where('email', $_SESSION['userid']);
$this->db->where('campaign', $_SESSION['campaign']);
$this->db->select_sum('w2_wages',"$total_salaries"); //summing fields from the employees table
$this->db->set(['dollars_count' =>$total_salaries]);
$this->db->where('id', $id);
$this->db->update('titles');
One more thing. I realize that with Query Builder, this is not straight SQL. I think this is my problem. I see the Query Builder Class documentation on line. I'd love to see the SQL being created by Query Builder prior to executing it. Is there a pdf version of the QB documentation so I can print it out or can anyone recommend a book on
Active Record Database Pattern?
proof that an old dog can learn new tricks
