dcblogdev/pdo-wrapper

search function example code

Opened this issue · 2 comments

`
/**

  • Search records with optional date range and additional parameters

  • @param string $table table name

  • @param array|null $date_range date range array with [start_date, end_date] keys (optional)

  • @param array $params additional parameters

  • @return array returns an array of records
    */
    public function search($table, $date_range = null, $params = [])
    {
    $sql = "SELECT * FROM $table";
    $queryParams = [];
    $dateConditionAdded = false;

    if ($date_range !== null && is_array($date_range) && count($date_range) >= 2) {
    $start_date = $date_range[0];
    $end_date = $date_range[1];

     if ($start_date !== null && $end_date !== null) {
         $sql .= " WHERE date >= :start_date AND date <= :end_date";
         $queryParams[':start_date'] = $start_date;
         $queryParams[':end_date'] = $end_date;
         $dateConditionAdded = true;
     }
    

    }

    foreach ($params as $key => $value) {
    if ($value !== null) {
    if (!$dateConditionAdded) {
    if (empty($queryParams)) {
    $sql .= " WHERE $key = :$key";
    } else {
    $sql .= " AND $key = :$key";
    }
    } else {
    $sql .= " AND $key = :$key";
    }
    $queryParams[":$key"] = $value;
    }
    }

    $stmt = $this->db->prepare($sql);
    $stmt->execute($queryParams);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    `

Errors may exist, but it serves my purpose. I would like to suggest adding the search function to the class. Thank you

SUGGESTION

Instead of calling it search, make it a select and the args would be (string $table, array $where = [])
and improve on the where such that you can use operators as well. And so you would have a more generic method.

example:

$db->select('users', [

    // simple = operator
    ['created_at' => date('Y-m-d')],

    // custom operator with nested assoc array
    ['created_at' => ['>=' => date('Y-m-d')]],

    // or it can like this
    ['created_at', '>=', date('Y-m-d')],
]);