Solved

what is wrong with these dynamic mysql selects that they do not return any results?

Posted on 2011-03-15
1
365 Views
Last Modified: 2012-05-11
hi there.
i have a form that upon selecting attributes from dropdowns, requests allowed attributes for the next dropdown. thus i have a table of attributes, with multiple parent id's and a full ancestral tree in a string representation of a two-dimentional array (see attached csv with the first 3 levels of attributes). the first level of attributes (Make) only has 0 as a parent id, thus to load them on loading the form before selections are made. This works. It loads the attributes that have the selected attribute as a parent. thus the first and second tiers load:
SELECT * FROM 3_serviceattributes WHERE bigint_AttributeServiceID = 1 AND bigint_AttributeParentID = "0" ORDER BY text_AttributeDescription ASC, text_AttributeValue ASC;
SELECT * FROM 3_serviceattributes WHERE bigint_AttributeServiceID = 1 AND CONCAT(",",bigint_AttributeParentID,",") LIKE CONCAT("%,",CONCAT_WS(";",(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = "Make" AND text_AttributeValue = "GMC")),",%") ORDER BY text_AttributeDescription ASC, text_AttributeValue ASC;

Open in new window

the problem comes into play with loading the third tier and onwards.
here is the php code that handles this, in the xml generator:
function getrecords($data,$set) {
	global $conn;
	$search = ($set > 0)?
				"CONCAT(\",\",bigint_AttributeParentID,\",\") LIKE CONCAT(\"%,\",CONCAT_WS(\";\",(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = \"".$data[0]."\" AND text_AttributeValue = \"".$data[1]
				:
				"bigint_AttributeParentID = \"".$data."\"";
	switch ($set) {
		case 1:			// 1
			$search .= "\")),\",%\")";
			break;
		case 2:			// 2
			$search .= "\"),(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = \"".$data[2]."\" AND text_AttributeValue = \"".$data[3]."\")),\",%\")";
			break;
		case 3:			// 3
			$search .= "\"),(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = \"".$data[2]."\" AND text_AttributeValue = \"".$data[3]."\"),".
					   "(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = \"".$data[4]."\" AND text_AttributeValue = \"".$data[5]."\")),\",%\")";
			break;
		case 4:			// 4
			$search .= "\"),(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = \"".$data[2]."\" AND text_AttributeValue = \"".$data[3]."\"),".
					   "(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = \"".$data[4]."\" AND text_AttributeValue = \"".$data[5]."\"),".
					   "(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = \"".$data[6]."\" AND text_AttributeValue = \"".$data[7]."\")),\",%\")";
			break;
		default:	// 0
			// do nothing
	}
	$sql = "SELECT * FROM 3_serviceattributes WHERE bigint_AttributeServiceID = 1 AND ".$search." ORDER BY text_AttributeDescription ASC, text_AttributeValue ASC;";
	$result = mysql_query_errors($sql , $conn , __FILE__ , __LINE__ , true );
	if ($result) {
		while ($row = mysql_fetch_array($result)) {
			echo "	<attrib name=\"".$row["text_AttributeDescription"]."\" value=\"".$row["text_AttributeValue"]."\" />\n";
		}
	}
}

Open in new window

this code creates and executes a third tier sql query as follows:
SELECT * FROM 3_serviceattributes WHERE bigint_AttributeServiceID = 1 AND CONCAT(",",bigint_AttributeParentID,",") LIKE CONCAT("%,",CONCAT_WS(";",(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = "Make" AND text_AttributeValue = "FORD"),(SELECT bigint_AttributeID FROM 3_serviceattributes WHERE text_AttributeDescription = "Model" AND text_AttributeValue = "SIERRA3500")),",%") ORDER BY text_AttributeDescription ASC, text_AttributeValue ASC;
An empty result set was returned by the query defined in /home/dwtphovu/public_html/truckcapxchange.com/ajax_attribs2.php on line 48

Open in new window

the csv with these first 3 tiers of attributes is attached. there are far more than 3 tiers in total, but how would i get a dynamic mysql select working to do what needs to be done? the values are successfully passed via ajax requests.
hope i have been clear enough - awesome thanks to the one who can solve this!
TCX-SERVICEATTRIBUTES-1.3.csv
0
Comment
Question by:intellisource
1 Comment
 

Accepted Solution

by:
intellisource earned 0 total points
ID: 35136807
ohhhh doh :P
solved it ^^ problem was with only one index used in ajax calling script, i turned that into an array in order to send multiple parent attributes to the php xml generator ;)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question