ok, bear with me, i'm not quite a PHP noob but i am a PHP5 noob! haha

I know how to do prepared queries, thats fairly straights forward.

And i know about using variables to add to the queries later (cant remember what i was called) thats really cool.

Basically I've worked my way through these examples : http://wiki.cc/php/PDO_Basics

I want to make a generic SELECT function... any suggestions how to go about it? I realise it could rabildy get very complicated as soon as any joins come into it.

One thing I HATE with a passion is having SQL queries dotted around my websites. I'd like to have it all in one class if possible. If my idea of a generic function isnt the best approach, any other suggestions?

    Well, off the top of my head. You could have a repository of named queries as methods in a class. Call the method with the params you want to bind and the class returns the resultset.

    Don't know anything about pdo so I don't know how to integrate it with that, but I know it would work in a class you wrote yourself so I don't see why the class could not be using pdo to do the work.

    You would end up with just the method names scattered around your code instead of the sql. Be a neat way of maintaining your sql statements.

      There isn't much call to have a "generic SELECT function"; what you would have would be a collection of the specific queries the site actually uses, gathered into a single class as Roger Ramjet suggests (if you want, an abstract class that implements the database connectivity itself (this would be quite thin, as it's pretty much just what PDO does) and can be used elsewhere, and a concrete subclass for each particular application that contains the specific queries that are used, encapsulated in individual methods for accessing them).

        One option you should definitely consider is using views - you'll still need to write SELECT statements with WHERE clauses, but should be able to dispense with most (explicit) joins (assuming you create sensible views).

        Views are a really useful feature, but things can get silly - making views of views is possible but I don't recommend it from a maintenance perspective.

        Mark

          definitely use named views in DBs that have them. Unfortunately mysql does not and that is what most people who come here are using. The DBs that support named views also generally support stored procedures and/or pre-compiled parametirc queries as well, which pretty much dispenses with writing sql in code, you just call the access query or the mssql stored procedure.

          Makes a point for not using mysql at all when you think about it, excepy mysql is so robust and reliable and fast in this environment, not to mention FREE. It is also the one most available in hosted services.

            mySQL 5 has some of these features, right?

              And it had a lot of bugs and problems as well, though some of these may have been resolved by now - though that would have taken a major re-build not just bug-fixes. Not a lot of hosts have ver 5 available and probably will not for a long time either.

                MySQL 5 supports named views (as well as anonymous ones).

                MySQL 5 has been the stable version for quite some time, there is really no excuse for not using it for new projects.

                Mark

                  As I said, the main problem is that so few host are offering mysql 5 - except if you want to pay a lot for a dedicated server.

                  I was all gungho about mysql 5 when it first came out but then many people here reported major problems with the build. I'm glad to hear that those have been resolved, but that does not mean that many hosts are going to offer it now. That will probably only happen when thery start too loose business without it.

                    Roger Ramjet wrote:

                    As I said, the main problem is that so few host are offering mysql 5 - except if you want to pay a lot for a dedicated server.

                    I was all gungho about mysql 5 when it first came out but then many people here reported major problems with the build. I'm glad to hear that those have been resolved, but that does not mean that many hosts are going to offer it now. That will probably only happen when thery start too loose business without it.

                    Hopefully mySQL 5 hosting will catch on soon.

                    but having said that, php5 is taking long enough.......

                      Write a Reply...