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);
}
Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.
TRUSTED BY
ASKER