hello list,
i'm hoping someone can enlighten me on the best way to do an insert. i am
creating a little user database that looks something like this:
CREATE TABLE ftpclients (
clientid serial primary key,
foldernumber int,
loginname varchar(32),
createdate date,
activedate date,
expiredate date,
quota int,
active boolean,
billbydate boolean,
passwd varchar(32)
);
and i'd like to log any changes to the ftpclients table (above) in a second
table like this:
CREATE TABLE clhistory (
clientid int,
entry text,
moddate date,
FOREIGN KEY (clientid) REFERENCES ftpclients
);
i'm writing a script in perl that will fetch the appropriate data and perform
these inserts, but i'm having trouble inserting into the clhistory table
because i need to insert two fields from the ftpclients table (clientid, and
createdate), but also a third value that comes from the script that runs the
query (so i can't use:
INSERT into ... SELECT...;
directly). right now, i see my options as (some perl'isms):
1 - $result=SELECT clientid from ftpclients WHERE
loginname=$client{loginname};
INSERT INTO clhistory VALUES ($result, $entry, $createdate);
2 - store the most recent comment in the ftpclients table, which would allow
an
INSERT ... SELECT;
ideally, i'd like to insert into the clhistory table a comment with a
clientid value that matches the desired loginname from the ftpclients table.
am i on the right track, or is there an entirely better way to handle this?
thanks,
twkonefal
______________________________________________________________________
Web-hosting solutions for home and business! http://website.yahoo.ca