How to insert form data into MySQL database table with PHP and Ajax?

Question

I am having trouble inserting data into my database using PHP, not sure if I am missing something.

Firstly my code validates the data using ajax method, but the data is not inserted into the database.

Secondly have php validation as a fallback that works and the data is inserted successfully, please see code below

<?php
    $polltitle = $_POST['polltitle'];
    $answerone = $_POST['answerone'];
    $answertwo = $_POST['answertwo'];
    $answerthree = $_POST['answerthree'];
    $activatepoll = $_POST['activatepoll'];

    if(isset($_POST['submit'])){
        if(trim($_POST['polltitle']) == ""){
            $errortitle = "<span class='error'>Please enter a poll question</span>";
            $hasError = true;
        }
        else{
            $polltitle = $_POST['polltitle'];
        }
        if(trim($_POST['answerone']) == ""){
            $erroropt_1 = "<span class='error'>Please choose a response name</span>";
            $hasError = true;
        }
        else{
           $answerone = $_POST['answerone'];
        }

        if(trim($_POST['answertwo']) == ""){
            $erroropt_2 = "<span class='error'>Please choose a response name</span>";
            $hasError = true;
        }
        else{
          $answertwo = $_POST['answertwo'];
        }

        if(!isset($hasError)){

        $sql = "INSERT INTO Poll (pollname, answer1 , answer2, answer3, active) VALUES ('".$polltitle."','".$answerone."','".$answertwo."','".$answerthree."','".$activatepoll."')";
        mysql_query($sql) or die(mysql_error());
        $successmg = "<p>1 record added</p>";
        }
    }

?>

<form method="post" id="postfrm">
                      <h2>Create a new poll question</h2>
                      <fieldset class="dataform" id="insertfrm">
                            <label for="qtitle">Question Title</label><input type="text" name="polltitle" value=""  id="qtitle" />
                            <?php print $errortitle;?>

                            <label for="opt1">Answer 1</label><input type="text" name="answerone" value="" id="opt1" />                      
                            <?php print $erroropt_1;?>

                            <label for="opt2">Answer 2</label><input type="text" name="answertwo"  value="" id="opt2"/>
                            <?php print $erroropt_2;?>

                            <label>Make question active</label><input type="checkbox" name="activatepoll" value="1" id="activepoll"/>
                            <span class="small-txt">If you want the poll to be visible please check the box</span>
                        <input type="submit" name="submit" value="Submit" id="addpoll"/>
                      </fieldset>
                    </form>
                    <?php print $successmg;?>


       $(document).ready(function() {
 $("#postfrm").submit(function(){
        $(".error").hide();
        var hasError = false;       
        var nameVal = $("#qtitle").val();
        var optVal1 = $("#opt1").val();
        var optVal2 = $("#opt2").val();
        var optVal3 = $("#opt3").val();
        var viewpoll = $("#activepoll").val();
        if(nameVal == '') {
            $("#qtitle").after('<span class="error">Please enter a poll question</span>');
            hasError = true;
        }
        if(optVal1 == '') {
            $("#opt1").after('<span class="error">Enter an answer</span>');
            hasError = true;
        }
        if(optVal2 == '') {
            $("#opt2").after('<span class="error">Enter an answer</span>');
            hasError = true;
        }
        if(hasError == false) {
            $(this).hide();
            $.ajax({
                type:"POST",
                url: "validatedata.php",
                data : ({
                    polltitle:nameVal,
                    answerone:optVal1,
                    answertwo:optVal2,
                    answerthree:optVal3,
                    $activatepoll:viewpoll
                }),
                success: function(){
                     alert("worked");
                    },
               error :function(){
                    alert("nope :( ");
               },
               complete : function(){
                   alert("thanks");
               }
                 });
        }

        return false;
    });

});

Maximilian Ehlers · Accepted Answer · 2011-01-27 17:32:17Z

why is there a questionmark before activatepoll?

data : ({
                    polltitle:nameVal,
                    answerone:optVal1,
                    answertwo:optVal2,
                    answerthree:optVal3,
                    $activatepoll:viewpoll
                }),

Further you should send the send the variable $_POST['submit']:

data : ({
                        polltitle:nameVal,
                        answerone:optVal1,
                        answertwo:optVal2,
                        answerthree:optVal3,
                        activatepoll:viewpoll,
                        submit: 'yeahhh'
                    }),

oh that is a comment copied in by mistake, will try your answer — NiseNise, Jan 27 '11 at 23:25

