Question 275280 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to replace several similar functions in a model with just one?

at the moment I’m using these functions for my queries:

public function Surat_300() {
    $query = $this->db->query(" SELECT * FROM `ts_300` WHERE `status_surat` = '1' ");
    return $query->num_rows();
}

public function Surat_336_a() {
    $query = $this->db->query(" SELECT * FROM `ts_336_a` WHERE `status_surat` = '1' ");
    return $query->num_rows();
}

public function Surat_336_b() {
    $query = $this->db->query(" SELECT * FROM `ts_336_b` WHERE `status_surat` = '1' ");
    return $query->num_rows();
}

public function Surat_336_c() {
    $query = $this->db->query(" SELECT * FROM `ts_336_c` WHERE `status_surat` = '1' ");
    return $query->num_rows();
}
Tags:

2

Answers


  1. 0

    for this kind of scenario you’d use a variable (e.g.: $table) in your query string, to represent the table you are wanting the data from:

    this variable could be received in a controller as a $_POST variable from an input field or via ajax call, as $_SESSION variable, URL, etc.

    public function Surat($table) { 
  $query = $this->db->query(" SELECT * FROM $table WHERE status_surat = '1' "); 
  return $query->num_rows(); 
}

    since this is labeled Codeigniter, I’d suggest to take advantage of the framework using the following syntax to produce safer queries:

    public function Surat($table) { 
  $query = $this->db->query->where('status_surat', 1) 
                           ->get($table); 
  return $query->num_rows();    
}
// change 1 to '1' if status_surat represents a string, not a boolean/numeric.

    from a controller you’d call the model function this way:

    $myrows=$this->My_model->Surat('ts-300');
    Login or Signup to reply.
  2. 0

    Your four existing methods are retrieving all records from each table, just to return the row count. It would be much more efficient to use SELECT COUNT(*) FROM `ts_300` WHERE `status_surat` = 1 instead.

    You could pass in the table name, as suggested by Vickel:

    public function SuratCount($table) {
    return $this->db->from($table)
            ->where('status_surat', 1)
            ->count_all_results();
}

    This feels a bit wrong, as these methods probably belong in their respective models anyway.

    It’s not possible to be sure from your question, but it seems likely that these four tables contain data of the same structure, in which case 300, 336_a, 336_b, etc should probably be values in a type identifier in a single table containing all of this data.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search