Surveys Readability Query: perform cross 6 table (joined or not) select, but constraining records to first table

intellisource
intellisource used Ask the Experts™
on
hi everybody,

i need to perform a selection similar to the following:

SELECT 
	C.text_ConsumerName AS `Consumer Name`, 
	SQ.smallint_SortOrder AS `Question Order`, 
	SQ.text_QuestionContent AS `Question Content`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		SO.smallint_SortOrder
	) AS `Option Order`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.text_SupplierName, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SR.text_ConsumerText, 
			SO.text_OptionContent
		)
	) AS `Option Content` 
FROM 
	24_surveyresponse SR, 
	21_consumersurveys CS, 
	19_consumers C, 
	22_surveyquestions SQ, 
	23_surveyoptions SO, 
	5_suppliers S 
WHERE 
	SR.bigint_ConsumerSurveyID = CS.bigint_ConsumerSurveysID AND 
	CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
	CS.bigint_SurveyType = SQ.bigint_TypeID AND 
	SQ.bigint_QuestionID = SO.bigint_QuestionID AND 
	SR.bigint_ResponseID = IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		SO.bigint_OptionID
	) AND 
	SR.bigint_ConsumerSurveyID = 1 
GROUP BY 
		`Consumer Name`, 
		`Question Order`, 
		`Question Content`, 
		`Option Order`, 
		`Option Content` 
ORDER BY 
	`Question Order` ASC,
	`Option Order` ASC;

Open in new window


which also gets the same result as a LEFT JOIN:

SELECT 
	C.text_ConsumerName AS `Consumer Name`, 
	SQ.smallint_SortOrder AS `Question Order`, 
	SQ.text_QuestionContent AS `Question Content`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		SO.smallint_SortOrder
	) AS `Option Order`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.text_SupplierName, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SR.text_ConsumerText, 
			SO.text_OptionContent
		)
	) AS `Option Content` 
FROM 
	24_surveyresponse SR 
LEFT JOIN (
		21_consumersurveys CS, 
		19_consumers C, 
		22_surveyquestions SQ, 
		23_surveyoptions SO, 
		5_suppliers S 
	) ON (
		SR.bigint_ConsumerSurveyID = CS.bigint_ConsumerSurveysID AND 
		CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
		CS.bigint_SurveyType = SQ.bigint_TypeID AND 
		SQ.bigint_QuestionID = SO.bigint_QuestionID AND 
		SR.bigint_ResponseID = IF(
			SO.tinyint_OptionType > 3, 
			S.bigint_SupplierID, 
			SO.bigint_OptionID
		)
	)
WHERE 
	SR.bigint_ConsumerSurveyID = 1 
GROUP BY 
		`Consumer Name`, 
		`Question Order`, 
		`Question Content`, 
		`Option Order`, 
		`Option Content` 
ORDER BY 
	`Question Order` ASC,
	`Option Order` ASC;

Open in new window


but where the previous query returns the 22 records in sheet Readable Query of the attached XLSX document.

however there are only 12 in the saved survey response rows (24_surveyresponse in the XLSX) from the dynamic database driven web form (screenshot is attached, and the form can be viewed here, firefox 4 recommended).

how would i be able to do a selection similar to the above, which only returns 12 records?
the other associated tables mentioned below are also sheets in the XLSX:


21_consumersurveys
19_consumers
22_surveyquestions
23_surveyoptions
5_suppliers

Some of the tables have been truncated to display only the required data selected, there are 3,279 rows if i do not constrain the data by grouping the temporary fields.

24_surveyresponse.bigint_ResponseID contains either an option id (where 24_surveyresponse.text_ConsumerText is blank there is no text input field bound to the radio/checkbox button), or a supplier id (for radio/checkbox supplier listings, 24_surveyresponse.text_ConsumerText is blank too because the supplier id is saved in 24_surveyresponse.bigint_ResponseID).

the tables are in an excel workbook for easier viewing; formatting and aligning text on any forum is the pits!
basically, how can i select only 12 records from the available data.

constraining suppliers by another WHERE option returned 12 records but most of the fields are empty (Broken Query in the XLSX)

SELECT 
	C.text_ConsumerName AS `Consumer Name`, 
	SQ.smallint_SortOrder AS `Question Order`, 
	SQ.text_QuestionContent AS `Question Content`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SO.smallint_SortOrder, 
			SR.bigint_ResponseID
		)
	) AS `Option Order`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.text_SupplierName, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SR.text_ConsumerText, 
			SO.text_OptionContent
		)
	) AS `Option Content` 
FROM 
	24_surveyresponse SR
