On PHPBuilder.con today there's a new post from Jason Gilmore looking at using procedures and views in MySQL to refactor and simplify your site.

One particularly prominent mistake many programmers make is heavily intermingling the application logic and the domain model. PHP developers, for example, have a tendency to jam a tremendous amount of SQL into their website logic, resulting in undecipherable, unmaintainable code. [...] MySQL stored procedures and views can not only go a long way towards separating the logic and domain model, but they also can easily be integrated into PHP.

He includes several examples of how to create simple views in your database with tables from a possible e-commerce application holding order information and customer details.

On the 9Lessons blog today there's a new post looking at stored procedures - how to create them and how to use them from your PHP code.

Are you writing Stored Procedures if not please take a look at this post. Stored procedures can help to improve web application performance and reduce database access traffic. In this post I want to explain how to create and call the stored procedures from database server.

He shows you how to create a sample table and stored procedure on the server side (selecting a username from a users table) and how it compares to a normal SQL query. Two different ways to input values into the procedure are shown as well.

In this new post to his blog Chris Jones looks at an option to increase the performance of your PHP/Oracle application even more - converting a REF CURSOR into a piped data set via the PDO_OCI extension.

REF CURSORs are common in Oracle's stored procedural language PL/SQL. They let you pass around a pointer to a set of query results. However in PHP, PDO_OCI doesn't yet allow fetching from them. [...] One workaround, when you can't rewrite the PL/SQL code to do a normal query, is to write a wrapper function that pipes the output.

He includes an example, creating an example myproc() that contains the query to select the last names of all employees in the table. This procedure is put inside of a package so it can be called directly in the SQL statement and the ref cursor can be automatically piped to output.

On the EasyTech blog, there's a recent post for Zend Framework developers out there working with the Oracle database showing how to execute PL/SQL code from your Zend_Db queries.

Calling PL/SQL code from PHP can be tricky sometimes, specially when the PL/SQL procedure has input and output parameters. In this posting I will show you how to call a procedure from the PHP using Zend Framework. I will assume you have some experience using Zend Framework, specially the Database module (Zend_db).

He walks through the creation of a simple PL/SQL stored procedure and how to prepare your query to get results out of it (Zend_Db_Statement_Oracle and an execute call). There's a few stipulations you'll need to follow - named parameters, reserving space for the output and using references for output variables.

On the Internet Super Hero blog, they take a quick look at what's new in the MySQL native driver version that will be included in the upcoming PHP version, PHP 5.3.

PDO_MYSQLND is in the PHP CVS repository at php.net: PDO_MYSQL has been patched (PHP 5.3, PHP 6.0). Try out PDO_MYSQL with the MySQL native driver for PHP (mysqlnd). Its has new features.

They do a short recap of what the native driver libraries are all about and some of the advantages to using them. They look at some of the "memory tricks" supported by the driver and a look at the prepared statement and procedure support.

In a new post to his blog today, John Coggeshall comments on some thoughts from Alan Knowles about a method for making PHP obsolete.

Alan, I think you were smoking way too much PHP when you wrote this post.. This in particular really surprised me to hear you say [that a module that made mysql stored procedure calls based on a URL and returned JSON could make PHP obsolete]. While I do understand the concept your explaining, I simply can't see how the model is practical at all for two big reasons.

His reasons involve not having a business case where an entire application is right there for the user to download and that its an insecure method for running an app.

In a new post to his blog today, John Coggeshall comments on some thoughts from Alan Knowles about a method for making PHP obsolete.

Alan, I think you were smoking way too much PHP when you wrote this post.. This in particular really surprised me to hear you say [that a module that made mysql stored procedure calls based on a URL and returned JSON could make PHP obsolete]. While I do understand the concept your explaining, I simply can't see how the model is practical at all for two big reasons.

His reasons involve not having a business case where an entire application is right there for the user to download and that its an insecure method for running an app.

In a new post today, Maggie Nelson starts with the wrong way to do something - passing an array from PHP to a database - and works backward to make it all right.

It would be really useful to have an easy way to pass arrays as bound parameters to queries or procedures from PHP. This would be especially useful if you're letting Oracle handle most of your data manipulating (as you should).

She includes an example of how she's like it to work. Sadly, it doesn't but there are some ways that a developer could get close. Here's her process:

• No queries in loops, please!
• In the ideal world...
• Put all your DML in stored procedures.
• str2tbl
• The list_pkg package
• list_pkg in your procedure
• list_pkg in your PHP
• Leveraging list_pkg

The list_pkg is based around this article from AskTom.

In a new post today, Maggie Nelson starts with the wrong way to do something - passing an array from PHP to a database - and works backward to make it all right.

It would be really useful to have an easy way to pass arrays as bound parameters to queries or procedures from PHP. This would be especially useful if you're letting Oracle handle most of your data manipulating (as you should).

She includes an example of how she's like it to work. Sadly, it doesn't but there are some ways that a developer could get close. Here's her process:

• No queries in loops, please!
• In the ideal world...
• Put all your DML in stored procedures.
• str2tbl
• The list_pkg package
• list_pkg in your procedure
• list_pkg in your PHP
• Leveraging list_pkg

The list_pkg is based around this article from AskTom.

The A/R/T article repository (from php|architect) has posted the second part of their series covering stored procedure programming in MySQL by Ligaya Turmelle.

Now that we become familiar with the fundamentals of stored procedures it is time to start playing with the "Big Boy Toys". This article will go over stored procedures's built in error handling, the security features available, various "extras" available, what isn't allowed in a stored procedure, and some basic administration of the stored procedures. So lets stop talking and bust open the toy box and start playing!

Since they've already gotten the groundwork laid in the previous article, they jump right into the transactions in this part. In this example, they create a "mass insert", show hoe to create some error handling, add in a dash of security, and toss in a few extras. There's also a few small gotchas included to watch out for.