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.

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

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.

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

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.

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:

1
'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”¹ – 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.”² – 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.

1. MySQLi from Wikipedia [↩]
2. MySQL Improved Extention [↩]