LEFT JOIN (
		21_consumersurveys CS, 
		19_consumers C, 
		22_surveyquestions SQ, 
		23_surveyoptions SO, 
		27_leadssent LS, 
		13_prospectleadsent PS, 
		5_suppliers S
	) ON (
		SR.bigint_ConsumerSurveyID = CS.bigint_ConsumerSurveysID AND 
		CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
		CS.bigint_SurveyType = SQ.bigint_TypeID AND 
		CS.bigint_LeadID = LS.bigint_LeadID AND 
		CS.bigint_ReferenceID = PS.bigint_ProspectID AND 
		SQ.bigint_QuestionID = SO.bigint_QuestionID AND 
		LS.bigint_SupplierID = S.bigint_SupplierID AND 
		PS.bigint_SupplierID = S.bigint_SupplierID AND 
		IF(
			SO.tinyint_OptionType > 3, 
			(
				SR.bigint_ResponseID = LS.bigint_SupplierID OR 
				SR.bigint_ResponseID = PS.bigint_SupplierID
			), 
			SR.bigint_ResponseID = SO.bigint_OptionID
		)
	) 
WHERE 
	SR.bigint_ConsumerSurveyID = 1 
GROUP BY 
		`Consumer Name`, 
		`Question Order`, 
		`Question Content`, 
		`Option Order`, 
		`Option Content` 
ORDER BY 
	`Question Order` ASC,
	`Option Order` ASC;

Open in new window


not sure if there is an easier way to go about it, but i seem to have gotten lost between the start of the query and the finish. please help!

Sincerely,

Pierre "Greywacke" du Toit

PS:

then again - if this issue somehow cannot be resolved with the current form, in the database selection, then perhaps i should simplify the query: save either the option / checkbox texts selected, or supplier names in the blank 24_surveyresponse.text_ConsumerText entries.

the form is created with the following php function:

function getquestions($surveytype,$consumerid,$leadid=0,$refid=0) {
	global $conn;
	$sql = "SELECT * 
			FROM 
				22_surveyquestions 
			LEFT JOIN 
				23_surveyoptions 
			ON (
				22_surveyquestions.bigint_QuestionID = 23_surveyoptions.bigint_QuestionID
			) 
			WHERE 
				22_surveyquestions.bigint_TypeID = ".$surveytype." 
			ORDER BY 
				22_surveyquestions.smallint_SortOrder ASC, 
				23_surveyoptions.smallint_SortOrder ASC;";
	$result = mysql_query_errors($sql, $conn , __FILE__ , __LINE__ );
	$html = "<form id=\"form_survey\" name=\"form_survey\" method=\"post\" accept-charset=\"UTF-8\" action=\"survey_handler.php\" onsubmit=\"return valform(this);\">".
			"<input name=\"consumersurveyid\" id=\"consumersurveyid\" type=\"hidden\" value=\"".$GLOBALS["q"]."\" />".
			"<input name=\"includepath\" id=\"includepath\" type=\"hidden\" value=\"".$GLOBALS["p"]."\" />\n";
	$q = array();
	$q[0] = "";
	$q[1] = "";
	$shtml = "";
	$i = 0;
	if ($result) {
		while ($row = mysql_fetch_array($result)) {
			$q[1] = $row["text_QuestionContent"];
			if ($q[0] != $q[1]) {
				$i++;
				$n = 0;
				if ($q[0] != "") {
					$html .= "	</p>\n";
				}
				$q[0] = $q[1];
				$html .= "	<p><label for=\"radio_answer_".$i."_1\"><strong>".$i."). ".$q[1]."</strong></label></p>\n".
						 "	<p>\n";
				$shtml = "";
			}
			$n++;
			$a = xmlentities($row["text_OptionContent"]);
			$sid = 0;
			switch ($row["tinyint_OptionType"]) {
				case 1:		// radio select + text input
					$html .= "	&nbsp; <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" onfocus=\"document.form_survey.text_".$row["bigint_OptionID"].".focus();\" />".
							 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."<br /> &nbsp; ".
							 "<input type=\"text\" name=\"text_".$row["bigint_OptionID"]."\" id=\"text_".$row["bigint_OptionID"]."\" value=\"\" /></label><br />\n";
					break;
				case 2:		// checkbox
					$html .= "	&nbsp; <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"check_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" />".
							 "<label for=\"check_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
					break;
				case 3:		// checkbox + text input
					$html .= "	&nbsp; <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"check_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" onfocus=\"document.form_survey.text_".$row["bigint_OptionID"].".focus();\" />".
							 "<label for=\"check_".$i."_".$n."\" >&nbsp; ".$a."<br /> &nbsp; ".
							 "<input type=\"text\" name=\"text_".$row["bigint_OptionID"]."\" id=\"text_".$row["bigint_OptionID"]."\" value=\"\" /></label><br />\n";
					break;
				case 4:		// supplier names listing radio
					$sql0 = "SELECT 
								5_suppliers.bigint_SupplierID, 
								5_suppliers.text_SupplierName 
							FROM 
								5_suppliers 
							LEFT JOIN (
								27_leadssent, 
								13_prospectleadsent
							) ON (
								5_suppliers.bigint_SupplierID = 27_leadssent.bigint_SupplierID AND 
								5_suppliers.bigint_SupplierID = 13_prospectleadsent.bigint_SupplierID
							) 
							WHERE 
								27_leadssent.bigint_LeadID = ".$leadid." OR 
								13_prospectleadsent.bigint_ProspectID = ".$refid." 
							GROUP BY 
								5_suppliers.text_SupplierName;";
					$result0 = mysql_query_errors($sql0, $conn , __FILE__ , __LINE__ );
					if ($result0) {
						while ($row0 = mysql_fetch_array($result0)) {
							$a = $row0["text_SupplierName"];
							$html .= "	&nbsp; <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row0["bigint_SupplierID"]."\" />".
									 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
							if ($n<mysql_num_rows($result0)) $n++;
						}
					}
					break;
				case 5:		// supplier names listing checkbox
					$sql0 = "SELECT 
								5_suppliers.bigint_SupplierID, 
								5_suppliers.text_SupplierName 
							FROM 
								5_suppliers 
							LEFT JOIN (
								27_leadssent, 
								13_prospectleadsent
							) ON (
								5_suppliers.bigint_SupplierID = 27_leadssent.bigint_SupplierID AND 
								5_suppliers.bigint_SupplierID = 13_prospectleadsent.bigint_SupplierID
							) 
							WHERE 
								27_leadssent.bigint_LeadID = ".$leadid." OR 
								13_prospectleadsent.bigint_ProspectID = ".$refid." 
							GROUP BY 
								5_suppliers.text_SupplierName;";
					$result0 = mysql_query_errors($sql0, $conn , __FILE__ , __LINE__ );
					if ($result0) {
						while ($row0 = mysql_fetch_array($result0)) {
							$a = $row0["text_SupplierName"];
							$html .= "	&nbsp; <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"radio_".$i."_".$n."\" value=\"".$row0["bigint_SupplierID"]."\" />".
									 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
							if ($n<mysql_num_rows($result0)) $n++;
						}
					}
					break;
				default:	// radio select
					$html .= "	&nbsp; <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" />".
							 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
			}
		}
		mysql_free_result($result);
	}
	$html .= "	</p>\n";
	$html .= "	<p><input type=\"submit\" value=\"Submit!\" /><input type=\"reset\" value=\"Reset...\" /></p>\n";
	$html .= "</form>";
	return array($i, $html);
}

