How to use sql queries in classes

Question

I have a question on what is the best way of implementing SQL queries in PHP classes. I want to keep the queries as low as possible.

This was my first attempt:

class NewsArticle
{
    //attributes
    private $newsArticleID;

    //methodes
    //constructoren
    public function __construct($newsArticleID)
    {
        $this->newsArticleID = $newsArticleID;
    }

    //getters   
    public function getGeneralData()
    {
        $query  = mysql_query("SELECT author, title, content, datetime, tags FROM news WHERE news_id = '$this->newsArticleID'");
        $result = mysql_fetch_array($query);

        $data = array(
            'author' => $result['author'],
            'title' => $result['title'],
            'content' => $result['content'],
            'datetime' => $result['datetime'],
            'tags' => $result['tags']
            );

        return $data;
    }

    //setters
}

Now I'm wondering if it would be better to create a setter for generalData and for each item I retrieve from the database create a variable and assign the proper value. Then I could create a getter for each variable. So like this.

class NewsArticle
{
    //attributen
    private $newsArticleID;
    private $author;
    private $title;
    // more variables

    //methodes
    //constructoren
    public function __construct($newsArticleID)
    {
        $this->newsArticleID = $newsArticleID;
    }

    //getters    
    public function getAuthor()
    {
        return $this->author;
    }

    public function getTitle()
    {
        return $this->title;
    }

    //setters
    public function setGeneralData()
    {
        $query  = mysql_query("SELECT author, title, content, datetime, tags FROM news WHERE news_id = '$this->newsArticleID'");
        $result = mysql_fetch_array($query);

        $this->author = $result['author'];
        $this->author = $result['author'];

        //other variables
    }
}

Please stop using `mysql_*` functions: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Mave, Dec 19 '14 at 09:28
Welcome to SO! On this board, we fix the code that doesn't work (wrong results, errors etc). If you're just looking for an advice how to make your code better, our [code review](http://codereview.stackexchange.com/) site is the proper place. — georg, Dec 19 '14 at 09:35
@georg Thanks, I will use code review the next time. I'm pretty new to stack, thanks for the heads up. — Ruben Vermeulen, Dec 19 '14 at 10:46

score 0 · Answer 1 · answered Dec 19 '14 at 09:38

The point of using setters and getters is that by forcing the developer to use these you can implement more complex access mechanisms, for example setting a flag when a value is set so that your class knows to write it back into the database (although this is a rather bad example as the objective can be acheived without having to force access via a method).

BTW

news_id = '$this->newsArticleID'

No.

Numbers shouldn't be quoted (breaks the optimizer) and strings should be escaped at the point where they are used in a query.

Thanks for the information and I will delete the quotes. – Ruben Vermeulen Dec 19 '14 at 10:48 — Ruben Vermeulen, Dec 19 '14 at 10:48

score 0 · Answer 2 · answered Dec 19 '14 at 09:45

My advice for you would be to start using PHP-PDO and stored procedures of MySQL. Although you will see a small time difference in using direct queries in php code and in using stored procedures (a difference of milliseconds), you will have less code to write in php. To my opinion that way you would have a more BALANCED application. Also use getData and setData functions that should return anything you want in a form of array or json (i think you will find this very interesting in case you want to use a browser's local storage).

How to use sql queries in classes

2 Answers2