[FIXED] Problem with a subselect (better way to do it?)
Posted: Sun Jul 11, 2004 3:26 pm
This is my first post on the DevNetwork forums, but a friend of mine posts on here (DeuceOfHearts). This post is long, but mostly because I have provided a lot of code, and explained the application in great detail, to aid all you in helping me fix it.
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:
The various ID's under "placeID" are representative of Shawnee City, Johnson County, Kansas State, and United States Nation. These are irrelevant, as they are passed in the query that pulls the data back.
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:
This will populate the objects table with enough data for this example (as this is all the data we have to work with anyway, and is sufficent).
The query for this whole thing needs to pull back a list of all the objects, when the following is passed to it:
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:
What really <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> me off is that it works if I query both elements separately (ie, do the subselect, get the result, and then paste it in the original query), but it returns '0' rows when done all at once.
Any ideas?
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?