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;
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;
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;
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);
}
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE