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]
