Add Multiple records from other Tables

[jedel]

Hi All,

I've been asked by a client to do up a simple event registration site where:

a. Organizers can enter an event(s) into a database for display on the web
b. People can register to become members of the organization
c. These members can register to attend the event online, and
d. Administrators of the organizations website are able to add multiple registrations to an event list.


Having said this, my thoughts are that I will need three tables in a database
1. The events table
2. The members table
3. The event register table

What I'm thinking is that I will need to add the event ID of the event records and the relevant fields from the members table to the event registration table. This will allow me to display a list of registered members to an event by sorting the register table by the event ID. I really can't think of another way of doing this, Any ideas with this setup would be appreciated. Now, I've looked at a number of tutorials and forums on this page and I'm getting a better understanding of Arrays and loops, nut I'm still a little uncertain of how to get the code from the first two tables into the third; particularly the multiple records by the administrators.

If anyone could provide some guidelines to maybe get me started, I can then begin to get more specific questions as this post develops. In the meantime I'll try a few things myself.

BTW - I use Dream weaver to develop my pages, so... Sorry to all of the "puritans" out there, I'm just no yet bright enough to type code from my head


-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jpadie]

the third table could just be a join table between the primary keys of the events and members table. you would then retrieve those members attending an event by a simple join query.

Don't use dreamweaver. genuinely. it will not be helpful in the long run.

 

[jedel]

OK,

I've never really played with join tables. Am I right in saying that this is similar writing a query in Access?

and for my level of expertise, what Editor would you recommend for writing PHP?

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jpadie]

ah. didn't realise you were en bapteme!

for an editor i would strongly recommend aptana. i use it on all three platforms that i work on.

join queries? we're talking oranges and fruit here. an access query is the same (more or less) as any other query. a Join query is a query that produces a resultset based on two or more tables. this can be done in access or just about any other relational database.

 

[jedel]

How about if I use Dream weaver, but Promise not to use the point and click features.....please?

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jpadie]

i shall spank you.

 

[jedel]

OH...OK then. I'm downloading Aptana now. But you're going to have to guide through this, new software and all...

I'll let you know when its up and running

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jedel]

OK,

I have Aptana up and running Although you might as well put me in a spaceship and tell to fly to the moon! I'd have just as much success with this. I'm sure I'll get the hang of it though.

I have now created two database tables into a database called "youthdb"

TABLE 1 = 'events' The fields are:
EventID , int(11),NotNull,Auto-Increment, Primary Key
Month, text, Not Null,
Date, date, NotNull
Title, text, NotNull
Description, LongText, Null
Author, text, Null (This will be a hidden field and be automatically filled in)
dteEdit, Timestamp, CurrentTimestamp (just to keep a record of last update)

TABLE 2 = 'MbrTBL' Fields Are:
MbrID, int(11),NotNull,Auto-Increment, Primary Key
Surname, text, Not Null,
FirstName, text, Not Null,
DOB, text, Null,
Myspace, text, Null,
Bebo, text, Null,
Facebook, text, Null,
School, text, Null,
Grade, text, Null,
Street, text, Not Null,
Suburb, text, Not Null,
Postcode, text, Not Null,
State , text, Null, (Will be completed automatically)
Ph , text, Not Null,
EmContNAme, text, Not Null,
EmContactPh, text, Not Null,
Medical, text, Not Null,
dteEdit, Timestamp, Null, (Will be completed automatically)
EditBy, text, Null, (Will be completed automatically)

I'll go in now and see if I can connect to this using Aptana, but some help here would be good to get this project off the ground and to really start to get into the coding

Cheers


-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jpadie]

aptana won't help you 'correct' things so to speak. It is an excellent code editor however, and (being free) much cheaper than dreamweaver which is neither an excellent code editor nor an excellent IDE. It's code snips are quite often examples of how best NOT to do something, rather than the inverse.

for the moment, keep things simple and create a third table with this data definition

CREATE TABLE event-member
( 
MbrID int(11) not null,
eventID int(11) not null,
PRIMARY KEY (MbrID, eventID)
)

from a logic standpoint you would then obtain all members going to an event like so (for an event with the ID of 5)

select MbrID from event-member where eventID = 5

long hand you could then iterate through this recordset and retrieve the member's name as so

$result = mysql_query("select MbrID from event-member where eventID = 5") or die (mysql_error());
$members = array();
while ($row = mysql_fetch_assoc($result)){
  $result2 = mysql_query ("Select * from MbrTBL where MbrID = {$row['MbrID']}") or die (mysql_error());
  $members[] = mysql_fetch_assoc($result2);
}
echo "These members are attending event 5 <br/> <pre>";
print_r($members);

but this is quite long winded and you have to run x + 1 queries where x is the number of members. you can avoid this with joins

$query = "Select j.MbrID, m.* from event-member j join (MbrTBL m) USING (MbrID)";
$result = mysql_query($query) or die mysql_error());
while ($row = mysql_fetch_assoc($result)){
 print_r($row);
}

hope that helps! check out the mysql manual for examples of joins. For core sql problems it's probably best if you post back in the mysql forum, though.

 

[jedel]

Whoa up there big Fella! I got the gist of the SQL code above, but where not there yet!

I'm still trying to find the ignition key for Aptana.

I made the third table no problems.

But how do I CONNECT (not correct) to the MySQL database through Aptana? First things first here.

I need to be able to see the page develop by viewing it but I can't do that if I can't connect to the database.

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[jpadie]

you do not connect with aptana! it's just a coding environment. You connect to a database in php using mysql_connect and mysql_select_db as normal!

it's a brave new world outside of dreamweaver!