One Query to rule them all - Is it possible?

[laek]

I have been banging my head against the wall for the last few days with this.  My problem is that I am writing a PHP website that grabs content from a database (basically a content management system, or CMS) for my job.  I want the database to be dynamic so if we have to add new types of content to the website, I don't have to recode a large chunk of the PHP to work with it.  I also want the pages to load quickly, since dial-up users often visit the website, and I want to cater to them as well - and hey, who doesn't love a web page that loads quick?

 

The best way I can think of to do this is to do two queries on the database, the first would send the page name to a 'landing table' then return a profileID for that specific of page.  Then I want to query the database with something like

 

Here is a few examples of tables of the database i am thinking about using:

(bold is the table names' date=' list items are the fields, and the ***** is what is stored in the database, itself.)[/quote']

 

profiles:

• profileID

metaTags:

• metaTagID
• profileID
• contentType - this will always say "metaTag" (will be used to detect what this item is in the array, i'll get to that later)
• details1 - This is the <meta name='*****' part.
• details2 - This is the content='*****' part.

linkRel:

• linkRelID
• profileID
• contentType - this will always say "linkRel" (will be used to detect what this item is in the array, i'll get to that later)
• details1 - This is the <link rel='*****' part.
• details2 = This is the href = '****'
• details3 = This is the type = '****'

 

(This is a big problem area:)

SELECT * FROM profiles LEFT JOIN linkRel ON (profiles.profileID=linkRel.profileID) LEFT JOIN metaTags ON (profiles.profileID=metaTags.profileID) WHERE profileID='###'

this SQL outputs something that looks like:

data spill out from profiles table

data spill out from linkRel table

data spill out from metaTags table

 

 

| profileID | linkRelID | profileID | contentType | details1 | details2 | details3 | metaTagID | profileID | contentType | details1 | details2

This outputs a crapton of multiple data, since if there are two types of meta tags for this one profileID (such as a keywords meta tag, and a description tag)

 

Where I want it to spill out like this:

| profileID | contentID | details1 | details2 | details3 |

data from the linkRel table

data from the linkRel table

data from the metaTags table

data from the metaTags table

data from the metaTags table

The idea is that they share column names' date=' so the query (or the array once in PHP) has less NULL values.[/quote']

 

 

Once I get this query to run correctly how I want it to.  I can throw it into my PHP code and have it fill an array named $masterRow[] or something like that to be ran through a while loop until there are no more records for it to read.  Within that while statement I could have a SWITCH/CASE Statement which pulls out the $masterRow['contentType'] and decides what to do based off of that.

Inside each case, it appends to a specific variable (if the case is metaTags, it would append to $metaTags and add in the tag data around the dynamic content pulled from the database).

 

Once the Switch/Case and While statements are done, variables are all pretty & loaded, make the HTML code by calling the variables into their own place.

 

Here is the code I have so far built on this logic.

<?php
//--stuff to get $profileID goes up here--

//make variables to be filled lower on the page
$metaTags='';
$linkRel='';

$masterSQL = "SELECT * FROM profiles LEFT JOIN linkRel ON (profiles.profileID=linkRel.profileID) LEFT JOIN metaTags ON (profiles.profileID=metaTags.profileID) WHERE profiles.profileID = '".$profileID."'";
$masterQuery = $connector->query($masterSQL);

while($masterRow = $connector->fetchArray($masterQuery))
{
switch ($masterRow['contentType']) 
{
//Build page's metaTags variable.
case "metaTags":
	$metaTags .= "<meta";	//Build Meta Tags!
	if($masterRow['details1']!='')
		$metaTags .= " name = '".$masterRow['details1']."'";
	if($masterRow['details2']!='')
		$metaTags .= " content = '".$masterRow['details2']."'";
	$metaTags .= " />/r/n"
	break;

//Build page's linkRel variable.		
case "linkRel":
	$linkRel .= "<link";
	if($masterRow['details1']!='')
		$linkRel .= " rel = '".$masterRow['details1']."'";
	if($masterRow['details2']!='')
		$linkRel .= " href = '".$masterRow['details2']."'";
	if($masterRow['details3']!='')
		$linkRel .= " type = '".$masterRow['details3']."'";
	$linkRel .= " />/r/n";
	break;
}
}
?>

<html>
<head>

<? echo $metaTags; ?>
<? echo $linkRel; ?>
</head>
<body>

 

 

Basically the main problem is that I want my LEFT JOIN query to show up how a UNION query does, but I have to make this dynamic so the tables can't be locked down to "all tables have 5 details sections" so that the UNION query will run (since UNION requires all tables involved to have the same amount of fields) because then I'm stabbing myself in the foot when I need a details4 field for something.  I also don't want to make each table go up to details15 to avoid this, since then the Query would take FOREVER to run, which brings me back to my page-load-time problem >.<

 

Any help is appreciated, 

-Laek

======

I know the profiles table looks useless right now' date=' but I plan to use that for later things to deal with tables that have a ton of things to spill out (like the links at the top of the page, there is a possibility to have more than 25 links per page, so I'd be throwing an ForeignKey(linksProfileID) into the profiles table so i can reuse "linksProfileID" to get the same set of links on different pages to reduce database redundancy.[/quote']

[fenway]

I'm very confused... do you need all of this data back all of the time?

[laek]

I'm very confused... do you need all of this data back all of the time?

Every time that a visitor loads a page, it would hit the database once to grab the content in one query.

 

Right now I'm playing with UNION queries instead of JOINs, but i'm running into the problem with UNIONs in this situation is that the database does not allow me to have different amounts of columns like a JOIN does.

 

Is there a way to run a query like:

SELECT profileID, details1, details2, details3 FORM linkRel where profileID='0'
UNION
SELECT profileID, details1, details2 FROM metaTag where profileID='0'
UNION
SELECT profileID, details1 FROM pageTitle where profileID='0'

?

 

I think I over-explained it before, if I can get this query down, i can get the rest.

 

Thank you,

-Lake

[fenway]

Is there a way to run a query like:

SELECT profileID, details1, details2, details3 FORM linkRel where profileID='0'
UNION
SELECT profileID, details1, details2 FROM metaTag where profileID='0'
UNION
SELECT profileID, details1 FROM pageTitle where profileID='0'

 

Yes, fake the other columns:

 

SELECT profileID, details1, details2, details3 FORM linkRel where profileID='0'
UNION
SELECT profileID, details1, details2, NULL AS details3 FROM metaTag where profileID='0'
UNION
SELECT profileID, details1, NULL AS details2, NULL AS details3 FROM pageTitle where profileID='0'

?