Page 1 of 1

fetching unknown column names

Posted: Tue Nov 18, 2008 6:47 pm
by cali_dotcom
i'm building a website where users can add articles. on the admin side, they can also add new fields on the add articles page.
to allow the user edit the articles, i have to query the database and then assign the results from the query to variables, usually i use the $fieldname.
the problem is, if a user added a new field while he added the article, i can't fetch the content by field anme from the rusult set of the query.
eg:

Code: Select all

$select = $article->select();
        $select->where('id =?', $id);
        while ($rows = $article->fetchAll($select)){
            $this->cat_id = $rows->cat_id;
            $this->article_title = $rows->title;
            $this->description = $rows->description;
            :
                        :
        }
but if i dont know the name of the new field(s) the user added, i can't assign it to a variable.
PS: i was thinking of a solution that would need me to fetch the name of the fields form the article_fields table, but then again i got stuck trying to assign the field name to a variable that had the same name i.e:

Code: Select all

$select = $article_fields->select();
            while ($rows = $article_fields->fetchAll($select)){
            ${$rows->field_name}= $rows->field_name;
            ;
Does anyone have any solutions??

Re: fetching unknown column names

Posted: Tue Nov 18, 2008 7:26 pm
by Eran
If users can change the db schema on a whim, your database design is probably lacking somewhat. If you could post it here, I'm sure plenty of people can give you feedbacks

Re: fetching unknown column names

Posted: Tue Nov 18, 2008 7:49 pm
by cali_dotcom
i have a table where the description of the newly added field is stored but not the data inputed.:

CREATE TABLE `article_fields` (
`id` int(11) NOT NULL auto_increment,
`field_title` varchar(75) NOT NULL,
`field_type` varchar(75) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


then i have another table where all data of all fields submitted is stored

CREATE TABLE `articles` (
`id` int(11) NOT NULL auto_increment,
`cat_id` int(11) NOT NULL,
`title` varchar(75) NOT NULL,
`description` varchar(300) NOT NULL,
`text` varchar(5000) NOT NULL,
`post_date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

i hope this makes my problem clearer..

can anyone suggest a better to solve this problem?