Array in MySql stored procedures

Hi guyz,

I saw a weird short coming in MySql stored procedures today.
It doesn't allow to pass in or pass out arrays.
The reason (I would say unreasonable reason) is that they only support variable types that they support for the data types in database storage. May be the architecture of MySql limits them. I'm not an expert of database engine architectures by any means but if other databases can offer, MySql MUST be able to offer this facility.

The alternative for this (as explained in MySql's developer forum) to create a temporary table in which you can store the output (or input) of the stored procedure and then you can fetch them.

Now, why would I do this? As for performance optimization purpose I would want to keep the database calls by PHP to MySql in minimum number. So when I want to make multiple fetch operation for a single task I would use stored procedure. But if I have to redirect my output in an another table I will have to make a PHP call to fetch the result from the tmp table anyways!! This would kill it's very reason.

You can pass CSV in string variable in place of a single dimension array, but what about multi dimension array? 9 out of 10 times you would need multi dimension array in practical scenario.

So what is the solution? Sadly, nothing.
Suddenly an alternative blinked to me and I tried it.
I simply furnished the output array in serialized format manually from the stored procedure.
Since the array became a string it was easy to pass to PHP, and unserialize an array in PHP is a cake walk!!

Try passing the following string from a stored procedure to PHP

a:2:{i:0;a:4:{s:5:"title";s:29:"MySql does not support arrays";s:11:"description";s:47:"Strange problem!! MySql does not support arrays";s:11:"website_url";s:20:"kapsdave.blogger.com";s:6:"number";i:1;}i:1;a:4:{s:5:"title";s:50:"How to pass out array in stored procedure in MySql";s:11:"description";s:35:"You can do this using serialization";s:11:"website_url";s:20:"kapsdave.blogger.com";s:6:"number";i:1;}}Array ( [0] => Array ( [title] => MySql does not support arrays [description] => Strange problem!! MySql does not support arrays [website_url] => kapsdave.blogger.com [number] => 1 ) [1] => Array ( [title] => How to pass out array in stored procedure in MySql [description] => You can do this using serialization [website_url] => kapsdave.blogger.com [number] => 1 ) )

And then unserialize the above text.
You will get an array similar to the following.

Array ( [0] => Array ( [title] => MySql does not support arrays [description] => Strange problem!! MySql does not support arrays [website_url] => kapsdave.blogger.com [number] => 1 ) [1] => Array ( [title] => How to pass out array in stored procedure in MySql [description] => You can do this using serialization [website_url] => kapsdave.blogger.com [number] => 1 ) )

I'm not sure if anybody has tried this before but I could not find anybody mentioning this on web so I finally decided to write a blog post.

Hope this helps!!

XSLT Using PHP

Hello,

PHP being a ridiculously easy web scripting language, can be used (and is used) as a great front end tool for any high scale application. In many cases you need to have a back end of Java, C++ etc. but it may not be a great idea to create front end (i.e. web end) using those languages. One of the way to combine these back end with front end would be the use of XSLT transformation.

Your back end can create an XML. Using PHP-XSLT you can create the HTML front end for this application. The original XML will content the variables that you want to show on the front end. XSLT - being a transformation language - would convert the XML to HTML. All three (XML, XSLT and HTML) being markup languages, this process is easy and fast too.

While using Java and C++ are such platforms that will require such transformation for front end, I have used it for .Net. I know .Net already has ASP.Net which can do this but in our requirement, we had to do the transformation from VB.Net to PHP. No matter how weird it may sound, but it was a genuine requirement mainly because of the legacy of the program. I would discuss it in detail sometime.

To use PHP-XSLT transformation refer this link - http://atom.dosimple.ch/docs/html/d1/d3/xslt_8class_8php-source.html