Using the database class to save comments

We will now register the comment and grade filled in by the visitor in the database. First, we will create a table in our database:

CREATE TABLE IF NOT EXISTS ps_mymod_comment (id_mymod_comment int(11) NOT NULL AUTO_INCREMENT, id_product` int(11) NOT NULL, grade tinyint(1) NOT NULL, comment text NOT NULL,date_add datetime NOT NULL,PRIMARY KEY (id_mymod_comment)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

As you can see, the table contains five fields: the line identifier (in auto-increment), the product identifier, the grade, the comment, and the date of the comment.

Note

In PrestaShop, the naming conventions for the database field are:

• Name your identifier field with the id_ prefix followed by the name of the table without the ps_ prefix
• Name your date with the date_ prefix, such as date_comment

Create your table with your SQL admin tool (mine is phpMyAdmin; yeah, I know, I'm a bit old school). Beware! If you chose a different prefix than ps_ when you installed PrestaShop, create this table with the same prefix.

Now, we have our HTML form and our table in the database. So, we just need to code the process to store comments in the database. We will use the same method we saw in Chapter 1, Creating a New Module, to split the process part from the display part. Let's create a method named processProductTabContent and add the condition to check whether the form has been submitted:

public function processProductTabContent()
{
  if (Tools::isSubmit('mymod_pc_submit_comment'))
  {
  }
}

public function hookDisplayProductTabContent($params)
{
  $this->processProductTabContent();
  return $this->display(__FILE__, 'displayProductTabContent.tpl');
}

We have to retrieve the POST data sent by the customer with the Tools::getValue method (that we saw in the previous chapter):

$id_product = Tools::getValue('id_product');
$grade = Tools::getValue('grade');
$comment = Tools::getValue('comment');

Then, we must save this data in the table we created. To do this, you can either make a direct SQL insert request (quicker way) or use an ObjectModel class (better way). For this part, we will use the quicker way, and for this, I will quickly introduce you the Db class of PrestaShop.

Each time you want to make a request to the database, you will have to instantiate the Db class with Db::getInstance(). This method will automatically connect to database if it's not already done. The eight main methods you will use are:

• insert($table, $data): This method is used to make an INSERT request. The parameters are the table name and an associative array ($fieldname | $value). It returns a Boolean result.
• update($table, $data, $condition): This method is used to make an UPDATE request. The parameters are the table name, an associative array ($fieldname | $value), and the WHERE condition (optional). It returns a Boolean result.
• Insert_ID(): This method will return the last ID inserted in this Db instance.
• executeS($sqlRequest): This method is used to make a SELECT request. The parameter is the SQL request and it will return an array containing the result lines.
• getRow($sqlRequest): This method is used to make a SELECT request on one line. The parameter is the SQL request, and it will return an array containing the result line. It automatically adds the LIMIT 1 parameter on your request.
• getValue($sqlRequest): This method is used to retrieve a single value with a SELECT request, which is generally used to get an identifier or a COUNT result.
• execute($sqlRequest): You can do any SQL request with this method, but we advise you to use the previous four methods if you want to do either a DELETE, INSERT, or UPDATE request. It returns a Boolean result, so it's not appropriate for SELECT.
• query($sqlRequest): This method is used by the seven other methods I described to make database requests. It works like the execute method, except no cache system applies to it and it returns the SQL result directly. So use it only if you can't do what you want with the seven other methods. In that case, take a look at how the DbQuery class works.

In our case, we will use the insert method to insert a comment in database, executeS to display the comments on your front office, and getValue to display the number of comments. First of all, we have to save it, so we will do the following in our processProductTabContent method:

if (Tools::isSubmit('mymod_pc_submit_comment'))
{
  $id_product = Tools::getValue('id_product');
  $grade = Tools::getValue('grade');
  $comment = Tools::getValue('comment');
  $insert = array(
    'id_product' => (int)$id_product,
    'grade' => (int)$grade,
    'comment' => pSQL($comment),
    'date_add' => date('Y-m-d H:i:s'),
  );
  Db::getInstance()->insert('mymod_comment', $insert);
}

Note

As you might have noticed, I casted an integer value with (int) and used the pSQL() method for the other POST value. This is to avoid SQL injection; you must always protect your data before making a SQL request. We will see more about security in Chapter 10, Security and Performance.

Now, go to your front office, choose a grade, fill in a comment, and submit. Then go to phpMyAdmin and check the content of your ps_mymod_comment table. You should see the content you just filled in.