faster,better way to get stuff from db + add/edit theory

[eluser]xpix[/eluser]
How would you improve the following:
(I am trying to get the status field from jobs table)

Code:

//get job status
        $this->db->select('status');
        $this->db->where('id', $id);
        $query = $this->db->get('jobs');
        $queryresult = $query->result();
        if($queryresult[0]->status){
            
        }else{
            
        }

Thx

[eluser]sophistry[/eluser]
what you have is perfectly fine.

but, if you only want one row the best thing to do is to use LIMIT either in Active Record method or as a parameter sent to the get method. then to get just one row from the $query you use $query->row().

this cleans up the code a little.

Code:

$this->db->select('status');
$query = $this->db->get_where('jobs', array('id' => $id), 1);
$row = $query->row();
if($row->status){
            
}else{
            
}

[eluser]xpix[/eluser]
nice

Thx

[eluser]Yash[/eluser]

Code:

$this->db->select('status');
$this->db->where('id', $id);
$query = $this->db->get('jobs');
$result=$query->result_array();

print_r($result) ; // if want to use  direct array

[eluser]xpix[/eluser]
another thing

how do you manage add and edit controllers into one

Eg

Code:

function addUser(){
  //add user form
  $rules['name'] = "trim|required|max_length[100]|xss_clean";
  $rules['email'] = "trim|required|valid_email";
  $rules['pass'] = "trim|required|min_length[4]|max_length[100]";
  $this->validation->set_rules($rules);
  
  //re-populating
  $fields['name']    = 'Name';
  $fields['email'] = 'Email';
  $fields['pass'] = 'Password';
  $this->validation->set_fields($fields);
  
  if ($this->validation->run() == FALSE){
      //nothing to do - the view is loaded by default                
  }else{
    //get data for db insert
    $user = array(
      'name' => $this->input->post('name'),
      'email' => $this->input->post('email'),
      'pass' => $this->input->post('pass')
    );
    //insert job
    $this->db->insert('users', $user);
  }
}


function editUser($id){
  $data['id'] =  $id;//show links
  
  $query = $this->db->get_where('users',array('id' => $id));
  $result = $query->result();
  //get current fields value to display in view
  $data['oldName'] = $result[0]->name;
  $data['oldEmail'] = $result[0]->email;
  $data['oldPass'] = $result[0]->pass;
  
  //add user form
  $rules['name'] = "trim|required|max_length[100]|xss_clean";
  $rules['email'] = "trim|required|valid_email";
  $rules['pass'] = "trim|required|min_length[4]|max_length[100]";
  $this->validation->set_rules($rules);
  
  //re-populating
  $fields['name']    = 'Name';
  $fields['email'] = 'Email';
  $fields['pass'] = 'Password';
  $this->validation->set_fields($fields);
  
  if ($this->validation->run() == FALSE){
      $this->load->view('editstudent',$data);            
  }else{
    //get data for db insert
    $user = array(
    'name' => $this->input->post('name'),
    'email' => $this->input->post('email'),
    'pass' => $this->input->post('pass')
    );
    //insert job
    $this->db->where('id', $id);
    $this->db->update('users', $user);
    redirect('backend/students');
  }
}

Would you use a function user($action, $id) where $action is "add" or "edit"?

Is there a better way?

[eluser]sophistry[/eluser]
yes, "don't repeat yourself" (sometimes people make this an acronym DRY).

since add and edit do the same thing to the same table try to cut down on extra code to save yourself typing and debugging.

seems like you might be ready to check out IgnitedRecord. http://ellislab.com/forums/viewthread/78305/

[eluser]xpix[/eluser]
Thx, I will check that out

Meanwhile is there a better way for

Code:

$myquery = "select jobs.id, jobs.title, jobs.company, jobs.address,";
$myquery .="( SELECT COUNT(*) FROM applied WHERE applied.userId = ? AND jobs.id = applied.jobId) as applied,";
$myquery .="( SELECT COUNT(*) FROM approved WHERE approved.userId = ? AND jobs.id = approved.jobId) as approved";
        $myquery .=" from jobs where jobs.status = 1";
        
$query = $this->db->query($myquery, array($studentId,$studentId));
$data['job_list'] = $query->result();

(not an expert in SQL)

Unfortunately could not figure how to do it with joins

[eluser]codex[/eluser]
[quote author="xpix" date="1217553874"]Thx, I will check that out

Meanwhile is there a better way for

Code:

$myquery = "select jobs.id, jobs.title, jobs.company, jobs.address,";
$myquery .="( SELECT COUNT(*) FROM applied WHERE applied.userId = ? AND jobs.id = applied.jobId) as applied,";
$myquery .="( SELECT COUNT(*) FROM approved WHERE approved.userId = ? AND jobs.id = approved.jobId) as approved";
        $myquery .=" from jobs where jobs.status = 1";
        
$query = $this->db->query($myquery, array($studentId,$studentId));
$data['job_list'] = $query->result();

(not an expert in SQL)

Unfortunately could not figure how to do it with joins[/quote]

I don't have the solution, but was just wondering: is the '?' in the query valid?

[eluser]Hannes Nevalainen[/eluser]
The '?' will get replaced by the data in the second param to $this->db->query();

[eluser]codex[/eluser]
[quote author="Hannes Nevalainen" date="1217554373"]The '?' will get replaced by the data in the second param to $this->db->query();[/quote]

Interesting. Never heard of this function.