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

    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

3

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'
                    }),

answered Jan 27, 2011 at 17:32

Maximilian Ehlers

3281 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NiseNise Over a year ago

oh that is a comment copied in by mistake, will try your answer

Alfred · Accepted Answer · 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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related