How to use MySQL functions in CakePHP save() function

Question

I've MySQL Query like this

INSERT INTO users (firstname, amount) VALUES ('Suresh', ABS('-400'));

It inserts data like this

Suresh | 400

Same Query with CakePHP Model

$this->User->query("INSERT INTO users (firstname, amount) VALUES ('Suresh', ABS('-400'))");

It inserts data like this

Suresh | 400

Now My Question is How Can I use MySQL functions with CakePHP save() function

if ($this->request->is('post')) {
     $this->User->create();
     $this->request->data['User']['firstname'] = "Suresh";    //This is inserting in database
     $this->request->data['User']['amount'] = "ABS('-400')";  //This is not inserting in database
     $this->User->save($this->request->data);
}

Please let me know where I'm doing wrong?

Yeah I've tried with ABS(-400) I can use PHP function, but I want to use MySQL functions @sagi — Shri Suresh
– Shri Suresh, Commented Mar 30, 2016 at 7:11
You should have choosen a different example. ABS() can be easily solved at PHP level, there is absolutely no need to handle this at SQL level. — ndm
– ndm, Commented Mar 30, 2016 at 7:26
I know this can be handled by PHP side also, but I'm curious to know how can i use MySQL function with save() function, It allows in query() function but not allowing in save() function. @ndm — Shri Suresh
– Shri Suresh, Commented Mar 30, 2016 at 7:29

ndm · Accepted Answer · 2016-03-30 21:26:06Z

3

You could try to use a DboSource expression, for example:

$this->request->data['User']['amount'] =
    $this->User->getDataSource()->expression('ABS(-400)');

Expression objects are not going to be sanitized or escaped, but inserted into the query as is.

A word of warning, be careful what you are passing, in case the -400 would actually be user input, you would need to make sure that it is being sanitized/casted/validated properly!

3 Comments

Shri Suresh Over a year ago

After applying your code i got this error "Non-static method DboSource::expression() should not be called statically, assuming $this from incompatible context.." Is there anything I'm missing? or I've to include anything?

ndm Over a year ago

@ShriSuresh Just like the error message says, the method isn't static, so it shouldn't be called that way. Use $this->User->getDataSource()->expression() instead. And a word of warning, be careful what you are passing, in case the -400 would actually be user input, you would need to make sure that it is being sanitized/casted properly!

Michael Clark Over a year ago

I used $connection->newQuery()->newExpr('WorkoutOrder + 1') successfully to increment a column, note the warning from ndm though

Alimon Karim · Accepted Answer · 2016-03-30 07:23:03Z

0

Change your code

$this->request->data['User']['amount'] = "ABS('-400')";  // here you are sending string.

To

$this->request->data['Album']['amount'] = ABS('-400');

answered Mar 30, 2016 at 7:23

Alimon Karim

4,48910 gold badges46 silver badges72 bronze badges

1 Comment

Shri Suresh Over a year ago

I would Like to use MySQL functions not PHP function, in above case it's using PHP function.

Collectives™ on Stack Overflow

How to use MySQL functions in CakePHP save() function

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related