Hello,
I have a table "page_content" that is used for dynamically generating the common page content throughout the site (and of course is changeable by admins). Here's the create table:
Table: page_content
Create Table: CREATE TABLE page_content (
id mediumint NOT NULL auto_increment,
page varchar(32) default NULL,
zone varchar(32) default NULL,
site char(1) default NULL,
page_message text, <-- "content_col"
welcome_line tinytext, <-- "content_col"
PRIMARY KEY (id)
) TYPE=MyISAM
I need a select that will "coalesce" the result for the current page based on the following logic:
-- Return result where page=$current_page and content_column is not null, else
-- Return result where zone=$current_zone and content_column is not null, else
-- Return result where site=1 and content_column is not null, else,
-- Return nothing.
So let's say that there are the following entries in the table:
1.
page = $current_page
zone = NULL
site = NULL
page_message = "Some message"
welcome_line = NULL
2.
page = NULL
zone = $current_zone
site = NULL
page_message = NULL
welcome_line = "Some welcome line"
I want the result set to be like:
page_message = "Some message"
welcome_line = "Some welcome line"
Of course if id 1 were different...
1.
page = $current_page
zone = NULL
site = NULL
page_message = NULL
welcome_line = NULL
Then the result should be:
page_message = NULL
welcome_line = "Some welcome line"
AND if there were another id like this:
3.
page = NULL
zone = NULL
site = 1
page_message = "A site level message"
welcome_line = NULL
Then the result would be:
page_message = "A site level message"
welcome_line = "Some welcome line"
And here is the sql I have so far:
$select = mysql_query("select page_message, welcome_line from page_content where
(page_message is not null or welcome_line is not null) and
(page='{$page->current}' or zone='{$page->zone}' or site=1)
order by site, zone, page desc")
This does not work as required. It provides TWO result rows:
page_message = "Some message"
welcome_line = NULL
page_message = NULL
welcome_line = "Some welcome line"
... and I need them on one!!
Is this possible? Gratitude+ to any helpers!
(sorry for the ugly post...) :bemused:
