simonrs Posted February 16, 2009 Share Posted February 16, 2009 Hi all, Would really appreciate some help on a problem I'm having. Every piece of content on my site is an extension of a php class called "Content". One of the variables/properties of this class is a "fields" array, which stores as keys => values all fields for that piece of content. For instance, this array might look like this for a blog post: array('author' => 'simonrs', 'title' => 'my blog title', 'content' => 'blog content bla bla bla'); My database schema for storing all these content objects in two tables is as follows: content: id, class content_fields: content_id, field, value So in the above example, it will be stored as 1 row in content: 1,BlogEntry And a few in content_fields: 1,author,simonrs 1,title,my blog title 1,content,blog content bla bla bla Onto my problem! I want to, in one database select query, search over more than one field for a particular content class - for instance, search over an author's name and a blog entry's title at the same time. The problem is, that I can't think of how to design such a query as it must involve two rows from the same table, both extracted as part of the same "result row". Is this possible or is a schema redesign advisable? Thanks greatly in advance for any help any one can offer me. Simon Link to comment https://forums.phpfreaks.com/topic/145475-problem-implementing-an-object-database/ Share on other sites More sharing options...
corbin Posted February 16, 2009 Share Posted February 16, 2009 You're wanting to search serialized arrays in rows? Ewwwwwwwwww. Link to comment https://forums.phpfreaks.com/topic/145475-problem-implementing-an-object-database/#findComment-763753 Share on other sites More sharing options...
simonrs Posted February 16, 2009 Author Share Posted February 16, 2009 Ok... could you suggest a better schema that doesn't involve dynamic adding of tables or columns? Link to comment https://forums.phpfreaks.com/topic/145475-problem-implementing-an-object-database/#findComment-763781 Share on other sites More sharing options...
trq Posted February 17, 2009 Share Posted February 17, 2009 Your schema seems fine to me, I think corbin misread your post. Given your description, your query will indeed return two rows as its result, I don't however see the proplem with that. Link to comment https://forums.phpfreaks.com/topic/145475-problem-implementing-an-object-database/#findComment-763806 Share on other sites More sharing options...
simonrs Posted February 17, 2009 Author Share Posted February 17, 2009 But if I wanted to search over two fields, how would that work? Let me give a simple query (may not be syntactically perfect but you'll get the idea) that will work, for searching on the title of a blog: SELECT `content`.`id`, `content`.`value` FROM `content`, `content_fields` WHERE `content`.`class` = 'blog' AND `content`.`id` = `content_fields`.`content_id` AND `content_fields`.`field` = 'title' AND `content_fields`.`value` LIKE '%search text%' That'll work fine, returning to php a data structure of all matching content id's and titles. What if I wanted to extend that query, though, so as well as the titles, it also returned the body and author of each post? They don't have a different column name, so they can't be specified in the list of columns to select. And what if I wanted to extend it further to search over more than just the title, say it had to match a certain title string, and be by a certain author? Unfortunately I want (or at least, want to be able to have) the ability to do both those things, so I think I'm looking at a schema change. Any ideas? Or am I going to be forced to add a table for each content type, which I'd prefer not to do as I'd have to change the database manually or by code (messy) everytime I add a new content type or change the schema of one. Link to comment https://forums.phpfreaks.com/topic/145475-problem-implementing-an-object-database/#findComment-763819 Share on other sites More sharing options...
corbin Posted February 17, 2009 Share Posted February 17, 2009 Yeah I think I entirely misunderstood your first post. Sorry hehe. Anyway, maybe an IN() clause could work? WHERE field IN('author', 'title', 'content') for example. Link to comment https://forums.phpfreaks.com/topic/145475-problem-implementing-an-object-database/#findComment-763888 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.