homepage Welcome to WebmasterWorld Guest from 54.161.247.22
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Please kickstart me - MySQL How to have a variable set of input fields
kajje




msg:4101430
 6:26 am on Mar 20, 2010 (gmt 0)

Using PHP/MYSQL...

I'm working on an application for managing our huge database of contact companies and people.

For the first stage I was developing a simplistic Company+1-staff record, it helped us to combine all address books, spreadsheets, etc into one big database.

For the 2nd stage I would like to split up the Companies and Staff, having a Company record with Company details, and with several Staff records attached to it.

So far so good...

Now the Staff record has some fixed fields, but also 1-2-many other fields.

MR
John Doe
General Manager
[+] add

when pressed 'add' there is an extra line created
+---------------+ +-----------------+
| input/text | |dropdown V Type |
+---------------+ +-----------------+


Type can be 'Fax','Tel','Skype','Mobile','Email', etc



I can manage all this except the way how I should handle the unknown amount of fields in the table.

Eventhough my time is limited (isn't that for us all...) I don't like turnkey solutions, I want to find things out most by myself. I'm completely blocked with this one though... Can somebody drive me in the right direction?

Thanks
Kajje

 

rocknbil




msg:4101624
 7:01 pm on Mar 20, 2010 (gmt 0)

I wouldn't look to a variable number of fields in the main table. What you probably want here is a relational table, joined on some unique id. Using your example:

Companies and Staff, having a Company record with Company details, and with several Staff records attached to it.


So

table staff
id|company_id|user_id|first|last|position......

Type can be 'Fax','Tel','Skype','Mobile','Email', etc


<select name="type" id="type">
<option value="">Select</option>
<option value="1">Tel</option>
<option value="2">Fax</option>
<option value="3">Skype</option>
<option value="4">Mobile</option>
<option value="5">Email</option>
</select>

Store the types in their own table, editable by admin, so you can use an integer value for the type - this becomes very important in terms of performance when you go to do joins and searches. Alternatively, this could be a simple associative array somewhere, but that's not very user-manageable.

table contact_types
id|contact_id|user_id|type_id|contact_value

Example:
13265|452353|1234|5|test@example.com

So the staff member can have unlimited contact types, and are joined on user ID. This can make for more complex joins, but it's not too difficult.

Note that you'll never have to physically store the textual value of "type_id" anywhere but in the types table/array. It's just referenced by an ID. So any time that type changes, your database requires no updating, the displays change with it.

kajje




msg:4102804
 4:01 am on Mar 23, 2010 (gmt 0)

Thanks Rocknbill, this approach sounds indeed like the way to go.

As a matter of fact, this is also the way to go for my other databases. I will need some serious redesigning, but it for sure will pay off in the future!

Thanks
Kajje

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved