Unique ID on MySQL Columns, other than name

ElectusUnum

This may be insane, and I may be an idiot seeing as I've self taught myself PHP and am only beginning to weild it properly. My problem is this, I'm writing a news script that will have the ability to add custom news fields. A user could add something like "Current Favorite Song:" so that the news form would have the news item title, content, and current favorite song:. The way that I've figured to do this, since everything's held in a MySQL database, is to have the php script add a column to the database like current_favorite_song and then when printing out the HTML form read through each field (column) and display it's name. The problem is I need some fields to be hidden, some to be one line text fields (in html) and some to be full text boxes. This is probably confusing as hell, but is there a way to uniquely identify a MySQL column other than its name? I've thought about setting different fields to be different types (varchar,blob,etc.) but mysql_field_name only returns a few basic mysql types. text() and blob() are both returned as blob and varchar is returned as string.

Maybe I'm insane, but any help is appreciated!

-Matt

dnszero

The only guarunteed unique field identifier available is the column number. You can always reference the first column of a result returned as an array with $row[0], the second column as $row[1], etc... So, you could create a seperate listing that would contain the formatting data you want for each column by number, eg:

Column 0 -> Visible, Bold font, 1 line
Column 1 -> Visible, Normal Font, 5 lines
Column 2 -> Hidden...

This could be stored as a multi-dimensional array, a flat text file, or put into a db table.

If you're not familiar with CSS you might want to find a couple articles on it, as that can help make complex formatting easier.

Lars_Berg

I think that your idea of dynamically adding columns to the database is all wrong.

Use an additional table with three columns

item property propertyValue

which makes it possible to add any number of properties for an item.

ElectusUnum

If I had a seperate table devoted to the different fields, when a custom field was added I would need PHP to add a column to the actual news table, the one containing the content, as well as this other table that would contain data about how it should be displayed in HTML. Is this what you mean?

Excuse the newbieness 😕 .

-Matt

Krush019

i think he means don't ad new fields at all. Make a new table called "custom fields" and ad rows to it rather than fields.
Give each news post a unique id, then when someone wants a custom field ad a new row to "custom fields" table with the unique id for that post and the field they want.

oh and yes if you have 3 display options then put them in there own table with ids and write the id to the "custom fields" table.
So the tables may look something like this ( if it prints right)

custom feilds table:

post_id field content display

  1       fav music      hip-hop           1
  2       fav car          ferrari              3
  1       fav color       blue                 2

display type table:

id display_type

1 bold
2 invisible
3 etc

Well i hope thats not more confusing than it is helpful 🙂

ElectusUnum

Ok, I think I got it - I'm not totally clear but I understand enough 🙂. I'm actualy thinking about redoing the whole database now that I've read these posts. Huge thanks to everyone that helped me!