Immediate update to database upon clicking check box

[bmtndog]

Many, many years ago I wrote a web-based recipe database program using CodeIgnitor. I have not coded much since then, so have not kept up with that skill or the changes to PHP, etc. So, I could use your help.

 

In the UI for the recipe database, I have some recipe attributes such as whether it is a New recipe, one that I want to Export, etc. These are represented as checkboxes. While I have a editor that can individually set these attributes that get saved to the DB when I submit the editing form, I also wanted to have a "live" ability to set these in a list view of recipes. So, I found some code on these forums. Here is the code I found. This was placed as a function in the HTML header.

// Coding guidance from http://www.phpfreaks.com/forums/index.php?topic=257587.0	
function chkit(row, chk, field) {

   chk = (chk==true ? "1" : "0");
   var url = "http://localhost/recipes/check_validate.php?number="+row+"&chkYesNo="+chk+"&field="+field;

   if(window.XMLHttpRequest) {
      req = new XMLHttpRequest();
   } else if(window.ActiveXObject) {
      req = new ActiveXObject("Microsoft.XMLHTTP");
   }
   // Use get instead of post.
   req.open("GET", url, true);
   req.send(null);
}

The check_validate.php code is:

<?php

$dbhost = 'localhost';   // usually localhost
$dbuser = 'username';      // database username
$dbpass = 'password';      // database password

$db = @mysqli_connect($dbhost, $dbuser, $dbpass) or die ("Database connection failed.");

mysql_select_db('recipes');

// Get the variables.
$number = $_GET['number'];
$value = $_GET['chkYesNo'];
$field = $_GET['field'];

$sql = "UPDATE recipes SET $field = $value WHERE recipeNumber=$number";

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

?>

Then, in the recipe list view, each attribute has code that looks something like this:

<input type="checkbox" name="f_mark2_1052" value="0"   onclick="chkit(1052, this.checked, 'mark2');" />

When I recently updated the CodeIgnitor framework, this stopped working for me. One thing that I know changed was a change from using mysql to mysqli. So, I'm not sure how that would change the code in the 2nd example above. 

 

I am getting no errors or any indication of what may be wrong. So fixes to this code, or another method for accomplishing my goal would be appreciated.

[benanamen]

Your code is too old and insecure to spend the time trying to update it to Mysqli. The way we do things now is with PDO. https://phpdelusions.net/pdo

[maxxd]

I am getting no errors or any indication of what may be wrong.

 

You probably are, though they're not going to be printed to screen. The update is being done via AJAX, so you'll want to check your browser console as you migrate to PDO, as benanamen suggests.

[bmtndog]

So, only the check_validate.php code (2nd in the example code above) would need to be changed? CodeIgnitor provides similar DB access abstraction as PDO it seems, maybe I can figure out how to use that by bringing check_validate.php into my model code. Is PDO part of PHP, or do I need to load a library?

 

Sorry for the naïve questions. 

 

I'm open to alternative ways to do the AJAX stuff.

[Jacques1]

Unfortunately, you have more problems than the obsolete database interface.

• Your Ajax code is written for 90s browsers. The last browser which didn't support the standard XMLHttpRequest API was Internet Explorer 6, and even Microsoft no longer wants you to use that.
• Instead of writing the same low-level JavaScript code over and over again, you should consider using a library like jQuery. This would turn the whole thing into a one-liner.
• Abusing the GET method to change data is conceptually wrong. GET is strictly for getting data (hence the name), and browsers expect it to have no side effects. If you violate that assumption, you'll quickly end up with unintented data changes. The right method is POST.
• Your form has no protection against cross-site request forgery attacks either. This makes it easy to send requests on behalf of other users.
• Changing the data as soon as the user clicks on the checkboxes isn't necessarily smart, because it can violate user expectations. The standard workflow is that changes don't happen until the form has been submitted. If you depart from that rule, you should be absolutely sure that all users are aware of the nonstandard workflow.
• Inline JavaScript code is messy, obsolete and prevents the use of modern security features like Content Security Policy. You should completely separate the JavaScript code from the HTML markup (i. e. keep it in external files).

As to the PDO questions: PDO is an extension. On properly maintained servers, it's usually preinstalled, but you'll have to check that. If you can use the standard features offered by your framework, even better.

[bmtndog]

So, I just updated the check_validate.php to use mysqli, as shown here:

<?php
// Would be nice to generalize this, even integrate with the recipe model code
$dbhost = 'localhost';   // usually localhost, this mimics what is in the database config file
$dbuser = 'user';       // database username
$dbpass = 'pass';   // database password
$dbname = 'db'; // database name

$db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($db->connect_error) {
trigger_error('Database connection failed: ' . $db->connect_error, E_USER_ERROR);
}

