PHP Private method is not assept a parameter

Question

I want to make a class which will show random data from database and counts how many times it was shown.

The sequence is next.

1. I take random data from database.
2. I increase its view count.
3. I show taken (and increased) data on the page.

My code:

class Translate

private $db ;
private $result ;
private $foreign;
private $translation;

function __construct()
{
    $this->db = new PDO('mysql:host=localhost;dbname=translate;charset=utf8', 'root', '');
}

private function increaseView($word)   
{
    $sql = $this->db->prepare("UPDATE admin_words SET was_shown = was_shown + 1  WHERE in_english=   ' " . $word . " ' ");
    $sql->execute();        
}

public function getNewWord()
{
    $this->result = $this->db->query('SELECT * FROM admin_words ORDER BY RAND() LIMIT 1');
    $this->result = $this->result->fetchAll();

    // next string is not working
    $this->increaseView('book');

    return $this->result;
} 

I use it in next way:

$word = new Translate();
$texts = $word->getNewWord();

The trouble is not with $texts. It work great. But I can not send a parameter to

private function increaseView($word)    
{
    $sql = $this->db->prepare("UPDATE admin_words SET was_shown = was_shown + 1  WHERE in_english=   ' " . $word . " ' ");
    $sql->execute();        
}

by

// next string is not working
$this->increaseView('book');

The current behavior: My random data is shown as well but increasing of count is not happening. Any errors are shown. But if I use $sql = $this->db->prepare("UPDATE admin_words SET was_shown = was_shown + 1 WHERE in_english= 'book' "); 'book' instead of variable it updates a table.(work well)

What I do wrong?

Answer 1

Firstly you might want to use the prepared statement with bindParam as opposed to embedding variables directly in the sql - though there may be little opportunity for sql injection. The update method increaseView should use the value returned from the select query should it not - perhaps like this?

class Translate{

    private $db;
    private $result;
    private $foreign;
    private $translation;

    function __construct()
    {
        $this->db = new PDO('mysql:host=localhost;dbname=translate;charset=utf8', 'root', '');
    }

    private function increaseView($word)   
    {
        $sql = $this->db->prepare("UPDATE admin_words SET was_shown = was_shown + 1 WHERE in_english=:word");
        $sql->bindParam( ':word', $word );
        $sql->execute();        
    }

    public function getNewWord()
    {
        $this->result = $this->db->query('SELECT * FROM admin_words ORDER BY RAND() LIMIT 1');
        $this->result = $this->result->fetchAll();
        $this->increaseView( $this->result['in_english'] ); /* is this the field / column ?? */
        return $this->result;
    }
}

$word = new Translate;
$texts = $word->getNewWord();