Re: [Phpwebsite-developers] PEAR DB and auto_increment

[Matthew M. <ma...@tu...>]

Funny you should mention this Don :)

I was driving home from getting lunch and I was thinking about your letter
from yesterday. I thought, "Well we already have a parameter that will
return the max value of a column if you request it. Why not just write
that amount to the column?"

So I was ready to work on it and I read your followup. Well, I looked
sequences up on the PEAR site but I couldn't find HOW to define a sequence
before you call nextId.

So after trying unsuccessfully for make that work, I decided to use my
plan, which I believe is how they are doing it anyway :)

Anyway, here is how it works:

function sqlInsert ($db_array, $table_name, $check_dup=FALSE,
$maxColumn=NULL, $autoIncrement=FALSE, $show_sql=FALSE)

I have tested this on creating a user and it worked:
$user_id = $GLOBALS["core"]->sqlInsert($sql_array, "mod_users", 1,
"user_id", 1);

The $sql_array has the variable to put in the database. mod_users is the
table name. The next parameter (1) instructs the function not to insert a
repeat. The "user_id" is the name of the column I want to return the max
amount of. Finally the last parameter is a boolean saying to write the max
amount to the $maxColumn ("user_id").

So, once everyone reviews this procedure and if it gets voted up, then we
can start converting our sqlInserts.

Consider this however. MySQL auto_increment remembers the last number added.

For example, if we add 1, 2, and 3 and then 3 is deleted, auto_increment
will add 4. Our new functionality will add 3 instead (it doesn't keep
track of that stuff). Possible problem? Only if a module developer did not
remove all linked material. In other words, a module could have 3 as a
linked id because it did not know it was deleted. So it would pull 3's
data. Not a huge problem, but one we would have to be wary of while
programming.

Thanks for lighting the fire under me Don :)

Sincerely,
Matt

> It looks like the PEARy way of doing things is to use PEAR Sequences:
> http://pear.php.net/manual/en/core.db.tut_sequences.php
>
> I got this info from the PEAR mailing list when I asked about them:
>
> "When you first use a sequence PEAR::DB creates it in the DB, so you
> don't *have* to create it beforehand, you can though, in which case it
> has to be of the form 'sequencename_seq' without the quotes and if you
> left the default values. You can change the format of the string used to
> create the sequence to match your own preferences.
>
> If you create it you must not set it to autoincrement but better to INT
> unsigned."
>
> Has PEAR sequences been looked at before?
>
> Just curious,
> Don.
>
> On Fri, 18 Oct 2002, Don Seiler wrote:
>
>> In the install.sql for core and some modules (gallery I've seen), you
>> have  create table statements that use "auto_increment" to define a
>> field as  sequential.
>>
>> Isn't auto_increment a mysql function and not standard across
>> databases?   I don't see it in postgres and I'm pretty sure it won't
>> fly in Oracle.
>>
>> I want to use PEAR for database abstraction but using mysql-specific
>> syntax kind of defeats the purpose.
>>
>> Am I mistaken?
>>
>> Don.
>>
>>
>>
>> -------------------------------------------------------
>> This sf.net email is sponsored by:ThinkGeek
>> Welcome to geek heaven.
>> http://thinkgeek.com/sf
>> _______________________________________________
>> Phpwebsite-developers mailing list
>> Php...@li...
>> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers
>>
>
>
>
>
> -------------------------------------------------------
> This sf.net email is sponsored by:ThinkGeek
> Welcome to geek heaven.
> http://thinkgeek.com/sf
> _______________________________________________
> Phpwebsite-developers mailing list
> Php...@li...
> https://lists.sourceforge.net/lists/listinfo/phpwebsite-developers


Matthew McNaney
Internet Systems Architect
Electronic Student Services
Email: ma...@tu...
URL: http://phpwebsite.appstate.edu
Phone: 828-262-6493
ICQ: 141057403