 |
Submit Your Code | |
 |
SEO Monitor | |
 |
UpTime Monitor | |
 |
PHP Code Contest | |
 |
My Favorite Examples | |
|
My Favorite Articles | |
 |
Update Your Profile | |
|
|
| More Weber Sites | ||
| PHP Code Search | ||
| Web Development Forums | ||
| Learn MySQL Playing Trivia | ||
| PHPBB2 Templates | ||
| Web Development Index | ||
| PHP Web Logs (BLogs) | ||
| Web Development Resources | ||
| Web Development Content | ||
| Recommended Links | ||
| PHPClasses | ||
| PHP Editor | ||
| PHP Jobs | ||
| Vision.To Design | ||
| Ajax Tutorials | ||
| PHP Programming Help | ||
| PHP/MySQL Programming | ||
| Webmaster Resources | ||
| Webmaster Forum | ||
| XML meta language | ||
| website builder | ||
| Recommended | ||
| Submit Site | ||
| Forex Trading Online forex trading platform | ||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Code | |||||
<H2>Creating Auto-incrementing ID Fields with PHP/Oracle</H2> <HR> You will often need to create auto-incrementing fields in your Oracle tables, most usually in order to generate a unique number for a table's primary key. Although there is no inbuilt support for this in Oracle, it is a relatively simple process to make this behaviour happen. You need to use Oracle sequences to generate the numbers, and triggers to automagically give the ID field a value whenever you insert a row into the database. <P> We'll use the tried and tested emp table as an example, created with: <PRE> create table emp ( id number(5), name varchar2(20), surname varchar2(30) ); </PRE> It's a bit simple, but we're only interested in one field in this case, the id field. <P> The next step is to create a sequence: <PRE> create sequence seq_emp_id start with 1 increment by 1; / </PRE> Next, the trigger: <PRE> create or replace trigger trig_emp before insert on emp for each row begin select seq_emp_id.nextval into :new.id from dual end; / </PRE> Once created, this trigger will be activated whenever a row is inserted into the table. The select statement may look daunting to everyone but Oracle gurus, but you should be able to understand the gist of what is going on. The next value is pulled out of the sequence and this value is entered into the id field. <P> Now, when you insert a couple of rows from PHP: <PRE> $sql1 = "insert into emp (name, surname) values ('Ali', 'G' )"; $sql2 = "insert into emp (name, surname) values ('Keith', 'Chegwin' )"; $stmt = OCIParse( $conn, $sql1 ); OCIExecute( $stmt ) $stmt = OCIParse( $conn, $sql2 ); OCIExecute( $stmt ) </PRE> and then issue a select statement, you will see that the id fields have been assigned a unique number without you having to explicitly ask it to in your PHP code: <PRE> SQL> select * from emp order by id; ID NAME SURNAME ---------- -------------------- ------------------------------ 1 Ali G 2 Keith Chegwin </PRE> | |||||
| Related Code Examples | |||||
