v5:userguide:portable_sql
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
reference:portable_sql [2015/12/04 18:36] – mnewnham | v5:userguide:portable_sql [2016/03/13 23:52] – [Inserting Records] mnewnham | ||
---|---|---|---|
Line 26: | Line 26: | ||
^Oracle|select * from (select * from table) where rownum <= 10| | ^Oracle|select * from (select * from table) where rownum <= 10| | ||
- | This feature of getting a subset of data is so useful that in the PHP class library ADOdb, we have a [[reference: | + | This feature of getting a subset of data is so useful that in the PHP class library ADOdb, we have a [[v5:reference:connection: |
<code php> | <code php> | ||
Line 34: | Line 34: | ||
==== Fetch Modes Within Select Statements ==== | ==== Fetch Modes Within Select Statements ==== | ||
- | PHP allows you to retrieve database records as arrays. You can choose to have the arrays indexed by field name or number. However different low-level PHP database drivers are inconsistent in their indexing efforts. ADOdb allows you to determine your prefered mode. You set this by setting the variable [[reference: | + | PHP allows you to retrieve database records as arrays. You can choose to have the arrays indexed by field name or number. However different low-level PHP database drivers are inconsistent in their indexing efforts. ADOdb allows you to determine your prefered mode. You set this by setting the variable [[v5:reference:adodb_fetch_mode|$ADODB_FETCH_MODE]] to either of the constants: |
* ADODB_FETCH_NUM (for numeric indexes) | * ADODB_FETCH_NUM (for numeric indexes) | ||
* ADODB_FETCH_ASSOC (using field names as an associative index). | * ADODB_FETCH_ASSOC (using field names as an associative index). | ||
Line 44: | Line 44: | ||
Another problem with SELECTs is that some databases do not return the number of rows retrieved from a select statement. This is because the highest performance databases will return records to you even before the last record has been found. | Another problem with SELECTs is that some databases do not return the number of rows retrieved from a select statement. This is because the highest performance databases will return records to you even before the last record has been found. | ||
- | In ADOdb, [[reference: | + | In ADOdb, [[v5:reference:connection: |
- | If you prefer to set '' | + | If you prefer to set '' |
<code php> | <code php> | ||
Line 98: | Line 98: | ||
===== Inserting Records ===== | ===== Inserting Records ===== | ||
- | When you create records, you need to generate unique id's for each record. There are two common techniques: | + | When you create records, you need to generate unique id's for each record. There are two common techniques: |
+ | * Auto-increment | ||
+ | * Sequences | ||
- | Auto-incrementing | + | Auto-increment |
+ | |||
+ | Different tables can have different sequences. | ||
<code php> | <code php> | ||
$id = $connection-> | $id = $connection-> | ||
$connection-> | $connection-> | ||
- | values ($id, $firstname, $lastname)" | + | values ($id, $firstname, $lastname)" |
</ | </ | ||
- | For databases that do not support | + | For databases that do not provide native |
===== Binding ===== | ===== Binding ===== | ||
Line 153: | Line 157: | ||
===== DDL and Tuning ===== | ===== DDL and Tuning ===== | ||
- | 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. | + | There are database design tools such as ERWin or Dezign that allow you to generate data definition language commands such as '' |
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: | 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: | ||
Line 191: | Line 195: | ||
</ | </ | ||
- | For date calculations, | + | For date calculations, |
<code php> | <code php> | ||
Line 197: | Line 201: | ||
</ | </ | ||
- | The maximum length of a char/ | + | The maximum length of a char/ |
<code php> | <code php> | ||
Line 213: | Line 217: | ||
In general, I avoid using nulls except for dates and default all my numeric and character fields to 0 or the empty string. This maintains consistency with PHP, where empty strings and zero are treated as equivalent, and avoids SQL ambiguities when you use the ANY and EXISTS operators. However if your database has significant amounts of missing or unknown data, using nulls might be a good idea. | In general, I avoid using nulls except for dates and default all my numeric and character fields to 0 or the empty string. This maintains consistency with PHP, where empty strings and zero are treated as equivalent, and avoids SQL ambiguities when you use the ANY and EXISTS operators. However if your database has significant amounts of missing or unknown data, using nulls might be a good idea. | ||
- | ADOdb also supports a portable [[reference: | + | ADOdb also supports a portable [[v5:reference:connection: |
===== Stored Procedures ===== | ===== Stored Procedures ===== | ||
Line 267: | Line 271: | ||
</ | </ | ||
- | Note that we quote the $word variable using the [[reference: | + | Note that we quote the $word variable using the [[v5:reference:connection:qstr|qStr()]] function. This is because each database quotes strings using different conventions. |
===== Final Thoughts ===== | ===== Final Thoughts ===== | ||
The best way to ensure that you have portable SQL is to have your data tables designed using sound principles. Learn the theory of normalization and entity-relationship diagrams and model your data carefully. Understand how joins and indexes work and how they are used to tune performance. | The best way to ensure that you have portable SQL is to have your data tables designed using sound principles. Learn the theory of normalization and entity-relationship diagrams and model your data carefully. Understand how joins and indexes work and how they are used to tune performance. | ||
- | |||
- | Visit the following page for more references on database theory and vendors: [[http:// | ||
(c) 2002-2014 John Lim. | (c) 2002-2014 John Lim. |
v5/userguide/portable_sql.txt · Last modified: 2020/12/30 21:32 by peterdd