How do you avoid hard coding SQL statements in PHP?

You're looking for http://www.devshed.com/c/a/PHP/Database-Abstraction-With-PHP/. http://www.symfony-project.org is a PHP framework that makes this kind of thing very simple.

Depending on the SQL server you are using, Stored Procedures is the way to go for that problem. Here's a quick link i googled that gives the overview for PHP and MS SQL : http://www.sitepoint.com/article/php-microsoft-sql-server/. Basically, you write the inserts / updates in the SQL engine, and then call them almost like a function from PHP. If you need advice on the PHP - MySQL link, mefimail me, but it's very similar to the MSSQL.

Another way is to use an Object Relational Model like http://www.doctrine-project.org/, or a framework that includes a database abstraction layer like the aforementioned Symfony or http://framework.zend.com. I think PHPIgniter and CakePHP also include these features, but my experience is mostly with Symfony and Zend Framework because they have more advanced features that only come with PHP5.

soma lkzx: That's actually a cool little trick there. I'll pass it along to my PHP friend who never took to frameworks who still codes his PHP apps in Dreamweaver.

http://ca.php.net/manual/en/intro.pdo.php ships with PHP 5.1 (and higher) so you might already have it. It parameterizes and prepares plain old fashioned queries for execution. It's fairly easy (as in not much work) to refactor an existing codebase to use, and it handles things like properly escaping strings to prevent sql injection. A simplified but not atypical usage would be something to the effect of: $statement = $db->prepare( "select * from foo where bar = :baz" ); $statement->bindParam( ':baz', $baz, PDO::PARAM_STR ); $statement->execute(); while( $row = $statement->fetch( PDO::FETCH_ASSOC ) ) { echo $row['columnName']; }

Yeah, prepared statements or stored procs. Call either like a function; in fact you'll wat to make them only accessible through a function that validates/sanity checks the data (or at least the data's type, given that PHP is weakly typed), so they can only be called with valid data. Note that for selects with optional predicates, you can still have one SP, by using the null parameter trick. Consider the following sql statements: select * from person where lastname = ?; select * from person where lastname = ? and firstname= ?; select * from person where firstname= ?; Can you write this as one SP, rather than as three? Yes. Can you do that without putting all three statements and three "if" statements in the SP? Also yes. create proc find_person( @ln varchar, @fn varchar) as begin select * from person where ( lastname = @ln or @ln is null) and ( firstname = @fn or @fn is null); end; Then pass null for the parameters you don't care about, the values that should be "unbound". While this'll work on any RDBMS, some optimize the null out better than others (Sybase optimizes it well, MSSQL Server not so well), so if you do this, run a showplan. For prepared statements, this is less necessary, as you can create the prepared statement dynamically, while dynamically creating an SP shouldn't be allowed.