Hi I know this may be ambitious for a relative newbie but I have basic experience of html and relational database design (i have also just started a basic php course). I have searched the web and gleaned some information, but it has not got me very far. I would therefore be grateful for any pointers/code I could use to work off:
I have the following tables in the same mysql database:
meetcat consists of:
meetid <-- auto incrementing primary key -->
dropdescr <-- description to appear in a dropdown box on the form-->
mtype <--The type of meeting. Another description that will be output to a webpage-->
mtime <--the set time of day that meeting takes place-->
mweekday <-- the set day of the week that the meeting takes place-->
organiser consists of:
orgid <-- auto incrementing primary key -->
title <-- title of the organiser e.g. mr,mrs etc-->
firstname <--self-explanatory-->
lastname <--self-explanatory-->
A table called event creates a "relationship" by using the ids from meetcat and organiser along with a variable date to provide details of an event. This is because there are many organisers and many types of meeting.
event therefore consists of:
eventid<-- this is also an auto increment primary key. I could not use meetid and orgid as a composite as the same combination is possible on different events, and I did not want to complicate things by trying to create a composite by using date as well-->
date<--will be a dropdown option in a form box-->
meetid<--foreign key-->
orgid<--foreign key-->
What I would like to do
I would like an admin page where the user can only enter data from dropdown lists (i.e. make it as error free as is possible) using the <select> tags, but all on the same line (probably using <tr> within a table). The following <td> tags then need to be set-up:
dayofweek<-- dropdown with all seven days of week. Could be from a basic static option list-->
date<--dropdown list that automatically updates on dayofweek selection providing the next 6 weeks of that day of the week e.g. if Monday then options from today would be 15th Oct 07, 22nd Oct 07 etc-->
organiser<--dropdown of all the organisers within the organiser table-->
meetingcat<--dropdown of all meetings from the meetid table where mweekday matches dayofweek. If there are no matching records then the user needs to be instructed to change the dayofweek because of this problem-->
At the end of the row there needs to be an Add another Meeting button,
which creates another row with the same options beneath the first one (including the add another meeting button).
Prefererably each row would also have a delete option.
Finally there would be an update database button which would then append all the records to the database.
I guess I will need to use an html <table><tr>and <td> within a form context possibly using the GET method, with loops, arrays and functions thrown in.
I have started it and managed the organiser dropdown list but my code is definately very inefficient and as a newbie to php/mysql i dont want to learn bad habits, therefore I would rather start again with any pointers or code from experts on this forum. If possible I would not want to use javascript as some users may not have javascript enabled. Thankyou very much for your patience if you have read this to the end.
Add rows to MySql "relational" table via form
Moderator: General Moderators
- ReverendDexter
- Forum Contributor
- Posts: 193
- Joined: Tue May 29, 2007 1:26 pm
- Location: Chico, CA
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
I should state that as a msaccess novice aswell I am using the word foreign keys loosely as I was assuming I might use an array/loop or combination with the meetid and orgid to iterate through their respective tables. The number of records in these tables is small with meetcat probably having a max of 20, and organiser also about 20 max. The event details will continue to grow week on week but that may be max 500 per year.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Sounds like a pretty good design start to me. You can use foreign keys with ISAM tables, as well, but you will have to manage all the joins in your application. JOINs are the core of the database issues. I would suggest that you consider the need to delete and edit these tables, as well as add new records.
Thanks for the help so far.
I've found the Joining Three Tables of the following link extremely useful as a starting point:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
It's enabled me to extract the data from my mysql database and output related fields from all three tables row by row within an html table. i.e. I've been able to test that my mysql query using all three tables works!!!
I should now be able to use this technique to place info into drop-down lists on the update form.
The next hurdle is to be able to immediately limit date options within the date drop-down list based on the day of the week selected from another drop down field.
Please feel free to suggest any ideas on this one.
I'll keep you posted if I manage to work this out during the course of this week.
Cheers.
I've found the Joining Three Tables of the following link extremely useful as a starting point:
http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.php
It's enabled me to extract the data from my mysql database and output related fields from all three tables row by row within an html table. i.e. I've been able to test that my mysql query using all three tables works!!!
I should now be able to use this technique to place info into drop-down lists on the update form.
The next hurdle is to be able to immediately limit date options within the date drop-down list based on the day of the week selected from another drop down field.
Please feel free to suggest any ideas on this one.
I'll keep you posted if I manage to work this out during the course of this week.
Cheers.