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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

Now, that's the case a LEFT JOIN is t be used :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

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

Post 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.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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?
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

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

Post 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:
Post Reply