Thanks for the detailed answer. I have dealt with JOIN clauses briefly in
the past, but not in this context. I will have to revisit on my own. As
far as grabbing the unique id, I am definitely going to use the DBI module
for contacting MySQL. When I am looking at your piece of code, I had a few
questions though. Let's say that I use this code to connect and insert data
into table called pageInfo in a database called "Project."
pageInfo Table:
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
pageName VARCHAR(200)
pageTitle TEXT
pageContent TEXT
my $dbh = WebDB::connecteclub ();
$sth = $dbh->prepare("insert into pageInfo(id, pageName, pageTitle,
pageContent) values(?,?,?,?)") or ¨
$sth->execute(undef, $cgi->param('pageName'), $cgi->param('pageTitle'),
$cgi->param('pageContent')) or ¨
How would the suggested code below then grab that generated id from the
record created by the process above? Also, let's say that there are two
people who submit the form at the same moment. Will this code be able to
ensure that the correct id is selected to be stored into the cookie?
my $newid = $sth->{'Database'}->{'mysql_insertid'};
unless ($newid) {
# handle error here
}
Thanks,
Andrew
-----Original Message-----
From: Wiggins d'Anconia [mailto:[EMAIL PROTECTED]
Sent: Sunday, August 10, 2003 7:46 PM
To: Hughes, Andrew
Cc: '[EMAIL PROTECTED]'
Subject: Re: perl / MySql application overview question
Hughes, Andrew wrote:
> I have a multi-table MySQL database that holds different pieces of
> information that will all be displayed on a web page template. The
general
> principle is that non web people will be able to populate web pages
through
> an admin panel. The data in all of the MqSQL tables will be entered by
the
> user through forms on the web and bought into a page template using the
> unique id for each page.
>
> The user will login with a username and password. This information will
> then be stored in a session using Apachee::Session and a cookie. The
first
> form that the user submits will contain general information about the page
> itself. All of this information will be stored in one row in the table
and
> a unique id will be generated in MySQL for that webpage. Subsequent forms
> will request other information for different areas of that one page. I
plan
> on relating all of the information in the rest of the tables to build that
> one page back to the specific page based on that page's unique id that was
> generated. I was planning on doing that by somehow getting that generated
> unique id into the cookie immediately after it is generated in the MySQL
> database. Then, every time information is added to another table that
> relates back to the webpage, I will enter that id from the cookie in the
> MySQL insert statement.
>
> Here are my questions:
> Is this a proper approach?
> If not, what is a better / correct approach?
> If it is, how would I go about it?
>
> I am not asking for someone to do it for me. I was just wondering if
there
> were some terms / buzzwords / tutorials that I should follow up on.
>
This sounds like a common approach and certainly a viable one. In
general you are talking about precisely what an RDBMS is for and the
tables you refer to are in general called lookup tables. In other words
you store the main record in a master table then you lookup into another
table based on a unique id from the master.
One item you will want to look into is a join clause. A join clause
allows you to combine fields from multiple tables in a single select
statement so that you don't have to do multiple independent selects to
retrieve the same effect, but that is OT for this forum, but have a look
at the MySQL documentation for JOIN.
As for retrieving the ID generated by an auto increment in MySQL if you
are using DBI (which I hope you are) then you can using something like
the following after an insert:
my $newid = $sth->{'Database'}->{'mysql_insertid'};
unless ($newid) {
# handle error here
}
Though this may not be portable to other engines, but it saves you the
pain of having to do multipe statements in an atomic manner (locking), etc.
You will run into other design problems as you go along, and a lot of
questions, that really can't be avoided without experience, but overall
your design is fairly sound for some applications and obviously you have
given it some or a lot of thought before starting, which is usually the
critical and first mistake made (aka to dive right in).
Good luck,
http://danconia.org
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
