Solved

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

Posted on 2011-03-15
1
359 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now