PHP OOP avoid having to repeat similar queries in methods

Question

Im learning, and am still struggling to get the hang of many OOP concepts so please keep that in mind when reading / answering the question.

So one of the primary purposes of object orientated programming is not to repeat yourself right. However, when I am creating methods, I constantly find myself repeating the same statements when querying the database. As can be seen in the below code taken from class I created.

 function getCategory()
    {
        $sql = "SELECT * FROM jobs";
        $stmnt = $db->prepare($sql);
        $stmnt->execute();
        $results = $stmnt->fetchAll();

        foreach ($results as $result) {
            $cat[] = $result['category'];
        }
        return  $this->category = $cat
    }
    function selectCategory($selectedCategory){
        $sql = "SELECT * FROM jobs
        WHERE  category =:category";
        $stmnt = $db->prepare($sql);
        $stmnt->bindValue(':category', $selectedCategory);
        $stmnt->execute();
        $results = $stmnt->fetchAll();
        foreach($results as $result){
            $result= array('category' => $result['category'], 'headline' => $result['headline']);
        }
        return $this->category = $result
    }// selectCategory

My question.

Is there a way / what should I do to avoid having to continuously write the same database queries in my methods? I feel im a little out of depth here, however its not going to stop me from trying. Any help, advice welcomed (please keep in mind im a beginner)

Im not quite getting you... im trying to avoid having to write select statements over and over! Appologies if my knowledge is lacking here but I dont understand how your suggestion can help solve my problem — Tim C
– Tim C, Commented Apr 6, 2017 at 3:36
@PHPglue I find myself writing similar statements like this $sql = "SELECT * FROM jobs WHERE category =:category"; $stmnt = $db->prepare($sql); $stmnt->bindValue(':category', $selectedCategory); $stmnt->execute(); in many of my methods (thus I am repeating code to much) And looking for a way how I can avoid it — Tim C
– Tim C, Commented Apr 6, 2017 at 3:38
To me, OOP helps to avoid repeating yourself in the client side, I mean, when you're actually using the classes (as objects). So you do have to write similar methods that don't do the exact same thing, but use the same "format" or "design", this is just to avoid messing up your index files with SQL stuff. In any case, I only do this to save data in class variables (select), insert, update and delete from my databases, then all other methods feed from the variables, so I don't need to do much SQL and I don't repeat myself too much, maybe 4 times at most. — Zeke
– Zeke, Commented Apr 6, 2017 at 3:51

meda · Accepted Answer · 2017-04-06 03:44:04Z

2

You can run the query to get all data, then extract the data from fetched data using PHP.

But I don't like it, and it is not efficient.

You have 2 different query , so you need to calls, just improve your code a little more, you can make a model like this:

class Category
{
    private $db;

    public function __construct(PDO $db)
    {
        $this->db = $db;
    }


    function getAll()
    {
        return  $this->db->query('SELECT * FROM jobs');
    }

    function getByCategory($category){
        $stmt = $this->db->prepare(
            "SELECT * FROM jobs WHERE  category =:category"
        );
        $stmt->bindValue(':category', $category);
        $stmt->execute();
        return $stmt->fetchAll();
    }
}

This is perfectly fine, and it is not really repeating

answered Apr 6, 2017 at 3:44

meda

45.5k14 gold badges96 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim C Over a year ago

Thank you kind sir, one question if I may. In the getCaregory() method how would you handle the data from return $stmnt->fetchAll() ? Will you created a different method for processing that data or will you handle the data processing in the getCategory() method?

meda Over a year ago

No do not process data inside of the model, this should be done outside, by a controller or something else

Tim C Over a year ago

OK I get you! Thank you very much

meda Over a year ago

your other options is to use a framework or ORM like eloquent from laravel

Tim C Over a year ago

thank you for your comment. Quick question if you dont mind. Would you say it is best to first learn OOP from scratch before jumping into a framework, or would you advice starting with a framework straight away...?

Collectives™ on Stack Overflow

PHP OOP avoid having to repeat similar queries in methods

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related