Solved

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

Posted on 2011-03-15
1
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
show child records separated by commas 12 38
Duplicated data in GROUP_CONCAT 2 27
Problem with MySQL query - graph 3 28
php hashing methods 3 19
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
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.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

697 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