Can't update database using PHP and JavaScript

Question

I'm trying to build a very simple "budget" website solely for practice, but I can't make this site update my database.

Basically I have a database 'budgetdb' running in XAMPP with MySQL. I've got 1 table where the structure looks like this:

I've got two files, 'index.html' and 'handleUserInput.php'.

Index.html:

<!DOCTYPE html>
<html>
<body>
    <input type="text" id="description">
    <input type="number" id="budgetin">
    <input type="number" id="budgetout">
    <button type="button" onclick="updateDB()">Add to database</button>

    <script>

        function updateDB() {
            var description = document.getElementById('description').value;
            var budgetin = document.getElementById('budgetin').value;
            var budgetout = document.getElementById('budgetout').value;

            var xmlhttp = new XMLHttpRequest();
            var url = "handleUserInput.php?description='" + description + "'&budgetin='" + budgetin + "'&budgetout='" + budgetout + "'";

            xmlhttp.onreadystatechange = function() {
                if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                    alert('Variables sent to server!');
                }
            }
            xmlhttp.open("POST", url);
            xmlhttp.send();
        }

    </script>
</body>
</html>

handleUserInput.php:

<?php

    $host = "localhost";
    $username = "root";
    $password = "";
    $dbname = "budgetdb"

    mysql_connect($host, $username, $password;
    mysql_select_db($dbname);

    $description = $_POST['description'];
    $budgetin = $_POST['budgetin'];
    $budgetout = $_POST['budgetout'];

    $query = 'INSERT into budget VALUES ($description, $budgetin, $budgetout)';

    mysql_query($query)

    ?>

The message prompt is displayed, but no data is shown in the database. Any clue on what I am doing wrong here?

UPDATE chrome error:

Notice: Undefined index: description in /Applications/XAMPP/xamppfiles/htdocs/handleUserInput.php on line 13

Notice: Undefined index: budgetin in /Applications/XAMPP/xamppfiles/htdocs/handleUserInput.php on line 14

Notice: Undefined index: budgetout in /Applications/XAMPP/xamppfiles/htdocs/handleUserInput.php on line 15

`"INSERT into budget VALUES ('$description', '$budgetin', '$budgetout')"` Check your quotes in your PHP string and MySQL query. — gen_Eric, Oct 16 '15 at 18:07
P.S. This code is *very* unsafe! Stop using `mysql_query` and learn about prepared statements and MySQLi (or PDO). http://www.bobby-tables.com/php.html — gen_Eric, Oct 16 '15 at 18:08
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Oct 16 '15 at 18:34
If you can, you should [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 not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Oct 16 '15 at 18:35

Barmar · Accepted Answer · 2015-10-16T19:12:56.227

1

Other answers have shown the problem with the way you write the query, it. should be, with quotes around each of the values.

$query= "INSERT into budget VALUES('$description',$budgetin,$budgetout)";

But you also have a problem with the way you create the URL. Quotes shouldn't be put around query parameters, and you should use encodeURIComponent to ensure that special characters are escaped properly.

var url = "handleUserInput.php?description=" + encodeURIComponent(description) + "&budgetin=" + encodeURIComponent(budgetin) + "&budgetout=" + encodeURIComponent(budgetout);

And to prevent SQL injection problems, you need to escape the strings before you use them as SQL parameters. And since you're sending the parameters in the URL, rather than in the POST data, you need to get them from $_GET, not $_POST.

$description = mysql_real_escape_string($_GET['description']);

Although if you're first learning PHP now, you should use PDO or mysqli, instead of the obsolete mysql extension, and use prepared statements instead of string substitution.

Change the line that performs the query to:

mysql_query($query) or die(mysql_error());

If there's a problem performing the query, this will display the error message.

edited Oct 16 '15 at 19:12

answered Oct 16 '15 at 18:27

Barmar

741,623
53
500
612

I see. Changed the quotes now, and it'll definitely be looking into SQL injection - but my example still doesn't work – Erik Oct 16 '15 at 18:43
Add the error checking that I just put into the answer. – Barmar Oct 16 '15 at 18:46
Did that now, but it doesn't react kind off. After I press the button, it displays the javascript prompt and doesn't do anything else – Erik Oct 16 '15 at 18:57
Go into Developer Tools, go to the Network tab, select the URL for the AJAX request, and look at the Response to see if there's an error message there. – Barmar Oct 16 '15 at 18:59
Went there now and found the error, but the fix revealed three new errors whereas it cannot find the POST keys. Looking through the request doesn't show any obvious glitches to me, though. – Erik Oct 16 '15 at 19:11
URL parameters are in `$_GET`, not `$_POST`. – Barmar Oct 16 '15 at 19:11
Sorry, I can't think of any reason. – Barmar Oct 16 '15 at 19:18
Figured it out now, I didn't specify the name of the database in the connection. Works now. Thank you! – Erik Oct 16 '15 at 19:19
You mean the line `$dbname = "budgetdb";` wasn't actually in the script, like it was in the question? – Barmar Oct 16 '15 at 19:20
I changed from mysql_connect to $conn = new mysqli, and forgot to include $dbname in the new connection – Erik Oct 16 '15 at 19:22

Tamil Selvan C · Answer 2 · 2015-10-16T18:22:04.637

0

Use

$query = "INSERT into budget VALUES ('$description', $budgetin, $budgetout)";

description is string type in database so you close with single quote or double quote in PHP String.

Always consider safety. ReWrite a code as (somewhat safety):

$description = mysql_real_escape_string( $_POST['description'] );
$budgetin = intval( $_POST['budgetin'] );
$budgetout = intval( $_POST['budgetout'] );

$query = 'INSERT into budget VALUES ("$description", $budgetin, $budgetout)';

Note: Don't use mysql_* function. It is deprecated in future versions. Use MySQLi or PDO

edited Oct 16 '15 at 18:22

answered Oct 16 '15 at 18:06

Tamil Selvan C

19,913
12
49
70

You're missing a few quotes and/or `.` operators. – gen_Eric Oct 16 '15 at 18:09

score 0 · Answer 3 · answered Oct 16 '15 at 18:14

The quotes in your query are incorrect. Should be (note outer double quotes and inner single quotes):

$query= "INSERT into budget VALUES('$description',$budgetin,$budgetout)";

However, be aware this has opened you up to a SQL injection where description is

'); DROP budget;

Also, your description may contain punctuation and spaces, which may be messing up your url. You are sending a POST anyway, so all the data should be in the body of the request not the url.

Guilherme Ferreira · Answer 4 · 2015-10-16T19:27:47.847

You are sending your values as GET parameters not POST parameters.

handleUserInput.php?description='" + description + "'&budgetin='" + budgetin + "'&budgetout='" + budgetout + "'"

Everything you pass to PHP thru url will fall into $_GET variable. Not only PHP bu every server side language will behave the same, once it is a standard GET means URL parameters and POST is Request Payload

You can change $_POST to $_GET or add your data do xmlhttprequest object

<!DOCTYPE html>
<html>
<body>
    <input type="text" id="description">
    <input type="number" id="budgetin">
    <input type="number" id="budgetout">
    <button type="button" onclick="updateDB()">Add to database</button>

    <script>

        function updateDB() {
            var description = document.getElementById('description').value;
            var budgetin = document.getElementById('budgetin').value;
            var budgetout = document.getElementById('budgetout').value;
            var params = "description=" + description + "&budgetin=" + budgetin + "&budgetout=" + budgetout;
            var xmlhttp = new XMLHttpRequest();
            xmlhttp .setRequestHeader("Content-type", "application/x-www-form-urlencoded");
            xmlhttp .setRequestHeader("Content-length", params.length);
            xmlhttp .setRequestHeader("Connection", "close");

            var url = "handleUserInput.php?";

            xmlhttp.onreadystatechange = function() {
                if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                    alert('Variables sent to server!');
                }
            }
            xmlhttp.open("POST", url,true);
            xmlhttp.send(params);
        }

    </script>
</body>
</html>

Sajin Andrei · Answer 5 · 2015-10-16T18:58:20.157

In order to minize your code use Jquery library instead of simple JS code

<html>
<head>
<script src="http://code.jquery.com/jquery-1.7.1.min.js"></script>
</head>
<body>
    <input type="text" id="description">
    <input type="number" id="budgetin">
    <input type="number" id="budgetout">
    <button type="button" onclick="updateDB()">Add to database</button>

    <script>

        function updateDB() {
            var description = $('#description');
            var budgetin = $('#budgetin');
            var budgetout = $('#budgetout');

            $.post( "handleUserInput.php", { "description": description, "budgetin": budgetin, "budgetout": budgetout }, function( data ) {
              alert( "Variables sent to server!" );
            } );

        }

    </script>
</body>
</html>

Use mysql_real_escape_string in order to avoid SQL injection into your application. Initial problem was caused because php variables wasn't correctly added in the string of SQL query, for example :

$input = 'aaa';
echo 'text $input';

this code will output

text $input

but this one

$input = 'aaa';
echo "text $input";

or this one

$input = 'aaa';
echo "text ".$input.";

will output

text aaa

Please check the code bellow

<?php

$host = "localhost";
$username = "root";
$password = "";
$dbname = "budgetdb"

mysql_connect($host, $username, $password;
mysql_select_db($dbname);

$description = $_POST['description'];
$budgetin = $_POST['budgetin'];
$budgetout = $_POST['budgetout'];

$description = mysql_real_escape_string($description);
$budgetin= mysql_real_escape_string($budgetin);
$budgetout= mysql_real_escape_string($budgetout);
$query = "INSERT into budget VALUES ('".$description."', ".$budgetin.", ".$budgetout.")";

mysql_query($query)

?>

Why should the OP try this? A good answer will always have an explanation of what was done and why it was done that way, not only for the OP but for future visitors to SO. — Jay Blanchard, Oct 16 '15 at 18:35

NASEEM FASAL · Answer 6 · 2016-01-03T10:11:21.433

-2

Use single inverted comma with php variables like below.

$query = "INSERT into budget VALUES ('$description', $budgetin, $budgetout)";

edited Jan 03 '16 at 10:11

answered Oct 16 '15 at 18:15

NASEEM FASAL

429
4
13

You need double quotes around the query, so that variables will be substituted. – Barmar Oct 16 '15 at 18:24
This won't even parse. It contains multiple syntax errors. – nobody Nov 04 '15 at 02:15

Can't update database using PHP and JavaScript

6 Answers6