Page 1 of 1

[FIXED] Problem with a subselect (better way to do it?)

Posted: Sun Jul 11, 2004 3:26 pm
by insta
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:

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 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:

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);
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:
  • A list of the city, county, state, and national ID's (complete)
  • The pageID of the current page to pull objects back for (complete)
One problem is that the objects may need to overwrite their own positions at different levels. (wha?) As an example, an advertiser might pay $x per month to be listed at the fifth position. However, because they have more coverage in a certain area (brick-n-mortar), they may pay a little extra to be listed at the third spot in one county alone. This will cause two entries in the table, one with the 'placeID' of the state level at position 5, and one at the county level at position 3.

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";


?>
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?

Posted: Sun Jul 11, 2004 3:46 pm
by feyd
it sounds like your version of mysql doesn't support subselects..

Posted: Sun Jul 11, 2004 3:47 pm
by insta
It does. It's 5.1, whatever the current alpha is.

The query doesn't error, it just doesn't return any results.

Posted: Mon Jul 12, 2004 6:54 am
by insta
Fixed it.

Code: Select all

SELECT 		tblobject.objID, tblobject.panel, tblobject.code, MAX(tbllocation.level) AS level, tbllocation.position 
		FROM    	tblobject
		INNER JOIN 	tbllocation ON tbllocation.ID = tblobject.objID 
		WHERE		tbllocation.placeId IN (&#123;$platform_locations&#125;) AND 
					tbllocation.pageID IN ('1', '&#123;$platform&#1111;'pageid']&#125;')
		GROUP BY	tbllocation.ID 
		ORDER BY	tbllocation.position ASC, tblobject.name ASC