MySQL SubSelect, null, and if for section to page condition?

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:

MySQL SubSelect, null, and if for section to page condition?

Post by JAB Creations »

I'm trying to first determine if a section exists; if it does then select a page's information all in a single query.

I think this could probably be done though I'm having difficulty finding decent examples to work from.

So the first thing I have is the following...

Code: Select all

SELECT *FROM ( SELECT id FROM cms_sections WHERE section_url='error' ) AS a
If the section exists I'll get it's ID.

What I'd like to do is figure out how to determine if a.id is null; if it is then do nothing (no rows returned) and I can server an HTTP 404.

However if a.id is not null execute another sub-SELECT to get the page's information.

So the second half of the query that I'm trying to figure out how to write is...

if a.id is not null ...and then SELECT the page's contents from the cms_page table.

So let's say as a user you're looking at my example 404 page for whatever reason (the HTTP request would be 200 as you're intentionally looking at the 404 page). Here is an example of some logic that I tried but am unable to find anything even half decent to read from (as examples are what I learn best from).

Code: Select all

SELECT *FROM (SELECT id FROM cms_sections WHERE section_url='error') AS aIF a.id IS NOT NULL THEN SELECT * FROM (SELECT * cms_pages FROM cms_pages WHERE id_section=a.id AND url='404.php')
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL SubSelect, null, and if for section to page condition?

Post by JAB Creations »

Some more things I've tried was using UNION though apparently that requires the same number of rows from each query so that dead-ended rather quickly.

These also both do not work though at this point I still haven't found any actual examples much less any useful examples. :|

Code: Select all

IFSELECT id FROM cms_sections WHERE section_url='error' IS NOT NULLTHENSELECT * FROM cms_pages WHERE url='404.php'

Code: Select all

SELECT * FROM (SELECT id FROM cms_sections WHERE section_url='error') AS aIF a.id IS NOT NULLSELECT * FROM (SELECT id AS id_section FROM cms_sections WHERE section_url='error') AS b
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL SubSelect, null, and if for section to page condition?

Post by JAB Creations »

Que Vladsun! Hey this INNER JOIN stuff is starting to actually stick. :mrgreen:

Actually it's a little more vague then that but what I can do is actually rather much simpler then I was thinking.

I have two tables: cms_pages and cms_sections. In cms_sections I store the section's URL. I also determine by the requested URI what section the visitor is in. So all I really have to do is see if the requested URI and the section_url match! If they don't but a page is still returned then the page does exist though in another section (and now I have it's ID any way!) so I can probably program a special "almost 200" error message. :mrgreen: If nothing is returned then they're so like totally making up fake links. :twisted:

Code: Select all

SELECT * FROM cms_pages AS cp INNER JOIN cms_sections AS cs ON (cs.id = cp.id_section)WHERE cp.url='404.php'
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL SubSelect, null, and if for section to page condition?

Post by VladSun »

You need a LEFT JOIN in this case. That's because you want to JOIN cms_pages and cms_sections even if cms_sections row is NULL.
An INNER JOIN is used when both tables must always have rows returned according to the ON condition.

Also, if you want to use UNION (in other cases) don't use *, but a list of the fields you want to get. This way your query will still be a valid one, even if you add/remove other fields in/from your tables lately.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: MySQL SubSelect, null, and if for section to page condition?

Post by JAB Creations »

I was actually trying some LEFT JOIN (I did INNER JOIN first though!) queries though it seemed to only return all rows or not.

I've got an interesting setup though!

If a page isn't found then I look for a page like the page requested.

If no page is found in the section using this URL as an example...
example.com/section/page

Then I do a second query...

Code: Select all

SELECT * FROM cms_pages AS cp INNER JOIN cms_sections AS cs ON (cs.id = cp.id_section) WHERE cp.url LIKE '%".$cms->page."%'"
Then I count the returned rows. If it's one then I use that page to do a HTTP 302 (found) redirect to.
If there two or more rows then I direct to the section's index...and then highlight possible matches.
Lastly if there are still no returned rows then it becomes a %100 pure 404!
Post Reply