Okay, first an explanation of what I am trying to accomplish - just so you don't think that I am crazy!
I am building a content management system and it has articles. Each article then can have objects attached (ie images, opinion polls, SWF...).
Each object type has different attributes, thus needs to reside in a new table
Since each object type has it's own table, I have a table that links articles to objects and contains information about what article type it is.
I will show you how I would do it using more than one query...
$object_links_sql = "SELECT * FROM object_links WHERE article_id='1'";
$object_links_result = mysql_query($object_links_sql);
while ($object_link = mysql_fetch_array($object_links_result)){
$object_data_sql = "SELECT * FROM object_".$object_link["object_type"]." WHERE object_id='".$object_link["object_link_id"]."'";
$object_data_result = mysql_query($object_data_sql);
$object_data[] = mysql_fetch_array($object_data_result);
}
Is it possible to do this with just one query?
I think I would like to be able to perform a join that uses part of the result as the table name to join to. (still following?)
(note the tables have a prefix of object_)
SELECT object_links., objectobject_links.object_type.
FROM object_links
LEFT JOIN objectobject_links.object_type ON object_object_links.object_type.object_id = object_links.object_id
WHERE object_links.article_id = '1'
Am I part way there?
Is it possible to do this?
