<?php
$host = "localhost";
$user = "user";
$password = "pass";
$schema = "schema";

if (isset($_POST['get_opros_list'])) {
    // Create connection
    $conn = new mysqli($host, $user, $password, $schema);
    $conn->set_charset("utf8");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
        echo "Connection failed: " . $conn->connect_error;
    }
    $quest = 'question';
    $orderby = 'ASC';
    $sql = "SELECT * FROM modx_polls_logs
            WHERE ipaddress = '" . $_POST['get_opros_list'] . "'
            ORDER BY $quest $orderby";
    $result = $conn->query($sql);
    $rows = array();
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    } else {
        echo '0 results';
    }
    $conn->close();
    echo json_encode($rows);
} elseif (isset($_POST['get_questions_list'])) {
    // Create connection
    $conn = new mysqli($host, $user, $password, $schema);
    $conn->set_charset("utf8");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = "SELECT * FROM modx_polls_questions
            WHERE id NOT IN (" . $_POST['get_questions_list'] . ")
            AND category = " . $_POST['category_id'] . "
            AND hide = 0
            ORDER BY publishdate ASC";
    $result = $conn->query($sql);
    $rows = array();
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    } else {
        echo '0 results';
    }
    $conn->close();
    echo json_encode($rows);
} elseif (isset($_POST['get_answers_list'])) {
    // Create connection
    $conn = new mysqli($host, $user, $password, $schema);
    $conn->set_charset("utf8");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = "SELECT
                mpa.id AS answer_id, mpa.answer AS answer_text, (
                  SELECT COUNT(id) FROM modx_polls_logs WHERE answer = mpa.id
                ) AS answer_votes,
                mpq.id AS question_id, mpq.question AS question_text
            FROM modx_polls_answers mpa
                LEFT JOIN modx_polls_questions mpq ON mpa.question = mpq.id
            WHERE mpq.id = " . $_POST['get_answers_list'] . "
            ORDER BY mpa.sort_order ASC";
    $result = $conn->query($sql);
    $rows = array();
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    } else {
        echo '0=' . $_POST['get_answers_list'];
    }
    $conn->close();
    echo json_encode($rows);
} elseif (isset($_POST['submit_answer'])) {
    // Create connection
    $conn = new mysqli($host, $user, $password, $schema);
    $conn->set_charset("utf8");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = "INSERT INTO modx_polls_logs
        (id, question, answer, ipaddress, logdate)
        VALUES
        (NULL, " .
        $_POST['question_id'] . ", " .
        $_POST['submit_answer'] . ", '" .
        $_POST['user_ip'] . "', '" .
        $_POST['cur_date'] . "')";
    $result = $conn->query($sql);
    $conn->close();
    echo 'success';
} elseif (isset($_POST['get_answered_questions_list'])) {
    // Create connection
    $conn = new mysqli($host, $user, $password, $schema);
    $conn->set_charset("utf8");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = "SELECT * FROM modx_polls_questions
            WHERE id IN (" . $_POST['get_answered_questions_list'] . ")
            AND category = " . $_POST['category_id'] . "
            AND hide = 0
            ORDER BY publishdate ASC";
    $result = $conn->query($sql);
    $rows = array();
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    } else {
        echo '0 results';
    }
    $conn->close();
    echo json_encode($rows);
} elseif (isset($_POST['get_question_by_id'])) {
    // Create connection
    $conn = new mysqli($host, $user, $password, $schema);
    $conn->set_charset("utf8");
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $sql = "SELECT * FROM modx_polls_questions
            WHERE id IN (" . $_POST['get_question_by_id'] . ")
            AND category = " . $_POST['category_id'] . "
            AND hide = 0
            ORDER BY publishdate ASC";
    $result = $conn->query($sql);
    $rows = array();
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    } else {
        echo '0 results';
    }
    $conn->close();
    echo json_encode($rows);
}
?>