With my Data Model seemingly done, I have started looking into what needs to be done to go from the user selecting a "Pretty URL", to retrieving data from MySQL, to displaying the requested Article.
Here is one thing I have noticed that seems a little amiss... (This may be more of a mod_rewrite or PHP question.)
Others have stressed to me that having a URL that is too long - even if it is "pretty" - is counter-productive. As such, I decided to drop "Dimension" from my Pretty URLs.
So, let's say that a user clicks on an Article link in the "Tax Season" SubSection.
Now remember that the "Tax Season" subsection could either be located in the center area of a given Section (i.e. Dimension = "Featured Finance"), or off in the right margin of a given Section (e.g. Dimension = "Seasonal").
In either case - since "Dimension" doesn't exist in the URL - they would both be pointing to a URL like this...
So, logically, each of the two different areas on the given Section page would map to two different records
in the database.
However, physically, only one URL (see above) would map to two different records
in the database.
Section Dimension SubSection Article
-------- ---------- ----------- --------
Finance Featured Finance Tax Season Save Your Taxes for a CPA
Finance Seasonal Tax Season Save Your Taxes for a CPA
And now for my question(s)...
When I use PHP to *sanitize* the submitted URL, how should I go about checking things?
Off the top of my head, here are the general steps I'd need to take...
1.) Use mod_rewrite to parse up the URL and assign different parts to variables (i.e. Section, SubSection, and Article).
2.) Next, I would run this query...
SELECT s.name AS Section, d.name AS Dimension, ss.name AS SubSection, a.heading AS Article
FROM section AS s
INNER JOIN section_dimension AS sd
ON s.slug = sd.section_slug
INNER JOIN dimension AS d
ON d.slug = sd.dimension_slug
INNER JOIN dimension_subsection AS ds
ON d.slug = ds.dimension_slug
INNER JOIN subsection AS ss
ON ss.slug = ds.subsection_slug
INNER JOIN article_placement AS ap
ON (sd.section_slug = ap.sd_section_slug)
AND (ds.dimension_slug = ap.ds_dimension_slug)
AND (ds.subsection_slug = ap.ds_subsection_slug)
INNER JOIN article AS a
ON a.slug = ap.article_slug
WHERE s.name = 'Finance'
AND ss.name = 'Tax Season'
AND a.heading = 'Save Your Taxes For A CPA'
ORDER BY s.sort, d.sort, ss.sort
...and make sure that it returns AT LEAST ONE RECORD.
(*NOTE: Because I'm thorough, I would actually probably run Query #1 to check that there was a valid "Section", and then Query #2 to check for a valid "Section/SubSection" pair, and then Query #3 - which is the one above - to check the whole combo.
Doing so would allow me to present more customized Error-Handling and Error-Logging...)
3.) If there was at least one record, then I'd run a final query which would gather all of the needed columns to display the Article itself.
Not sure if I could just query the ARTICLE table to get the Article, or if to be thorough I would want to run a query against all tables - like above - to make sure that the "Section", "SubSection" and "Article" all match?? (Security is *really* important to me, so I get pretty neurotic when it comes to checking that data submitted by the user is "clean"!!!)
(If the latter, then I'd need to add a column to ARTICLE_PLACEMENT called something like "placement_primary", so that I would know which record to pull in case the Article exists in multiple forms in my ARTICLE_PLACEMENT table, if you follow that?!)
How does all of that sound??