How do you search your database?

[Peter Karunyu]

I've been going through some of the code I've written to search tables. Nothing fancy really, but I got thinking that maybe there is someone here who has done it more elegantly.

Ok, below is a sample of the code I use:

function searchPurchaseOrders($searchTerm)
{
    $sql = "
    SELECT p.id,p.po_number,p.project,p.package_no,p.po_creation_date,p.atd_ex_works, p.etd_ex_works,
    p.customer,p.contractor,p.site,p.stock_code,p.part_number,p.line_items,p.unit_measure,p.order_qty,
    p.standing_qty,p.supplier,p.date_required,p.priority,p.archived
    FROM `purchase_orders` p
    WHERE deleted=0 AND archived=0 AND
    (p.po_number LIKE '%$searchTerm%' OR
    p.po_creation_date LIKE '%$searchTerm%' OR
    p.project  LIKE '%$searchTerm%' OR
    p.package_no  LIKE '%$searchTerm%' OR
    p.atd_ex_works  LIKE '%$searchTerm%' OR
    p.etd_ex_works  LIKE '%$searchTerm%' OR
    p.customer LIKE '%$searchTerm%' OR
    p.contractor LIKE '%$searchTerm%' OR
    p.site LIKE '%$searchTerm%' OR
    p.stock_code LIKE '%$searchTerm%' OR
    p.part_number LIKE '%$searchTerm%' OR
    p.line_items LIKE '%$searchTerm%' OR
    p.unit_measure LIKE '%$searchTerm%' OR
    p.order_qty LIKE '%$searchTerm%' OR
    p.standing_qty LIKE '%$searchTerm%' OR
    p.supplier LIKE '%$searchTerm%' OR
    p.date_required LIKE '%$searchTerm%' OR
    p.priority LIKE '%$searchTerm%')
    ORDER BY p.id DESC";
   
    $result = $this->db->query($sql);
    if ($result)
    {
       return $result;
    }
    else {
        return FALSE;
    }
}

[Felix mwai]

To me the code looks okay because it achieves the intended purpose. I mostly use active records pattern simply because i find it simple to debug and design.I would have done it like this

    $this->db->select('p.id', 'p.project');
    $this->db->from('purchase_orders p');
    $this->db->like('p.project', $searchTerm);
    $this->db->or_like('p.customer', $searchTerm);
.................................................................
.................................................................continues





...............

--
Regards
Felix Mwai
Software Developer.
Mobile 0720453684.

[Isaak Mogetutu]

For easier Design and debugging, create the array of columns to the affected, can be fixed or user driven. Then loop through them.[just thinking]

$this->db->select('Your columns here...');
$this->db->from('tablename');
foreach($columns as $column){
    $this->db->or_like($column, $search_term);
}
 $result = $this->db->get();

[Peter Karunyu]

Active record seems more elegant, I will give it a try.

--
Regards,
Peter Karunyu
-------------------

[Peter Karunyu]

I like the loop part, I hadn't looked at it that way, thanks!