Update sql in javascript function

Question

I have an ordinary html table in which each cell contains a name. I've added a function to each of these cells, which turns the cells background color green, if it's white and the other way around. However, I would also like to update an mySql datebase, when a cell is clicked, but I can't seem to figure out a good way to do this, without reloading the page (which I would prefer not to do) or using javascript to connect to the server (which seems like a very bad practice). The page has already been loaded at this point. Does anybody have any good suggestions?

<script type="text/javascript">
var tbl = document.getElementById("table");
        if (tbl != null) {
            for (var i = 1; i < tbl.rows.length; i++) {
                for (var j = 0; j < tbl.rows[i].cells.length; j++)
                    tbl.rows[i].cells[j].onclick = function () { getval(this); };
            }
        }

        function getval(cel) {
            if(cel.style.backgroundColor == "green")
            {
                 cel.style.backgroundColor = "white";

                 // Here I would like to update my datebase with mySql
                 // query(UPDATE team SET attended=0 WHERE name = cel.innterText)
                 // (name associated with the cell)

            }
            else
            {
                cel.style.backgroundColor = "green";
                 // Here I would like to update my datebase with mySql
                 // query(UPDATE team SET attended=1 WHERE name = cel.innterText)
                 // (name associated with the cell)
            }
        }   
</script>

@Niels Sønderbæk You may want to use Ajax with JQuery instead. Performance wise, JQuery is better with Ajax. Hope it helps. Search Ajax and JQuery Retreive data from MySQL. — weia design, Aug 22 '13 at 12:59

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

In broad terms, you need to turn part of your application into a service and have calls to it made by an asynchronous HTTP request from your page (this falls under the "AJAX" denomination).

That service can be written as an extra PHP script on your server, which may not necessarily return an HTML document, but possible XML or JSON (the latter is probably more popular these days), which will be handled by your JavaScript script in the browser for further actions if necessary (e.g. turning the background white only if this request has succeeded).

It is this PHP script that should handle the SQL queries.

As a general guideline, don't prepare or handle any SQL at all on the client side (in your JavaScript script), and make sure you use prepared statements when running your SQL queries. (I'm just saying that because you're obviously new to this and you'll inevitably find snippets of code here or on various blogs where people just put the variables they in into their SQL statements by using the variable in the query strings. This is extremely bad practice.)

EDIT:

I actually need to go no further than W3Schools to have a bad example of MySQL query that is vulnerable to SQL injection (the problem is in $sql="SELECT * FROM user WHERE id = '".$q."'";). DO NOT USE THIS EXAMPLE. I'd avoid W3Schools, see http://www.w3fools.com/

score 1 · Answer 2 · answered Aug 22 '13 at 11:42

1

SQL is server side, not client side. You need to use AJAX to send data to your server and then the server will use SQL to save.

answered Aug 22 '13 at 11:42

Fabien Papet

2,244
2
25
52

Update sql in javascript function

2 Answers2