Open in new window


the data could then be sent via hidden fields. or picked up from the database in the handler somehow... but why send a bunch of text back to the database, when it's already in it? 0o
Test-Survey-One.jpg
Survey-Readability-Queries---Tab.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ok seeing as i did not receive a solution, i looked into it further...

i then realised that the query has no way to validate wether it is an option id or a survey id in the response field. so i modified the form, had it send option id ;. supplier id if one is provided - the readability query is alot shorter now and it works perfectly! XD

i also added an autonumber index field, and a supplier id field to the response table, so the code could distinguish between option id's and supplier id's. the supplier names are also sent in a hidden field, to cut out selecting from the suppliers table.

SELECT 
	SR.bigint_OrderID AS SORT, 
	C.text_ConsumerName AS CONSUMER, 
	C.`text_ConsumerE-Mail` AS EMAIL, 
	SQ.text_QuestionContent AS QUESTION, 
	IF(
		SR.text_ConsumerText != "", 
		SR.text_ConsumerText, 
		SO.text_OptionContent 
	) AS ANSWER  
FROM 
	24_surveyresponse SR 
LEFT JOIN (
		19_consumers C, 
		21_consumersurveys CS, 
		22_surveyquestions SQ, 
		23_surveyoptions SO 
	) ON (
		SR.bigint_ConsumerSurveyID = CS.bigint_ConsumerSurveysID AND 
		CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
		CS.bigint_SurveyType = SQ.bigint_TypeID AND 
		SQ.bigint_QuestionID = SO.bigint_QuestionID AND 
		SR.bigint_ResponseID = SO.bigint_OptionID
	) 
WHERE 
	SR.bigint_ConsumerSurveyID = 1 
GROUP BY 
	SR.bigint_OrderID 
ORDER BY 
	SORT ASC;

Open in new window

Author

Commented:
ok seeing as i did not receive a solution, i looked into it further

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial