How do I update my database with mysqli and javascript

Question

I have a website which basically is an audioplayer and an integrated lyricviewer on screen, which the user should be able to sync with the music they hear playing. I only have one problem, and that is; How on earth do I, from a javascript function, call a mysqli update statement? When the user clicks a save button, content gets thrown into a div, which I want the PHP after the JavaScript has been run to take that content and put it into a database.

What is the best way to do that?

Why doesn't this work?

        function saveinPHP() {
        //alert("Came here");
        //var superstr = $( "#savelyric" ).text();
        var superstr = 'lol';
        $.ajax({
        type: "POST",
        url: "includes/sendlyrics.php",
        data: superstr,
        cache: false,
        contentType: false,
        processData: false,
        success:  function(data){
            alert("---"+data);
            alert("Settings has been updated successfully." + data + "~~~" + superstr);
            //window.location.reload(true);
        }
    });
    }

And then the PHP:

    <?php 
include ('db_connect.php');

$data = $_POST['data'];

    $query = "UPDATE song SET time=". $data ." WHERE id='1'";
    mysqli_query($query);
?>

Not heard so much about Ajax before, but I can check it out. Thanks! — Niklas, Apr 14 '14 at 13:24
@Kevkong Can you look at why this does not work? Code Looked like a mess, look at the post instead... — Niklas, Apr 14 '14 at 14:06
**Danger**: You are **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that you need to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Apr 14 '14 at 14:08

score 0 · Answer 1 · answered Apr 14 '14 at 13:24

Write PHP in a totally separate dedicated file that takes POST variables, constructs an SQL query, and inserts them into a database. Then have your JavaScript function send the data to this PHP file using a POST request.

JavaScript in the browser cannot interact with the database. It can only send GET/POST requests to the server which can catch those requests and put the attached data into the database.

score 0 · Answer 2 · edited May 23 '17 at 12:05

0

First, when you specify a string as data, jQuery will send it as is.

The string you are using "lol" is not formatted in either of the standard formats for POST data that are understood by PHP.

$_POST, therefore, has no data in it.

Pass jQuery an object instead:

data: { data: superstr }

Second, false is not a content-type of either of the standard formats mentioned above. jQuery will use an appropriate content-type by default. Remove this override:

contentType: false,

Third, processData: false, will break the conversion of the object into form encoded data. Remove it.

Fourth, strings in SQL must be quoted. You aren't quoting data.

 $query = "UPDATE song SET time='$data' WHERE id='1'";

Note this is still vulnerable to SQL injection and you should fix that.

edited May 23 '17 at 12:05

Community

1
1

answered Apr 14 '14 at 14:10

Quentin

914,110
126
1,211
1,335

Thank you so much for your answer! I'd like to ask, the thing about objects. "Lol" was only a test string, the real thing I'd like to send is `var superstr = $( "#savelyric" ).text();`. Is that already an object, or how do I make it one? – Niklas Apr 14 '14 at 14:15
No, it is a string. You do the same thing as I did for the previous string. – Quentin Apr 14 '14 at 14:16
It's everything I spotted. Look at your JavaScript error console. Check the request and response in your browser's developer tools Net tab. Check your server log for errors. – Quentin Apr 14 '14 at 14:19

How do I update my database with mysqli and javascript

2 Answers2