Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-15
1
Medium Priority
?
381 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

636 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