Page 1 of 1

Return a row even if empty is section exists but no pages?

Posted: Fri Feb 20, 2009 8:08 am
by JAB Creations
I'm trying to determine how to write a single MySQL query that will do two things if possible...

1.) I want to determine if a section exists...

Code: Select all

SELECT * FROM cms_sections WHERE section_name = 'web'
2.) If a section exists I want to execute a JOIN to generate a simple index (there is a relational table for sections (cms_sections) and the main table for the actual individual pages (cms_pages)). Otherwise I want to return a row that I can have a null without not returning a row as I'll use mysql_num_rows to count if any rows were return. If no rows were returned then the section doesn't exist in the database...but if the section is empty I still want to return something.

A little help to kick my mind in to gear would be greatly appreciated. A week's worth of socializing and no code has compromised my nerd status! 8O

Re: Return a row even if empty is section exists but no pages?

Posted: Fri Feb 20, 2009 4:31 pm
by VladSun
Now, that's the case a LEFT JOIN is t be used :)

Re: Return a row even if empty is section exists but no pages?

Posted: Fri Feb 20, 2009 7:40 pm
by califdon
Don't expect SQL to return something when there's nothing to return. What you can do is check the number of rows returned and if it's == 0, you can take appropriate action.

Re: Return a row even if empty is section exists but no pages?

Posted: Fri Feb 20, 2009 8:11 pm
by JAB Creations
I'm looking at MySQL Control Flow Functions right now and am very interested in IFNULL.

I'm very sure I can achieve this using Vlad's MySQL subselect somehow...I just have to figure out how to construct it all.

So the conceptual logic I am attempting to do with MySQL is...

IF (yes)
{
JOIN to pages table to retrieve the list of pages to be used for the index
if the result ISNULL return something any way!
}
ELSE return nothing (default/fine)

The PHP logic is simple...
1.) If there are no rows the section does not exist.
2.) If there is data...
2-A.) If the return data is not the "section exists but not empty" value spit out the index in a loop.
2-B.) Else show the section information but that there are no pages.

I'm pretty sure this can be done...it's a matter of learning the language a little more. :)

The issue I want to avoid is not returning any data if the section exists but it empty...then it will show it as not existing even though it does exist; that is what I'm trying to avoid through only a single query.

Re: Return a row even if empty is section exists but no pages?

Posted: Fri Feb 20, 2009 8:44 pm
by JAB Creations
The first line is valid though the second has errors...

Code: Select all

SELECT * FROM (SELECT * FROM cms_sections WHERE section_url='web')IF (id IS NULL, 'exists', 'does not exist') AS tag1exists
This is based upon John Cartwright's post. I wouldn't want to say it 'exists' but rather just return the data in the subselect. I'm trying to give you guys a code-related idea of the direction I'm going with this.

Re: Return a row even if empty is section exists but no pages?

Posted: Fri Feb 20, 2009 10:46 pm
by JAB Creations
This sort of seems to work though it always returns the column 'b'. I'd like to figure out how to fix it to only return column 'b' if the id column is null which I thought would work though is...why it isn't though I'm not getting any error messages in phpMyAdmin is beyond me at the moment. Here is what I have...

Code: Select all

SELECT *, IF (id IS NULL, id, 'does not exist') AS b FROM cms_sections WHERE section_url='web'
Thoughts please?

Re: Return a row even if empty is section exists but no pages?

Posted: Sat Feb 21, 2009 3:11 am
by JAB Creations
I really was over complicating things when I had the LEFT JOIN id's backwards and reversed (choosing FROM the wrong table). Here is a screenshot...
http://img4.imageshack.us/img4/6600/mysql.gif

Code: Select all

SELECT cp.meta_description, cp.meta_language, cp.meta_robots, cp.title, cp.url, cs.section_name, cs.section_urlFROM cms_sections AS csLEFT JOIN cms_pages AS cp ON (cs.id = cp.id_section)WHERE cs.section_url = 'web'
This returns what I want...null, null, and more null!

Victory is mine for I have defeated...err myself! :mrgreen: