[SOLVED] where is the INSERT INTO statement

[I am Oleg]

Hello everyone!
I am currently working on a new module.

It is gonna be on the page where people choose the address of delivering and write comments to the order.
So, when the customer clicks on the next button, order.php is executed and, for example, if the customer has written something in a comment field, that text is written to the database to the ps_messages table.

My questions is: where is that SQL code (in what php file) that takes that text from that field and writes in to the base?

Thank you!

[rocky]

It's the following code on lines 184-202 of order.php that inserts the order message or updates it if you return to it before completing the order:

if (Tools::isSubmit('message') AND !empty($_POST['message']))
{
   if (!Validate::isMessage($_POST['message']))
       $errors[] = Tools::displayError('invalid message');
   elseif ($oldMessage = Message::getMessageByCartId(intval($cart->id)))
   {
       $message = new Message(intval($oldMessage['id_message']));
       $message->message = htmlentities($_POST['message'], ENT_COMPAT, 'UTF-8');
       $message->update();
   }
   else
   {
       $message = new Message();
       $message->message = htmlentities($_POST['message'], ENT_COMPAT, 'UTF-8');
       $message->id_cart = intval($cart->id);
       $message->id_customer = intval($cart->id_customer);
       $message->add();
   }
}

It uses the add() function in classes/ObjectModel.php to create the INSERT query, then the autoExecute() function in the classes/Db.php function to perform the query.

[I am Oleg]

Thank you! I'll try to sort it out!

Isn't it possible to add some SQL commands to php codes (in my case order.php), so they will be executed as well?

Thank you!

[rocky]

Sure, you could add an SQL query to the code above like this:

Db::getInstance()->execute('INSERT INTO `'._DB_PREFIX_.'message`' ...);

Replace ... with the rest of the INSERT statement. Change message to whatever table you want to insert into.

[I am Oleg]

OK!
I have tried to make this alive, but wasn't able to...

The thing I'm working on is next: I want to add one more field on the address choosing page (see the screenshot).
I have added the next code to the themes\order-address.tpl after this

<textarea cols="60" rows="3" name="message">{$oldMessage}</textarea></p>:

<textarea name="text"></textarea>

So, the thing I need is to get the value of my new textbox "text" and write it to the base.
I've created one more column in ps_messages tabel.

So, how is the script is gonna look like?
With my current PHP knowledge I can not figure out what Db::getInstance()->execute('INSERT INTO `'._DB_PREFIX_.'message`' ...); means. My any manipulations give me string errors. Maybe I only need to copy/paste 184-202 line's code in order.php and change "message" to "text" and create "insert into" command and that is it?

Please help me to figure it out. Ask for details if it's not clearly described!
Thank you!

[rocky]

If you create it as a separate message, then adding a new field to the message table is unnecessary. You could use code like the following:

Db::getInstance()->execute("INSERT INTO `"._DB_PREFIX_."message` (`id_cart`, `id_employee`, `id_order`, `message`, `private`, `date_add`) VALUES (".intval($cart->id).", 0, 0, '".$_POST['text']."', 0, NOW())");

But that is missing validation code to check that the customer actually entered a date. There is a new "Date of delivery" module in PreataShop v1.4 that might do what you want. You may be better off waiting for PrestaShop v1.4 final to be released.

[I am Oleg]

Parse error: syntax error, unexpected T_STRING in D:\xampp\htdocs\stores\prestashop\order.php on line 205

I've just paste the code you've gave me...

P.S. I am working on a module where custumers will be able to choose the date when they want to get items.

thank you!

[rocky]

I've updated the code. Try again.

[I am Oleg]

OK! It works! But in a different way: it creates to rays in the message table. But I want the first field to be noted in the "message" column and the second field - to be noted in the same ray, but in the column that I created, it is called "deliveryd". How do we realise this?

Thank you!

[rocky]

In that case, you will need to modify the Message class to add the deliveryd variable there, then you can change the message code above to:

if (Tools::isSubmit('message') AND (!empty($_POST['message']) OR !empty($_POST['text'])))
{
   if (!empty($_POST['message']) AND !Validate::isMessage($_POST['message']))
       $errors[] = Tools::displayError('invalid message');
   elseif (!empty($_POST['text']) AND !Validate::isMessage($_POST['text']))
       $errors[] = Tools::displayError('invalid delivery date');
   elseif ($oldMessage = Message::getMessageByCartId(intval($cart->id)))
   {
       $message = new Message(intval($oldMessage['id_message']));
       $message->message = htmlentities($_POST['message'], ENT_COMPAT, 'UTF-8');
       $message->deliveryd = htmlentities($_POST['text'], ENT_COMPAT, 'UTF-8');
       $message->update();
   }
   else
   {
       $message = new Message();
       $message->message = htmlentities($_POST['message'], ENT_COMPAT, 'UTF-8');
       $message->deliveryd = htmlentities($_POST['text'], ENT_COMPAT, 'UTF-8');
       $message->id_cart = intval($cart->id);
       $message->id_customer = intval($cart->id_customer);
       $message->add();
   }
}

[I am Oleg]

Strange!

It writes to the "message", but not to the "deliveryd" column...

[rocky]

Did you add the field to classes/Message.php? Change line 19 from:

/** @var string message content */
public         $message;    

to:

/** @var string message content */
public         $message;    

/** @var string message content */
public         $deliveryd;    

and lines 40-44 from:

protected    $fieldsRequired = array('message');
protected    $fieldsSize = array('message' => 1600);
protected    $fieldsValidate = array(
   'message' => 'isCleanHtml', 'id_cart' => 'isUnsignedId', 'id_order' => 'isUnsignedId',
   'id_customer' => 'isUnsignedId', 'id_employee' => 'isUnsignedId', 'private' => 'isBool');

to:

protected    $fieldsRequired = array('message', 'deliveryd');
protected    $fieldsSize = array('message' => 1600, 'deliveryd' => '100');
protected    $fieldsValidate = array(
   'message' => 'isCleanHtml', 'deliveryd' => 'isCleanHtml', 'id_cart' => 'isUnsignedId', 'id_order' => 'isUnsignedId',
   'id_customer' => 'isUnsignedId', 'id_employee' => 'isUnsignedId', 'private' => 'isBool');

and line 53 from:

$fields['message'] = pSQL($this->message, true);

to:

$fields['message'] = pSQL($this->message, true);
$fields['deliveryd'] = pSQL($this->deliveryd, true);

[I am Oleg]

Of course no!

Now I've done it! And it works fine!

Thank you! Now I need to customize it to make it look greater. Thank you for helping a smart man!

And the last question: where do I need to edit to make this field to show up under message on the order page in the control panel? Thank you!

[rocky]

Change line 841 of admin/tabs/AdminOrders.php from:

echo '
'.nl2br2($message['message']).'';

to:

echo '
'.nl2br2($message['message']).'';
echo '
'.nl2br2($message['deliveryd']).'';

[I am Oleg]

YES!

Thank you! Works perfectly!

[rocky]

Great. Please edit your first post and add [sOLVED] to the front of the title...