$number = $_GET['number'];
$value = $_GET['chkYesNo'];
$field = $_GET['field'];

$sql = "UPDATE recipes SET $field = $value WHERE recipeNumber=$number";

$rs=$db->query($sql);

if($rs === false) {
trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $db->error, E_USER_ERROR);
} else {
$rows_returned = $rs->num_rows;
}

?>

And, now it works. I'm still getting some Access-Control-Allow-Origin errors in the js console, so need to sort that out I guess. But, code functionality has been restored.

 

Now, I'll look into updating this to "modern" technology as suggested in the previous comment. 

 

Thanks for the help.

[bmtndog]

Since the DB code is working, I guess the biggest issue I have is with the inline javascript in my HTML header, as pointed out by Jacques1. I'm studying jquery and I am sure I can sort that out in time. But, is there any chance someone could provide me with the code I'd use in my HTML header as well as an external script to accomplish what I am doing with the inline javascript?

 

Does the code for the checkboxes need to change too? Or, do I still use the onclick detection?

 

Regarding user expectations, there are only a handful of users and they all know what functionality to expect. They all want to be able to change attributes with a single click as opposed to opening the editor, editing the attribute and then submitting that form. So, I think I'm okay on that usability point.

[Jacques1]

Your code isn't “working”. What does that even mean? That you've opened the page a few times in your browser and didn't see any error messages? Great, but this isn't how the real world works. On the Internet, your software will face invalid requests, malicious users, human stupidity, programming mistakes and much more.

 

Unfortunately, your code has already failed the reality check: It's wide open to SQL injection attacks, accepts forged requests, accepts invalid requests, violates the HTTP protocol and leaks internal information all over the place. This doesn't even work for well-meaning users, let alone malicious ones.

 

I strongly recommend you take our advice more seriously. This isn't about “modernizing” your code, as you seem to think. It's about making the application survive. You can worry about the rest later.

[bmtndog]

Jacques1, I am trying to take all the advice. I am learning this stuff and take it one step at a time. I solved my immediate problem, and learned some things in the process. Now, I want to move forward making the "code" more "modern", meaning doing what it takes to address the issues you point out in a contemporary way using technologies and techniques that I do not yet understand. I do appreciate knowing what needs to be done. I just don't yet know how to do it. That is the help I was asking for.

 

I've found some online resources for using jQuery and Ajax, one even within the CodeIgnitor framework. I don't fully understand them yet, and will need some time to work through the reading, examples, etc. So, I was just looking for examples that were very closely tied to what I'm trying to do.

 

Thanks for taking the time to point me in the right direction.

[Jacques1]

Sure. My point is that you should first deal with the fatal errors and then move on to the less important stuff. Integrating jQuery is nice, but the vulnerabilities in your PHP code can compromise the whole server (which isn't just a modernization issue). So I was somewhat worried when you said that the database code is “working” now.

[NotionCommotion]

Got good news for you bmtndog!  Things are easier than you experienced earlier.  A bit of jQuery and some PDO for your queries, and in no time you will be 90% of the way there.

[Strider64]

I personally would concentrate on getting PHP with PDO acting right, before moving on to JavaScript/jQuery. It's relatively easy to add client-side script later and at the same time you'll have graceful degradation with the scripts. Myself I find if I have too many layers (I'm talking about coding) going on at the same time I get easily confused, but it could just be my old age creeping up on me.  Just my .02 cents. 

 

BTW JQuery is easy to implement Ajax but plain vanilla JavaScript isn't that much harder. 

 

For example this is a little bit of the coding of a trivia game that I'm developing..

function insertNew() {

    var addform = document.getElementById('addTrivia');
    var action = 'add_trivia.php';


    var fdata = new FormData(addform);
//    for ([key, value] of fdata.entries()) {
//        console.log(key + ': ' + value);
//    }
    var xhr = new XMLHttpRequest();
    xhr.open('POST', action, true);
    xhr.setRequestHeader('X-Requested-With', 'XMLHttpRequest');
    xhr.onreadystatechange = function () {
        if (xhr.readyState == 4 && xhr.status == 200) {
            var result = xhr.responseText;
            console.log(result);
            var info = JSON.parse(result);
            console.log('info', info);
            if (info.result) {
                document.getElementById("addTrivia").reset();
                function closeit() {
                    document.getElementById("shadow").style.display = "none";
                }
                if (info.result) {
                    document.getElementById('shadow').style.display = 'block';
                    setTimeout(closeit, 2000);
                }
            }

        }
    }
    xhr.send(fdata);
}

done in vanilla javascript. Though I agree jQuery is easier to use for Ajax.