Menu
Create Custom Side Menus
Frodo's Ghost | Using MySQL Stored Procedures in Cakephp
41
post-template-default,single,single-post,postid-41,single-format-standard,eltd-core-1.0.1,ajax_fade,page_not_loaded,,borderland - frodosghost-child-ver-1.0.0,borderland-ver-1.2, vertical_menu_with_scroll,smooth_scroll,no_animation_on_touch,side_menu_slide_with_content,width_470,paspartu_enabled,paspartu_on_bottom_fixed,wpb-js-composer js-comp-ver-4.4.4,vc_responsive

Using MySQL Stored Procedures in Cakephp

I am new to this topic in many ways. For ages I have prefered to do the database work in Cakephp – or being honest, I have been afraid to touch MySQL, being that close to the data seemed like a stupid idea. Especially because I wasn’t bold enough to go playing with things.

That being said, I have been needing to make my data transfers faster for one project I am currently working on (under wraps at this time, more news soon I’m sure). Every second query was returning nothing. Not good. So the first thing was to speed up the queries.

Firstly I tried turning debug to zero in the config file of cake – the error still appeared. So next move was to shift all the data and queries from the cakephp controller into MySQL and save all that database interaction. A point made by my brother and excellent codeman @ghostpsalm.

So I set about getting the queries into a procedure, which was entirely new ground, but with some google searching and watching others play with it already. I managed it together, that was until I tried to excecute it from Cakephp.

Stored Procedures in Cakephp

The Controller call was easy. I have the $this->query() call in the Model, so it just looks to the Model for the call, and sets the var to pass to the view, and sets the view as an ajax – which means it doesn’t render the layout around the view.

function show($site = null){
  Configure::write('debug', 0);
  if(!empty($site)){
    $advert = $this->Advert->findAd($site);
    $this->set('advert', $advert[0]);
    $this->render('show', 'ajax');
  }
}

Nice and easy. The Model was easy as well. Because I had setup the procedure to return data like Cakephp does dealing with the resulting data was easy.

function findAd($site) {
  $advert = $this->query('CALL display_ad("'.$site.'");');
  return $advert;
}

The $this->query() is the way to execute any SQL. Easy, just call the ‘display_ad’ procedure and the returned data is returned to the controller – where it is set for the view.

So with everything set up stock, I ran the page, and nothing worked. In fact I got this wonderful error : SQL Error: 1312: PROCEDURE telegraph.display_ad can’t return a result set in the given context. Another SQL error that explains nothing, I’m still not used to these and after a confusing day learning Stored Procedures it was not a nice error to end the day on.

There was an easy fix. First I changed the Table Engine on my display table from InnoDB to MyISAM – because it seemed like the better idea for the high read volumes the table would have. Edited basically once a month. I don’t think that was nessecary for it to run.

Changing the Table Engine

Next I had to change the database driver to be MySQLi instead of the standard MySQL. This was a simple change in config/database.php:

'driver' => 'mysqli'

Actually its a change I think I’ll be using from now on. “MySQLi is an improved version of the older PHP MySQL driver, offering various benefits” ((MySQLi from Wikipedia)) – So it seems from that and the PHP website that “The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above.” ((MySQL Improved Extention)) – sounds nice.

With those two changes I was able to get CakePHP to run a Stored Procedure. Nice and easy, so if you get that 1312 error running a Stored Procedure from Cake, give that a go, and let me know how you go.

james
12 Comments
  • Hey, I have added your site to http://planetcakephp.org/aggregator/sources/1244-james-rickard if you don’t mind.

    Cheers and keep baking!

    April 2, 2009 at 9:30 am
  • @Derick Ng – Cheers. It’s an honour to be on such a feed.

    @Adam – If by calling ‘them directly on the Model’ you mean calling them from the Controller, then yes, you are correct. You can do that. It looks like that is the case on book.cakephp.org : http://book.cakephp.org/view/456/query“ rel=”nofollow”>Query

    It would look like this:
    $advert= $this->Advert->query('CALL display_ad("'.$site.'");');

    But I do subscribe to the fat model skinny controller thing – as pointed out by http://cakebaker.42dh.com/2007/02/26/should-you-use-modelquery-in-the-controller/“ rel=”nofollow”>cakebaker. That being said I also like to keep the database transactions in the Model, and leave to Controller to do what it does.

    April 2, 2009 at 11:56 am
  • problems with stored procudure is that
    it is DB specific. what happen if one day
    you would like to change database, does the
    code in store procedure will still work?

    April 2, 2009 at 2:56 pm
  • Ture. It is database specific. If I was to change from MySQL to something else I would no doubt have to spend time figuring out how to change it over.

    Of course, at this stage I’m more than happy making it work with MySQL, so the problem is nulified.

    April 2, 2009 at 11:42 pm
  • […] dar neteko naudoti, bet pastebjau ger ra ta tema. Pirmiausia duomen bazs nustatymuose (config/database.php) nustatom […]

    April 3, 2009 at 5:35 pm
  • procsharp@gmail.com
    Reply

    1. @Adam don’t put any thing that smell like sql outside your model, among other reasons see point 2 .
    2. @ajmacaro stored procedure is the best performance choice so it’s better to invest in the performance area being a 24/7 issue then investing in the ability to change database, which probably will not happen.
    If you keep all your sql related syntax in the Model you can be sure to touch only your model files in case of migrating to new database type.

    March 22, 2010 at 11:10 am
  • You should be aware that the ->query method doesn’t protect you against SQL injection so you have to do it yourself.

    When you are making queries by concatenating strings, you must use mysql_real_escape_string

    This would be a safe implementation:

    function findAd($site) {
    uses(“Sanitize”);
    $advert = $this->query(‘CALL display_ad(“‘.Sanitize::escape($site).'”);’);
    return $advert;
    }

    October 22, 2010 at 5:07 pm
  • mysql_real_escape_string or Sanitize::escape form CakePHP 🙂

    October 22, 2010 at 5:08 pm
  • santanu.c@webspiders.com
    Reply

    Thanx a lot for your help, this is the thing exactly I have wanted

    March 1, 2011 at 1:34 pm
  • nirmalsharmamca@gmail.com
    Reply

    Thanks a lot.
    Its helpfull for me thank you dear. 🙂

    September 19, 2012 at 7:27 am
  • trilok.gupta@anktech.co.in
    Reply

    $advert = $this->query(‘CALL display_ad("’.$site.’");’);

    how to use displayad().
    i did not understand what is the functionality of display
    ad().

    May 8, 2013 at 11:27 am
    • james@frodosghost.com
      Reply

      Hi Trilok,
      The display_ad() is the stored procedure that I have created inside MySQL. It returns the result.

      So CALL display_ad() is how you run the stored procedure with MySQL code, which is why it is in the query.

      May 8, 2013 at 11:14 pm

Post a Comment