Re: [PHP4BETA] SQL abstraction

On Wed, 30 Jun 1999, Andrey Zmievski wrote: > Stig Bakken wrote: > > * Portable prepare/execute implementation. This means that your query > > should not have one syntax for Oracle, but another for Solid. This > > would require the abstraction layer to parse the query, but I think > > that's ok as long as we have defined what to look for. We could also > > implement a poor man's prepare/execute for the databases that don't > > have it, for people who don't like messing around with magic quotes (I > > don't.) I've done this for ODBC and OCI8, it's not very hard. > > A couple of comments related to this. > > If the abstraction layer is able to parse the query, then it should be > possible to automatically drop certain variables from the 'select' queries. > Example: > > $xval = ""; > $yval = "foo"; > db_prepare("select result from Text where X = ? and Y = ?", $xval, $yval, > ""); > > The second parameter specifies that if any of the variables have value "" > then the part of the query they occur in should be dropped. Since $xval is > "" in this case, the where clause becomes equivalent to "where Y = > '$yval'". > > This feature allows complicated queries to degenerate into simple ones when > the extra variables are not needed, without cumbersome checking of all the > parameters. A common use is in HTML forms, where there may be several search > fields that are optional (eg. an option checkbox, or subject and author text > fields). By setting the last parameter for db_prepare() to the empty string, > any unset checkboxes or empty (unfilled) text fields from the form can be > implicitly dropped from the SQL query. I see your point. At the very least, there should be a separate prepare function for it, due to the obscurity of its semantics. It would also have to handle stuff like "a is not null and b like ?". What about mixing AND and OR? Should it simply strip away the tests where the parameter is empty? Anyway this is definitely a "version 2" feature. - Stig