PHP Switch statement???

[spoco]

Hi guys, I have hit a wall with a project. I think what I need to be utilizing is a Switch statement, but there may be an easier way. Basically I have a registration system where I want different tables called depending on the instructor. Here is the code that has been working with one instructor.

 

$query_messages = sprintf("SELECT * FROM messages WHERE id=%s", "1");

$messages = mysql_query($query_messages, $databaseconn) or die(mysql_error());

$row_messages = mysql_fetch_assoc($messages);

 

Now that the system is adding two other instructors, I need to be able to pull from tables messages2 and messages3, etc. So in theory, if:

 

instructor=A, then the code above would run, if instructor=B, then messages would be changed to messages2, if instructor=C, then messages3 and so on. Really need help on this one.

[Psycho]

No offense, but you are doing it wrong. You should have ONE table for all messages regardless of instructors. Then create your query based upon which instructor you want data for. You would jsut create an additional column to identify the instructor and it would probably make sense for that column to be a foreign key to an "instructor" table where you store the details about each instructor.

[AbraCadaver]

Ideally you would always use messages and each message would have an instructor id, so that you could do something like:

 

SELECT * FROM messages WHERE id=1 AND instructor_id=2

[spoco]

The problem is that the way it is set up is screwy. When a class is created, the instructor enters their name and e-mail address (They want this because there may be several more instructors that may just teach one class here and there).

[spoco]

There is also another way that we could accomplish this that may be easier.

 

When a class is added, the instructor enters their name and e-mail address. Is there a way for those two fields to replace existing fields in a table?

 

For example:

 

The messages table has 6 messages

id's 1-6

each id has a "from email" and "from name" field that is called when the message is sent. Is there a way to make it work where when an instructor enters name and e-mail it populates all of these fields before messages are sent, and when another instructor enters name and e-mail it overwrites?

 

I will send files if someone wants to pick up a few bucks freelancing.

[Psycho]

The problem is that the way it is set up is screwy. When a class is created, the instructor enters their name and e-mail address (They want this because there may be several more instructors that may just teach one class here and there).

 

Okay, that doesn't explain why you would want separate tables. In fact, the explanation you made above only reaffirms the fact that you shouldn't be using separate tables for the messages. As I stated you would want one table to store information about the instructors and one table to store the messages. You use a foreign key in the messages table to identify which instructor the messages belong to.

 

Example table structures

 

Table: instructors

Table: instructors
instructorID | Name          | email
1               Bob Smith      [email protected]
2               Jane Doe       [email protected]
3               Rick Donner    [email protected]
4               Bart Johnson   [email protected]
5               Davey Jones    [email protected]

 

Table: messages

ID | instructorID | Message
1     2             Message by Jane Doe
2     3             Message by Rick Donner
3     2             Message by Jane Doe
4     1             Message by Bob Smith
5     5             Message by Davey Jones
6     3             Message by Rick Donner
7     4             Message by Bart Johnson

[Psycho]

The messages table has 6 messages

id's 1-6

each id has a "from email" and "from name" field that is called when the message is sent. Is there a way to make it work where when an instructor enters name and e-mail it populates all of these fields before messages are sent, and when another instructor enters name and e-mail it overwrites?

 

If a message has been "sent" it would not make sense to update the email addresses used when sending the email after the fact. But, you could reference the current email address for the associated instructor. You don't need to update anything in the messages table.

 

using the table structure I proposed above you could get the message with ID 1 and the current name/email of the instructor as follow

SELECT message, name, email
FROM messages
JOIN instructors USING (instructorID)
WHERE id = 1

[spoco]

Are you available for freelance work?