Unable to add data to mysql database using php & javascript after adding an auto_increment id column

Question

I am creating a simple form using php, mysql and javascript. The idea is to have the user input the text, then the text is saved to the database, at the same time the latest text that the user have input returns from the database and shows on the same page without refreshing the page. The submitting function worked before I manually added the id column to the table. Please take a look of my codes and help me fix it, thank you.

submit.php ↓

<?php

$conn = mysql_connect('localhost','root','') or die (mysql_error);
$db = mysql_select_db('qinglish_nihaoemilie') or die (mysql_error);

$lastname = $_POST['lastname'];
$table = 'emilieinfo_lastname';

mysql_query("INSERT INTO $table VALUES('$lastname',NULL)");

?>

return.php ↓

<?php

$conn = mysql_connect('localhost','root','');
$db = mysql_select_db('qinglish_nihaoemilie');

$res = mysql_query ("SELECT * FROM emilieinfo_lastname ORDER BY id DESC LIMIT 1");
$result = array();

while ($row = mysql_fetch_array($res)) {
    array_push($result, array('lastname' => $row[0]));
}

echo json_encode(array('result' => $result));
?>

Javascript ↓

$('.lastname-container i').click(function(){
    var lastnameInput = $('.lastname').val();
    var lastnameInputLength = lastnameInput.length;
    if (lastnameInputLength > 0) {
        $('.lastname').hide();
        $(this).hide();
        var data = {
        lastname: $('.lastname').val(),
        };
        $.ajax({
            type: "POST",
            url: "../php/submitinfo-lastname.php",
            data: data,
        });

        $(this).parent().parent().find('p .fa-pencil-square-o').addClass('inline-table');
        $(this).parent().parent().find('p .fa-pencil-square-o').show();

        // $.getJSON("../php/returninfo-lastname.php", function(result){
        //  $.each(result, function(i, lastname){
        //      $(this).parent().parent().find('span').append(lastname + '');
        //  });
        // });

        // $.getJSON("../php/returninfo-lastname.php", function(returndata){
        //  $.each(returndata.result, function(){
        //      $(this).parent().parent().find('span').append("a"+this['lastname']);
        //  });
        // });
    };

    if (lastnameInputLength == 0) {
        $('.lastname').addClass('red-underline');
        $(this).addClass('red-color');
    };  
});

Database screenshot ↓

database screenshot

HTML ↓

<h2>
    <p>last name: 
        <span></span> 
        <i style="position:static; display: none;padding-left:5px;" class="fa fa-pencil-square-o"></i>
    </p>
    <div class="lastname-container">
        <input class="lastname" name="lastname" type="text">
        <i class="fa fa-check-square-o"></i>
    </div>
</h2>

[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Feb 15 '16 at 22:56
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Feb 15 '16 at 22:57
I just followed an old tutorial, thank you i'll look into that. — Qing, Feb 15 '16 at 23:01
Definitely look into that stuff - very important - but in the meantime - what if you try taking the ", NULL" out of the query (so it's just $lastname being inserted)? Your database should automatically assign the next ID so long as the field is auto-increment. — , Feb 15 '16 at 23:11
Hi Mark thanks for the reply, I removed the NULL and still can't insert data to the database, however if i deleted the id column in the database, the data can be successfully inserted. — Qing, Feb 15 '16 at 23:16

score 0 · Answer 1 · answered Feb 15 '16 at 23:48

0

Should your query not look something like this

mysql_query('INSERT INTO '.$table.' VALUES("'.$lastname.'")');

Also maybe try running your query in the mySQL SQL window to make sure your query actually works

answered Feb 15 '16 at 23:48

Andy Wooller

3
2

Hi Andy thanks for the reply, I tried and it still doesn't work. – Qing Feb 16 '16 at 00:26
This is vulnerable to SQL injection attacks. – Pang Feb 16 '16 at 01:15

score 0 · Answer 2 · answered Feb 16 '16 at 00:47

0

check this query

mysql_query("INSERT INTO $table (lastname,id) VALUES('$lastname',NULL)");

answered Feb 16 '16 at 00:47

Bart

1,268
2
12
14

This is vulnerable to SQL injection attacks. – Pang Feb 16 '16 at 01:15
Hi Bart thanks for the reply, I tried this code but it does not work, I'm thinking the problem might be from the javascript since only theres no id value posted. – Qing Feb 16 '16 at 01:34

score 0 · Answer 3 · answered Feb 16 '16 at 01:07

0

mysql_query('INSERT INTO `emilieinfo_lastname`(`lastname`) VALUES('{$lastname}'));

Just remove id and NULL value since it is an AUTO_INCREMENT field you can just remove that when you INSERT you data.

answered Feb 16 '16 at 01:07

Matt Magallo

328
1
20

This is vulnerable to SQL injection attacks. – Pang Feb 16 '16 at 01:15
Yes, but as i can see he's still using the classic `mysql_query` if he's in `PDO` or `mylsqi` i can use named variable as you can see i'm just been in line with his question and i don't wanna complicate the process that he is using as of now, thanks BTW for noticing @Pang i can also see that. :) – Matt Magallo Feb 16 '16 at 01:20
Hi Matt thanks for the reply, I tried your code unfortunately its not working, I'm thinking the problem might be from the javascript since only theres no id value posted. – Qing Feb 16 '16 at 01:33

Unable to add data to mysql database using php & javascript after adding an auto_increment id column

3 Answers3