User-Added Inputs

[alexjewell]

I'm creating an inventory system for a school district's technology department. The system will keep track of thousands of technology pieces across two campuses, inventorying anything from full computers to digital microscopes and other random items. Now, I can have a list of default input fields, for when they add items to the database, but there is a need for custom fields. In other words, as new fields arise because of specific items, how do I make it possible for the users (the department's administrators) to add custom fields for those items as they add them? Thanks.

[djr33]

Depends on exactly what you want.

I create an inventory script for myself a while ago. I knew that some items would need extra info but not what.
However, I didn't really care about it's name.

So what I did was automatically a field called 'extra1'.
Then if 'extra1' was submitted (non empty), I generated 'extra2' the next time around.

So recursively every time the existing 'extra' fields were filled I added another blank one. Of course this means you need to update the page before having a new field, but that's not too bad. You could also make sure there are at least 3 fields blank, etc.


This presents problems for saving to a database, though you could do a CSV in a column called "extra".
For me I was using xml, so it was dynamic enough that it didn't matter.

If you must name the fields, then I suppose you can do exactly the same thing with fields named "extra1title" and "extra2title", ....



Alternatively you could deal with all of this using some sort of complex Javascript GUI that allows the user to reprogram the fields; of course then the PHP would have to accept whatever it gets (which isn't that hard-- just dump the whole $_POST array, one by one, into wherever you're storing it). This is complex and messy, though, so I'd recommend against it.



Another simpler way might be to have an admin page where they can manipulate extra fields they want that are then generated for each of the items on the "edit item" page, as needed. This doesn't really solve the database issue, but I suppose you could allow this field management page to add/subtract(?) columns from the db table.

[alexjewell]

This certainly would be easier with XML, then. But doing searches is more difficult with XML, correct? With MySQL you can just find something WHERE something = something.

Also, I'll be needing to have a function that exports the info as an excel spreadsheet - this would be easier with XML, as well, correct?

[djr33]

I'm not sure about excel, but I bet there's something for MySQL to Excel out there.


It depends on how you look at it. A dynamic database is never fun (security, planning ahead, stability, etc.), so that's a reason to avoid a db. However, if you can just settle for an "extra" column in which you list all of the extra fields (as a csv, or even as XML if you'd like), then it becomes a lot more manageable.
In the single case of this, yes, XML would be easier, but it is much harder to search and organize overall.

I'd say probably go with a database and you may have limited functionality on the extra fields. Or store XML in the 'extra' column of the database and while it'll be more work for you, that should be fully functional.

[alexjewell]

Ok, so say I end up with the final field of, "Extra Fields." Then, in the textarea, they could organize it like:

ExtraName1-ExtraValue1,ExtraName2-ExtraValue2,ExtraName3-ExtraValue3

That would be stored in the database, technically, as CSV. I can always throw those values into an XML format later, too. But how would I search for those values later? Say I searched for ExtraValue2; would it be able to find JUST ExtraValue2 in the ExtraFields column?

[djr33]

1. I wasn't suggesting they enter it as a CSV, but that you STORE it as a CSV. In other words, they enter it in the increasingly numbered "extra$number" columns I mentioned above.
Then in the PHP you implode() all of those into a list of CSV variables. Then you insert that into the database.
To display the form again, you explode() them and display them in separate forms.
If you do XML instead of CSV, do it the same way.
To the user, it will look like they are "real" fields.

2. You cannot search it this way. You could search to see if a term is contained in ANY of the extra fields (to shorten the result set) then you can use PHP to do the rest.
There's no ideal way to have variable fields AND searchable fields, but this way you can search all of the standard fields and only need to work out searching the variable fields in a more complex way.