Alfred · Answer 2 · 2011-01-27 23:50:35Z

You should be using PDO(PDO is the future) because your code is very unsafe. It is vulnerable to SQL-injections:

$sql = "INSERT INTO Poll (pollname, answer1 , answer2, answer3, active) VALUES ('".$polltitle."','".$answerone."','".$answertwo."','".$answerthree."','".$activatepoll."')";
        mysql_query($sql) or die(mysql_error());

Your code is also vulnerable to CSRF, XSS just to name a few.

Below I created a funny kev-value store or something. It uses SQLite as datastore, but you could replace it with your SQL server by replacing $db. Or you could just store SQLite-database(kv.sqlite3) in database/ folder.

index.php:

session_start();

/* prevent XSS. */
$_GET   = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
$_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

/* prevent CSRF. */
if (!isset($_SESSION['token'])) {
    $token = md5(uniqid(rand(), TRUE));
    $_SESSION['token'] = md5(uniqid(rand(), TRUE));
} else {
    $token = $_SESSION['token'];
}

/* prevent SQL-injection. */
$db = new PDO('sqlite:database/kv.sqlite3');
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

function createTable($db) {
    $db->exec("CREATE TABLE IF NOT EXISTS kv (id INTEGER PRIMARY KEY, key TEXT NOT NULL UNIQUE, value TEXT NOT NULL)");
}

createTable($db);

if (isset($_POST['token']) && isset($_POST['key']) && isset($_POST['value'])) {
    if ($_POST['token'] != $_SESSION['token']) {
        exit();
    }
    try {
        $stmt = $db->prepare("REPLACE INTO kv (key,value) VALUES (:key,:value)");
        $stmt->execute(array(
            ':key' => $_POST['key'],
            ':value' => $_POST['value']
        ));
        $data['count'] = $stmt->rowCount();
        echo json_encode($data);
    } catch(PDOException $e) {
        /*** echo the sql statement and error message ***/
        echo $sql . '<br />' . $e->getMessage();
    }
    exit();
} else if (isset($_GET['key'])) {
    try {
        $stmt = $db->prepare("SELECT value FROM kv WHERE key = :key");
        $stmt->execute(array(
            ':key' => $_GET['key'],
        ));

        if ($row = $stmt->fetch()) {
            $data['value'] = $row['value'];
        } else {
            $data['error'] = "key not found";
        }
        echo json_encode($data);
    } catch(PDOException $e) {
        /*** echo the sql statement and error message ***/
        echo $sql . '<br />' . $e->getMessage();
    }
    exit();
} else { ?>
<?php } ?>
<!DOCTYPE HTML>
<meta charset="utf-8">
<html>
    <head>
        <meta charset="utf-8">
        <title>Demo stackoverflow.com - 4819699</title>
    </head>
    <body>
        <input type="hidden" id="token" value="<?= $token; ?>" />
        <h1>Set:</h1>
            <label for="set-key">Key:</label><br />
            <input type="text" id="set-key" /><br />

            <label for="set-value">Value:</label><br />
            <input type="text" id="set-value" /><br />

            <button id="set-button">set</button>

        <h1>Get:</h1>
        <label for="get-key">Key:</label><br />
        <input type="text" id="get-key" /><br />
        <button id="get-button">get</button>

        <p id="result"></p>
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.4/jquery.min.js"></script>
        <script type="text/javascript">
            $(document).ready(function() {
                $('#set-button').click(function() {
                    var key = $('#set-key').val();
                    var value = $('#set-value').val();
                    var token = $('#token').val();
                    if (key && value) {
                        $.post(".", { key: key, value: value, token: token }, function(data) {
                            alert(data);
                        });
                        return;
                    }
                    alert('key or value is not provided');
                });

                $('#get-button').click(function() {
                    var key = $('#get-key').val();
                    if (key) {
                        $.get(".", {key: key}, function(data) {
                            $('#result').html(data);
                        });
                        return;
                    }
                    alert('key not provided');

                });
            });
        </script>
    </body>
</html>

Thanks for the information will look into pdo and apply into my code — NiseNise, Jan 28 '11 at 16:02

asked	2 years ago
viewed	8852 times
active	2 years ago

How to insert form data into MySQL database table with PHP and Ajax?

2 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged php jquery mysql ajax or ask your own question.

Linked

How to insert form data into MySQL database table with PHP and Ajax?

2 Answers

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged php jquery mysql ajax or ask your own question.

Linked

Related