A PHP application we are developing requires the usage of a rather complicated query. At least, I think it's complicated, but most likely it's the result of my inefficent coding.
The content for our site is produced entirely from a database. The content is region-specific for across the country, and 'objects' (various bits of HTML) are pulled back for different levels (National, State, County, and City).
As the objects come back, they are sorted by a "position" value. (some objects are supposed to be higher up in the page than others). This is not to say that a City object always appears higher up than a National object, or vise-versa.
The object positions always need to use the most specific position when being returned (City over County, County over State, etc). To aid in this, the table that handles the object placements has a field called 'level'.
The following SQL will create the objects table, and insert a few sample rows:
Code: Select all
CREATE TABLE `tbllocation` (
`ID` bigint(20) NOT NULL default '0',
`pageID` bigint(20) NOT NULL default '0',
`placeID` bigint(20) NOT NULL default '0',
`position` int(11) NOT NULL default '0',
`level` tinyint(1) NOT NULL default '0',
KEY `ID` (`ID`,`pageID`,`placeID`,`position`),
KEY `level` (`level`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `tbllocation` VALUES (4883342942, 3347643276, 1, 1, 1);
INSERT INTO `tbllocation` VALUES (3090513050, 3347643276, 1, 51, 1);
INSERT INTO `tbllocation` VALUES (6341746178, 3347643276, 1, 100, 1);
INSERT INTO `tbllocation` VALUES (3389986023, 3347643276, 2687927423, 50, 3);
INSERT INTO `tbllocation` VALUES (5375897807, 3347643276, 4645043051, 25, 2);
INSERT INTO `tbllocation` VALUES (3585837960, 3347643276, 4462021421, 1, 1);The ID under "pageID" is also passed into the query, and is thusly irrevelant in this problem, as well.
The "ID" field corresponds to an associated ID in the tblObjects table. Table structure and data follows:
Code: Select all
CREATE TABLE tblobject (
objID bigint(20) NOT NULL default '0',
name varchar(100) NOT NULL default '',
code mediumtext NOT NULL,
panel varchar(20) NOT NULL default '0',
objNav smallint(1) NOT NULL default '0',
objNavSub tinyint(1) NOT NULL default '0',
KEY name (name,panel),
KEY objID (objID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO tblobject VALUES (4883342942, 'Cities Logo', '<img src="http://www.citiesunlimited.com/images/culogo_redesign.gif" width="243" height="62" border="0">', 'header', 0, 0);
INSERT INTO tblobject VALUES (3389986023, 'Kansas Works!', '<strong>KANSAS PAGE!</strong>', 'content', 0, 0);
INSERT INTO tblobject VALUES (5375897807, 'JOCO WORKS!', '<br><em>JOCO WORKS!</em><br>', 'content', 0, 0);
INSERT INTO tblobject VALUES (3585837960, 'Shawnee WORKS!', '<br><u>SHAWNEE WORKS!</u><br>', 'content', 0, 0);The query for this whole thing needs to pull back a list of all the objects, when the following is passed to it:
- A list of the city, county, state, and national ID's (complete)
- The pageID of the current page to pull objects back for (complete)
The query needs to be able to distinguish between these, and pull back just the county level for that particular object. Right now, I'm doing that as a subselect, which I know isn't the best way ... but I lack the skills to do it properly (I'm assuming it's a GROUP BY).
Here is my query:
Code: Select all
<?php
global $platform;
$platform_locations = "";
$platform_locations .= "'{$platform['cityid']}', ";
$platform_locations .= "'{$platform['countyid']}', ";
$platform_locations .= "'{$platform['stateid']}', ";
$platform_locations .= "'1'"; // include national listings
$SQL = "
SELECT tblObject.objID AS myid, tblObject.panel AS panel, tblObject.code AS code
FROM tblObject
LEFT JOIN tblLocation ON tblObject.objID = tblLocation.id
WHERE tblLocation.placeID IN ({$platform_locations}) AND
tblLocation.pageID IN ('1', '{$platform["pageid"]}') AND
tblLocation.level = (
SELECT MAX(level)
FROM tblLocation
WHERE placeID IN ({$platform_locations}) AND
pageID IN ('1', '{$platform["pageid"]}') AND
id = (myid)
)
ORDER BY position ASC, name ASC";
?>Any ideas?