PHP & MySQL: Novice to Ninja 6

[mike324]

Hi.

To anyone who has read this book… what do you think would be the easiest way to implement a DatabaseTable->find() when you have more than one WHERE clause such as: “WHERE id = somevalue AND parent_id != 0” ?

Cheers

[m_hutley]

What makes you so sure i want to find a AND b?

[rpkamp]

Something like this:

public function find(array $columns) {
    $searchParts = [];
    $parameters = [];

    foreach ($columns as $column => $value) {
        $searchParts[] = $column . '= :'.$column;
        $parameters[$column] = $value;
    }

    $query = 'SELECT * FROM ' . $this->table . ' WHERE ' . implode(' AND ', $searchParts);

    $query = $this->query($query, $parameters);

    return $query->fetchAll(\PDO::FETCH_CLASS, $this->className, $this->constructorArgs);
}

[mike324]

Hi, rpkamp.
Thank you very much for your reply. It looks like you have read the book and your code looks very much like what I need. It does change the find() method significantly from what it is. Can I just offer you the method as it stands and ask if your answer is still what I should do (I should have done this before, apologies):

 public function find($column, $value, $orderBy = null, $limit = null, $offset = null)
    {
        $query = 'SELECT * FROM '.$this->table.' WHERE '.$column.' = :value';

        $parameters = [
            'value' => $value,
        ];

        if ($orderBy != null) {
            $query .= ' ORDER BY '.$orderBy;
        }

        if ($limit != null) {
            $query .= ' LIMIT '.$limit;
        }

        if ($offset != null) {
            $query .= ' OFFSET '.$offset;
        }

        $query = $this->query($query, $parameters);

        return $query->fetchAll(\PDO::FETCH_CLASS, $this->className, $this->constructorArgs);
    }

Thank you in advance for your help. (It’s so nice to speak to someone who has actually read the book…)
Mike

[rpkamp]

You could quite easily combine our codes by changing your method to public function find($columns, $orderBy = null, $limit = null, $offset = null) and modifying it such that it treats $columns as an array like my code did.

@mike324

It’s so nice to speak to someone who has actually read the book…

I haven’t read it fully, but I’ve read bits and pieces. Enough to know the general direction it takes.
The rest is just practical knowledge from having done PHP for around 18 years now

[mike324]

Thank you rpkamp.

OK. I get you. I’ll give that ago. I’m up to about 18 weeks PHP experience but getting there before I die, I hope…