[6779] | 1 | <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> |
---|
| 2 | |
---|
| 3 | <html> |
---|
| 4 | <head> |
---|
| 5 | <title>Tips on Writing Portable SQL for Multiple Databases for PHP</title> |
---|
| 6 | </head> |
---|
| 7 | |
---|
| 8 | <body bgcolor=white> |
---|
| 9 | <table width=100% border=0><tr><td><h2>Tips on Writing Portable SQL </h2></td><td> |
---|
| 10 | <div align=right><img src="cute_icons_for_site/adodb.gif"></div></td></tr></table> |
---|
| 11 | <p>Updated 6 Oct 2006. Added OffsetDate example. |
---|
| 12 | <p>Updated 18 Sep 2003. Added Portable Native SQL section. |
---|
| 13 | <p> |
---|
| 14 | |
---|
| 15 | If you are writing an application that is used in multiple environments and |
---|
| 16 | operating systems, you need to plan to support multiple databases. This article |
---|
| 17 | is based on my experiences with multiple database systems, stretching from 4th |
---|
| 18 | Dimension in my Mac days, to the databases I currently use, which are: Oracle, |
---|
| 19 | FoxPro, Access, MS SQL Server and MySQL. Although most of the advice here applies |
---|
| 20 | to using SQL with Perl, Python and other programming languages, I will focus on PHP and how |
---|
| 21 | the <a href="http://adodb.sourceforge.net/">ADOdb</a> database abstraction library |
---|
| 22 | offers some solutions.<p></p> |
---|
| 23 | <p>Most database vendors practice product lock-in. The best or fastest way to |
---|
| 24 | do things is often implemented using proprietary extensions to SQL. This makes |
---|
| 25 | it extremely hard to write portable SQL code that performs well under all conditions. |
---|
| 26 | When the first ANSI committee got together in 1984 to standardize SQL, the database |
---|
| 27 | vendors had such different implementations that they could only agree on the |
---|
| 28 | core functionality of SQL. Many important application specific requirements |
---|
| 29 | were not standardized, and after so many years since the ANSI effort began, |
---|
| 30 | it looks as if much useful database functionality will never be standardized. |
---|
| 31 | Even though ANSI-92 SQL has codified much more, we still have to implement portability |
---|
| 32 | at the application level.</p> |
---|
| 33 | <h3><b>Selects</b></h3> |
---|
| 34 | <p>The SELECT statement has been standardized to a great degree. Nearly every |
---|
| 35 | database supports the following:</p> |
---|
| 36 | <p>SELECT [cols] FROM [tables]<br> |
---|
| 37 | [WHERE conditions]<br> |
---|
| 38 | [GROUP BY cols]<br> |
---|
| 39 | [HAVING conditions] <br> |
---|
| 40 | [ORDER BY cols]</p> |
---|
| 41 | <p>But so many useful techniques can only be implemented by using proprietary |
---|
| 42 | extensions. For example, when writing SQL to retrieve the first 10 rows for |
---|
| 43 | paging, you could write...</p> |
---|
| 44 | <table width="80%" border="1" cellspacing="0" cellpadding="0" align="center"> |
---|
| 45 | <tr> |
---|
| 46 | <td><b>Database</b></td> |
---|
| 47 | <td><b>SQL Syntax</b></td> |
---|
| 48 | </tr> |
---|
| 49 | <tr> |
---|
| 50 | <td>DB2</td> |
---|
| 51 | <td>select * from table fetch first 10 rows only</td> |
---|
| 52 | </tr> |
---|
| 53 | <tr> |
---|
| 54 | <td>Informix</td> |
---|
| 55 | <td>select first 10 * from table</td> |
---|
| 56 | </tr> |
---|
| 57 | <tr> |
---|
| 58 | <td>Microsoft SQL Server and Access</td> |
---|
| 59 | <td>select top 10 * from table</td> |
---|
| 60 | </tr> |
---|
| 61 | <tr> |
---|
| 62 | <td>MySQL and PostgreSQL</td> |
---|
| 63 | <td>select * from table limit 10</td> |
---|
| 64 | </tr> |
---|
| 65 | <tr> |
---|
| 66 | <td>Oracle 8i</td> |
---|
| 67 | <td>select * from (select * from table) where rownum <= 10</td> |
---|
| 68 | </tr> |
---|
| 69 | </table> |
---|
| 70 | <p>This feature of getting a subset of data is so useful that in the PHP class |
---|
| 71 | library ADOdb, we have a SelectLimit( ) function that allows you to hide the |
---|
| 72 | implementation details within a function that will rewrite your SQL for you:</p> |
---|
| 73 | <pre>$connection->SelectLimit('select * from table', 10); |
---|
| 74 | </pre> |
---|
| 75 | <p><b>Selects: Fetch Modes</b></p> |
---|
| 76 | <p>PHP allows you to retrieve database records as arrays. You can choose to have |
---|
| 77 | the arrays indexed by field name or number. However different low-level PHP |
---|
| 78 | database drivers are inconsistent in their indexing efforts. ADOdb allows you |
---|
| 79 | to determine your prefered mode. You set this by setting the variable $ADODB_FETCH_MODE |
---|
| 80 | to either of the constants ADODB_FETCH_NUM (for numeric indexes) or ADODB_FETCH_ASSOC |
---|
| 81 | (using field names as an associative index).</p> |
---|
| 82 | <p>The default behaviour of ADOdb varies depending on the database you are using. |
---|
| 83 | For consistency, set the fetch mode to either ADODB_FETCH_NUM (for speed) or |
---|
| 84 | ADODB_FETCH_ASSOC (for convenience) at the beginning of your code. </p> |
---|
| 85 | <p><b>Selects: Counting Records</b></p> |
---|
| 86 | <p>Another problem with SELECTs is that some databases do not return the number |
---|
| 87 | of rows retrieved from a select statement. This is because the highest performance |
---|
| 88 | databases will return records to you even before the last record has been found. |
---|
| 89 | </p> |
---|
| 90 | <p>In ADOdb, RecordCount( ) returns the number of rows returned, or will emulate |
---|
| 91 | it by buffering the rows and returning the count after all rows have been returned. |
---|
| 92 | This can be disabled for performance reasons when retrieving large recordsets |
---|
| 93 | by setting the global variable $ADODB_COUNTRECS = false. This variable is checked |
---|
| 94 | every time a query is executed, so you can selectively choose which recordsets |
---|
| 95 | to count.</p> |
---|
| 96 | <p>If you prefer to set $ADODB_COUNTRECS = false, ADOdb still has the PO_RecordCount( |
---|
| 97 | ) function. This will return the number of rows, or if it is not found, it will |
---|
| 98 | return an estimate using SELECT COUNT(*):</p> |
---|
| 99 | <pre>$rs = $db->Execute("select * from table where state=$state"); |
---|
| 100 | $numrows = $rs->PO_RecordCount('table', "state=$state");</pre> |
---|
| 101 | <p><b>Selects: Locking</b> </p> |
---|
| 102 | <p>SELECT statements are commonly used to implement row-level locking of tables. |
---|
| 103 | Other databases such as Oracle, Interbase, PostgreSQL and MySQL with InnoDB |
---|
| 104 | do not require row-level locking because they use versioning to display data |
---|
| 105 | consistent with a specific point in time.</p> |
---|
| 106 | <p>Currently, I recommend encapsulating the row-level locking in a separate function, |
---|
| 107 | such as RowLock($table, $where):</p> |
---|
| 108 | <pre>$connection->BeginTrans( ); |
---|
| 109 | $connection->RowLock($table, $where); </pre> |
---|
| 110 | <pre><font color=green># some operation</font></pre> |
---|
| 111 | <pre>if ($ok) $connection->CommitTrans( ); |
---|
| 112 | else $connection->RollbackTrans( ); |
---|
| 113 | </pre> |
---|
| 114 | <p><b>Selects: Outer Joins</b></p> |
---|
| 115 | <p>Not all databases support outer joins. Furthermore the syntax for outer joins |
---|
| 116 | differs dramatically between database vendors. One portable (and possibly slower) |
---|
| 117 | method of implementing outer joins is using UNION.</p> |
---|
| 118 | <p>For example, an ANSI-92 left outer join between two tables t1 and t2 could |
---|
| 119 | look like:</p> |
---|
| 120 | <pre>SELECT t1.col1, t1.col2, t2.cola <br> FROM t1 <i>LEFT JOIN</i> t2 ON t1.col = t2.col</pre> |
---|
| 121 | <p>This can be emulated using:</p> |
---|
| 122 | <pre>SELECT t1.col1, t1.col2, t2.cola FROM t1, t2 <br> WHERE t1.col = t2.col |
---|
| 123 | UNION ALL |
---|
| 124 | SELECT col1, col2, null FROM t1 <br> WHERE t1.col not in (select distinct col from t2) |
---|
| 125 | </pre> |
---|
| 126 | <p>Since ADOdb 2.13, we provide some hints in the connection object as to legal |
---|
| 127 | join variations. This is still incomplete and sometimes depends on the database |
---|
| 128 | version you are using, but is useful as a general guideline:</p> |
---|
| 129 | <p><font face="Courier New, Courier, mono">$conn->leftOuter</font>: holds the |
---|
| 130 | operator used for left outer joins (eg. '*='), or false if not known or not |
---|
| 131 | available.<br> |
---|
| 132 | <font face="Courier New, Courier, mono">$conn->rightOuter</font>: holds the |
---|
| 133 | operator used for right outer joins (eg '=*'), or false if not known or not |
---|
| 134 | available.<br> |
---|
| 135 | <font face="Courier New, Courier, mono">$conn->ansiOuter</font>: boolean |
---|
| 136 | that if true means that ANSI-92 style outer joins are supported, or false if |
---|
| 137 | not known.</p> |
---|
| 138 | <h3><b>Inserts</b> </h3> |
---|
| 139 | <p>When you create records, you need to generate unique id's for each record. |
---|
| 140 | There are two common techniques: (1) auto-incrementing columns and (2) sequences. |
---|
| 141 | </p> |
---|
| 142 | <p>Auto-incrementing columns are supported by MySQL, Sybase and Microsoft Access |
---|
| 143 | and SQL Server. However most other databases do not support this feature. So |
---|
| 144 | for portability, you have little choice but to use sequences. Sequences are |
---|
| 145 | special functions that return a unique incrementing number every time you call |
---|
| 146 | it, suitable to be used as database keys. In ADOdb, we use the GenID( ) function. |
---|
| 147 | It has takes a parameter, the sequence name. Different tables can have different |
---|
| 148 | sequences. </p> |
---|
| 149 | <pre>$id = $connection->GenID('sequence_name');<br>$connection->Execute("insert into table (id, firstname, lastname) <br> values ($id, $firstname, $lastname)");</pre> |
---|
| 150 | <p>For databases that do not support sequences natively, ADOdb emulates sequences |
---|
| 151 | by creating a table for every sequence.</p> |
---|
| 152 | <h3><b>Binding</b></h3> |
---|
| 153 | <p>Binding variables in an SQL statement is another tricky feature. Binding is |
---|
| 154 | useful because it allows pre-compilation of SQL. When inserting multiple records |
---|
| 155 | into a database in a loop, binding can offer a 50% (or greater) speedup. However |
---|
| 156 | many databases such as Access and MySQL do not support binding natively and |
---|
| 157 | there is some overhead in emulating binding. Furthermore, different databases |
---|
| 158 | (specificly Oracle!) implement binding differently. My recommendation is to |
---|
| 159 | use binding if your database queries are too slow, but make sure you are using |
---|
| 160 | a database that supports it like Oracle. </p> |
---|
| 161 | <p>ADOdb supports portable Prepare/Execute with:</p> |
---|
| 162 | <pre>$stmt = $db->Prepare('select * from customers where custid=? and state=?'); |
---|
| 163 | $rs = $db->Execute($stmt, array($id,'New York'));</pre> |
---|
| 164 | <p>Oracle uses named bind placeholders, not "?", so to support portable binding, we have Param() that generates |
---|
| 165 | the correct placeholder (available since ADOdb 3.92): |
---|
| 166 | <pre><font color="#000000">$sql = <font color="#993300">'insert into table (col1,col2) values ('</font>.$DB->Param('a').<font color="#993300">','</font>.$DB->Param('b').<font color="#993300">')'</font>; |
---|
| 167 | <font color="#006600"># generates 'insert into table (col1,col2) values (?,?)' |
---|
| 168 | # or 'insert into table (col1,col2) values (:a,:b)</font>' |
---|
| 169 | $stmt = $DB->Prepare($sql); |
---|
| 170 | $stmt = $DB->Execute($stmt,array('one','two')); |
---|
| 171 | </font></pre> |
---|
| 172 | <a name="native"></a> |
---|
| 173 | <h2>Portable Native SQL</h2> |
---|
| 174 | <p>ADOdb provides the following functions for portably generating SQL functions |
---|
| 175 | as strings to be merged into your SQL statements (some are only available since |
---|
| 176 | ADOdb 3.92): </p> |
---|
| 177 | <table width="75%" border="1" align=center> |
---|
| 178 | <tr> |
---|
| 179 | <td width=30%><b>Function</b></td> |
---|
| 180 | <td><b>Description</b></td> |
---|
| 181 | </tr> |
---|
| 182 | <tr> |
---|
| 183 | <td>DBDate($date)</td> |
---|
| 184 | <td>Pass in a UNIX timestamp or ISO date and it will convert it to a date |
---|
| 185 | string formatted for INSERT/UPDATE</td> |
---|
| 186 | </tr> |
---|
| 187 | <tr> |
---|
| 188 | <td>DBTimeStamp($date)</td> |
---|
| 189 | <td>Pass in a UNIX timestamp or ISO date and it will convert it to a timestamp |
---|
| 190 | string formatted for INSERT/UPDATE</td> |
---|
| 191 | </tr> |
---|
| 192 | <tr> |
---|
| 193 | <td>SQLDate($date, $fmt)</td> |
---|
| 194 | <td>Portably generate a date formatted using $fmt mask, for use in SELECT |
---|
| 195 | statements.</td> |
---|
| 196 | </tr> |
---|
| 197 | <tr> |
---|
| 198 | <td>OffsetDate($date, $ndays)</td> |
---|
| 199 | <td>Portably generate a $date offset by $ndays.</td> |
---|
| 200 | </tr> |
---|
| 201 | <tr> |
---|
| 202 | <td>Concat($s1, $s2, ...)</td> |
---|
| 203 | <td>Portably concatenate strings. Alternatively, for mssql use mssqlpo driver, |
---|
| 204 | which allows || operator.</td> |
---|
| 205 | </tr> |
---|
| 206 | <tr> |
---|
| 207 | <td>IfNull($fld, $replaceNull)</td> |
---|
| 208 | <td>Returns a string that is the equivalent of MySQL IFNULL or Oracle NVL.</td> |
---|
| 209 | </tr> |
---|
| 210 | <tr> |
---|
| 211 | <td>Param($name)</td> |
---|
| 212 | <td>Generates bind placeholders, using ? or named conventions as appropriate.</td> |
---|
| 213 | </tr> |
---|
| 214 | <tr><td>$db->sysDate</td><td>Property that holds the SQL function that returns today's date</td> |
---|
| 215 | </tr> |
---|
| 216 | <tr><td>$db->sysTimeStamp</td><td>Property that holds the SQL function that returns the current |
---|
| 217 | timestamp (date+time). |
---|
| 218 | </td> |
---|
| 219 | </tr> |
---|
| 220 | <tr> |
---|
| 221 | <td>$db->concat_operator</td><td>Property that holds the concatenation operator |
---|
| 222 | </td> |
---|
| 223 | </tr> |
---|
| 224 | <tr><td>$db->length</td><td>Property that holds the name of the SQL strlen function. |
---|
| 225 | </td></tr> |
---|
| 226 | |
---|
| 227 | <tr><td>$db->upperCase</td><td>Property that holds the name of the SQL strtoupper function. |
---|
| 228 | </td></tr> |
---|
| 229 | <tr><td>$db->random</td><td>Property that holds the SQL to generate a random number between 0.00 and 1.00. |
---|
| 230 | </td> |
---|
| 231 | </tr> |
---|
| 232 | <tr><td>$db->substr</td><td>Property that holds the name of the SQL substring function. |
---|
| 233 | </td></tr> |
---|
| 234 | </table> |
---|
| 235 | <p> </p> |
---|
| 236 | <h2>DDL and Tuning</h2> |
---|
| 237 | There are database design tools such as ERWin or Dezign that allow you to generate data definition language commands such as ALTER TABLE or CREATE INDEX from Entity-Relationship diagrams. |
---|
| 238 | <p> |
---|
| 239 | However if you prefer to use a PHP-based table creation scheme, adodb provides you with this feature. Here is the code to generate the SQL to create a table with: |
---|
| 240 | <ol> |
---|
| 241 | <li> Auto-increment primary key 'ID', </li> |
---|
| 242 | <li>The person's 'NAME' VARCHAR(32) NOT NULL and defaults to '', </li> |
---|
| 243 | <li>The date and time of record creation 'CREATED', </li> |
---|
| 244 | <li> The person's 'AGE', defaulting to 0, type NUMERIC(16). </li> |
---|
| 245 | </ol> |
---|
| 246 | <p> |
---|
| 247 | Also create a compound index consisting of 'NAME' and 'AGE': |
---|
| 248 | <pre> |
---|
| 249 | $datadict = <strong>NewDataDictionary</strong>($connection); |
---|
| 250 | $flds = " |
---|
| 251 | <font color="#660000"> ID I AUTOINCREMENT PRIMARY, |
---|
| 252 | NAME C(32) DEFAULT '' NOTNULL, |
---|
| 253 | CREATED T DEFTIMESTAMP, |
---|
| 254 | AGE N(16) DEFAULT 0</font> |
---|
| 255 | "; |
---|
| 256 | $sql1 = $datadict-><strong>CreateTableSQL</strong>('tabname', $flds); |
---|
| 257 | $sql2 = $datadict-><strong>CreateIndexSQL</strong>('idx_name_age', 'tabname', 'NAME,AGE'); |
---|
| 258 | </pre> |
---|
| 259 | |
---|
| 260 | <h3>Data Types</h3> |
---|
| 261 | <p>Stick to a few data types that are available in most databases. Char, varchar |
---|
| 262 | and numeric/number are supported by most databases. Most other data types (including |
---|
| 263 | integer, boolean and float) cannot be relied on being available. I recommend |
---|
| 264 | using char(1) or number(1) to hold booleans. </p> |
---|
| 265 | <p>Different databases have different ways of representing dates and timestamps/datetime. |
---|
| 266 | ADOdb attempts to display all dates in ISO (YYYY-MM-DD) format. ADOdb also provides |
---|
| 267 | DBDate( ) and DBTimeStamp( ) to convert dates to formats that are acceptable |
---|
| 268 | to that database. Both functions accept Unix integer timestamps and date strings |
---|
| 269 | in ISO format.</p> |
---|
| 270 | <pre>$date1 = $connection->DBDate(time( ));<br>$date2 = $connection->DBTimeStamp('2002-02-23 13:03:33');</pre> |
---|
| 271 | <p>We also provide functions to convert database dates to Unix timestamps:</p> |
---|
| 272 | <pre>$unixts = $recordset->UnixDate('#2002-02-30#'); <font color="green"># MS Access date => unix timestamp</font></pre> |
---|
| 273 | <p>For date calculations, we have OffsetDate which allows you to calculate dates such as <i>yesterday</i> and <i>next week</i> in a RDBMS independant fashion. For example, if we want to set a field to 6 hour from now, use: |
---|
| 274 | <pre> |
---|
| 275 | $sql = 'update table set dtimefld='.$db->OffsetDate($db->sysTimeStamp, 6/24).' where ...'; |
---|
| 276 | </pre> |
---|
| 277 | <p>The maximum length of a char/varchar field is also database specific. You can |
---|
| 278 | only assume that field lengths of up to 250 characters are supported. This is |
---|
| 279 | normally impractical for web based forum or content management systems. You |
---|
| 280 | will need to be familiar with how databases handle large objects (LOBs). ADOdb |
---|
| 281 | implements two functions, UpdateBlob( ) and UpdateClob( ) that allow you to |
---|
| 282 | update fields holding Binary Large Objects (eg. pictures) and Character Large |
---|
| 283 | Objects (eg. HTML articles):</p> |
---|
| 284 | <pre><font color=green># for oracle </font> |
---|
| 285 | $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1,empty_blob())'); |
---|
| 286 | $conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1'); |
---|
| 287 | |
---|
| 288 | <font color=green># non-oracle databases</font> |
---|
| 289 | $conn->Execute('INSERT INTO blobtable (id, blobcol) VALUES (1, null)'); |
---|
| 290 | $conn->UpdateBlob('blobtable','blobcol',$blobvalue,'id=1'); |
---|
| 291 | </pre> |
---|
| 292 | <p>Null handling is another area where differences can occur. This is a mine-field, |
---|
| 293 | because 3-value logic is tricky. |
---|
| 294 | <p>In general, I avoid using nulls except for dates and default all my numeric |
---|
| 295 | and character fields to 0 or the empty string. This maintains consistency with |
---|
| 296 | PHP, where empty strings and zero are treated as equivalent, and avoids SQL |
---|
| 297 | ambiguities when you use the ANY and EXISTS operators. However if your database |
---|
| 298 | has significant amounts of missing or unknown data, using nulls might be a good |
---|
| 299 | idea. |
---|
| 300 | <p> |
---|
| 301 | ADOdb also supports a portable <a href=http://phplens.com/adodb/reference.functions.concat.html#ifnull>IfNull</a> function, so you can define what to display |
---|
| 302 | if the field contains a null. |
---|
| 303 | <h3><b>Stored Procedures</b></h3> |
---|
| 304 | <p>Stored procedures are another problem area. Some databases allow recordsets |
---|
| 305 | to be returned in a stored procedure (Microsoft SQL Server and Sybase), and |
---|
| 306 | others only allow output parameters to be returned. Stored procedures sometimes |
---|
| 307 | need to be wrapped in special syntax. For example, Oracle requires such code |
---|
| 308 | to be wrapped in an anonymous block with BEGIN and END. Also internal sql operators |
---|
| 309 | and functions such as +, ||, TRIM( ), SUBSTR( ) or INSTR( ) vary between vendors. |
---|
| 310 | </p> |
---|
| 311 | <p>An example of how to call a stored procedure with 2 parameters and 1 return |
---|
| 312 | value follows:</p> |
---|
| 313 | <pre> switch ($db->databaseType) { |
---|
| 314 | case '<font color="#993300">mssql</font>': |
---|
| 315 | $sql = <font color="#000000"><font color="#993333">'<font color="#993300">SP_RUNSOMETHING</font>'</font></font>; break; |
---|
| 316 | case '<font color="#993300">oci8</font>': |
---|
| 317 | $sql = |
---|
| 318 | <font color="#993300"> </font><font color="#000000"><font color="#993300">"declare RETVAL integer;begin :RETVAL := </font><font color="#000000"><font color="#993333"><font color="#993300">SP_RUNSOMETHING</font></font></font><font color="#993300">(:myid,:group);end;"; |
---|
| 319 | </font> break;</font> |
---|
| 320 | default: |
---|
| 321 | die('<font color="#993300">Unsupported feature</font>'); |
---|
| 322 | } |
---|
| 323 | <font color="#000000"><font color="green"> # @RETVAL = SP_RUNSOMETHING @myid,@group</font> |
---|
| 324 | $stmt = $db->PrepareSP($sql); <br> $db->Parameter($stmt,$id,'<font color="#993300">myid</font>'); |
---|
| 325 | $db->Parameter($stmt,$group,'<font color="#993300">group</font>'); |
---|
| 326 | <font color="green"># true indicates output parameter<br> </font>$db->Parameter($stmt,$ret,'<font color="#993300">RETVAL</font>',true); |
---|
| 327 | $db->Execute($stmt); </font></pre> |
---|
| 328 | <p>As you can see, the ADOdb API is the same for both databases. But the stored |
---|
| 329 | procedure SQL syntax is quite different between databases and is not portable, |
---|
| 330 | so be forewarned! However sometimes you have little choice as some systems only |
---|
| 331 | allow data to be accessed via stored procedures. This is when the ultimate portability |
---|
| 332 | solution might be the only solution: <i>treating portable SQL as a localization |
---|
| 333 | exercise...</i></p> |
---|
| 334 | <h3><b>SQL as a Localization Exercise</b></h3> |
---|
| 335 | <p> In general to provide real portability, you will have to treat SQL coding |
---|
| 336 | as a localization exercise. In PHP, it has become common to define separate |
---|
| 337 | language files for English, Russian, Korean, etc. Similarly, I would suggest |
---|
| 338 | you have separate Sybase, Intebase, MySQL, etc files, and conditionally include |
---|
| 339 | the SQL based on the database. For example, each MySQL SQL statement would be |
---|
| 340 | stored in a separate variable, in a file called 'mysql-lang.inc.php'.</p> |
---|
| 341 | <pre>$sqlGetPassword = '<font color="#993300">select password from users where userid=%s</font>'; |
---|
| 342 | $sqlSearchKeyword = "<font color="#993300">SELECT * FROM articles WHERE match (title,body) against (%s</font>)";</pre> |
---|
| 343 | <p>In our main PHP file:</p> |
---|
| 344 | <pre><font color=green># define which database to load...</font> |
---|
| 345 | <b>$database = '<font color="#993300">mysql</font>'; |
---|
| 346 | include_once("<font color="#993300">$database-lang.inc.php</font>");</b> |
---|
| 347 | |
---|
| 348 | $db = &NewADOConnection($database); |
---|
| 349 | $db->PConnect(...) or die('<font color="#993300">Failed to connect to database</font>'); |
---|
| 350 | |
---|
| 351 | <font color=green># search for a keyword $word</font> |
---|
| 352 | $rs = $db->Execute(sprintf($sqlSearchKeyWord,$db->qstr($word)));</pre> |
---|
| 353 | <p>Note that we quote the $word variable using the qstr( ) function. This is because |
---|
| 354 | each database quotes strings using different conventions.</p> |
---|
| 355 | <p> |
---|
| 356 | <h3>Final Thoughts</h3> |
---|
| 357 | <p>The best way to ensure that you have portable SQL is to have your data tables designed using |
---|
| 358 | sound principles. Learn the theory of normalization and entity-relationship diagrams and model |
---|
| 359 | your data carefully. Understand how joins and indexes work and how they are used to tune performance. |
---|
| 360 | <p> Visit the following page for more references on database theory and vendors: |
---|
| 361 | <a href="http://php.weblogs.com/sql_tutorial">http://php.weblogs.com/sql_tutorial</a>. |
---|
| 362 | Also read this article on <a href=http://phplens.com/lens/php-book/optimizing-debugging-php.php>Optimizing PHP</a>. |
---|
| 363 | <p> |
---|
| 364 | <font size=1>(c) 2002-2003 John Lim.</font> |
---|
| 365 | |
---|
| 366 | </body> |
---|
| 367 | </html> |
---|