Surveys Readability Query: perform cross 6 table (joined or not) select, but constraining records to first table
hi everybody,
i need to perform a selection similar to the following:
which also gets the same result as a LEFT JOIN:
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_R
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)
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_Con
the form is created with the following php function:
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
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;
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;
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_R
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;
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_Con
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 .= " <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."\" > ".$a."<br /> ".
"<input type=\"text\" name=\"text_".$row["bigint_OptionID"]."\" id=\"text_".$row["bigint_OptionID"]."\" value=\"\" /></label><br />\n";
break;
case 2: // checkbox
$html .= " <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"check_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" />".
"<label for=\"check_".$i."_".$n."\" > ".$a."</label><br />\n";
break;
case 3: // checkbox + text input
$html .= " <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."\" > ".$a."<br /> ".
"<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 .= " <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row0["bigint_SupplierID"]."\" />".
"<label for=\"radio_".$i."_".$n."\" > ".$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 .= " <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"radio_".$i."_".$n."\" value=\"".$row0["bigint_SupplierID"]."\" />".
"<label for=\"radio_".$i."_".$n."\" > ".$a."</label><br />\n";
if ($n<mysql_num_rows($result0)) $n++;
}
}
break;
default: // radio select
$html .= " <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" />".
"<label for=\"radio_".$i."_".$n."\" > ".$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);
}
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
Do more with
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.
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;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial