0

I'm rebuilding an old MS Access interface into a new PHP interface. The backend database is SQL Server and I'm trying to pull data from that database to the html form. So my form is filled out with all it's text input fields to the same name and ID as the column in the database table. I then have this snippet I wrote in the bottom.

<script type="text/javascript">
var mem = JSON.parse('<?php echo json_encode($vars); ?>');
$.each( mem[0], function( key, val) {
    $("#"+key).val(val);
    if (key == "Sex") {
        if (val == "M") {$("Sex-M").prop("checked", true);}
        else if (val == "F") {$("Sex-F").prop("checked", true);}
    }
    if (key == "InvalidEMail") {
        if (val == "1") {$("#InvalidEMail").prop("checked", true);}
        else if (val == "0") {$("#InvalidEMail").prop("checked", false);}
    };
}
</script>

So here's the question I have. There's got to be a better way of doing this. I'd like the JavaScript to dynamically check the checkboxes if the value is true. But according to Microsoft's SQL Server Driver for PHP a bit operator (for true and false) in SQL Server comes out as a standard integer in PHP. So then is there no way for PHP to be sure that this is a true/false statement instead of an actual 1 or 0?

If I tell JS to just look for ones and zeroes and make them into true/false, I'm afraid I'll grab some bit (see what I did there?) of data somewhere that SHOULD be a literal one or zero, and try to convert it into true/false incorrectly.

This code I wrote will work for the items I specify, but won't dynamically find other checkboxes that need them to be true/false.

ADDENDUM: I have about a hundred forms to rebuild like this one. So I've decided to try to build this in a manner that would allow me to easily copy and paste a template and change as little as possible to make them function for a new form. That's why I want this to dynamically populate the data. Because this form functions as both a "New" and "Edit" form, the form itself I'd like to have empty by default. True, I could skip javascript altogether and run an if statement in each input field ala:

 <?=(isset($vars[0]['InvalidEmail']))?$vars[0]['InvalidEmail']:'';?>

but the problem would remain the same. the SQL Server drivers for PHP convert bit operators to integer. That seems like a full stop problem to me; no solution but to hard code every bit-type column as a boolean in php. But I was hoping some brilliant programmer out there as figured out a good way around this.

DauntlessRob
  • 765
  • 1
  • 7
  • 17
  • 1
    Within the PHP code, you could use boolval() to convert the 0/1 values to boolean for output. Yes, it might be the case that the "bit" is translated to an integer when it's read by the database driver, but boolval() will happily translate those integer 0/1 values to true/false – Craig Jun 08 '21 at 00:19
  • 1
    Side Note: Your gender checkbox (or radio) selectors are invalid. Missing `#` or dot prefix – charlietfl Jun 08 '21 at 00:21
  • `(bool)$varHere;` is an easy way to cast to a boolean in PHP. If you want to do anything dynamically, you're going to want to learn to use JavaScript's `XMLHttpRequest` or the `fetch` API. – StackSlave Jun 08 '21 at 00:29
  • `$bool = Your_DB_Value === 1;` – Ravi Hirani Jun 08 '21 at 00:37
  • Why is Javascript involved here? Why not just mark the boxes checked in the HTML? You should also consider whether or not this interface is exclusionary to non-binary users. – miken32 Jun 08 '21 at 01:01
  • What is the purpose of the PHP/SQL conversion and involvement with the [loose-type comparisons](https://jsfiddle.net/5g2muorv/) in Javascript? What protocol is being used to transmit the values to/from Javscript, PHP and SQL? – Will B. Jun 08 '21 at 01:09
  • @charlietfl Thank you for that! I missed it. – DauntlessRob Jun 08 '21 at 18:57
  • @Craig That would likely help with javascript to php, but that's still ends up causing me to have to individually convert columns to a boolean value. – DauntlessRob Jun 08 '21 at 18:59
  • @miken32 I'm trying to work with the fewest lines of code possible for the hundreds of forms I'm going to have to build. And I'd like to have some code that can dynamically fill in that data. It is possible to do this all without javascript, but the problem of recognizing a SQL bit type in php as a Boolean dynamically remains the same. – DauntlessRob Jun 08 '21 at 19:02
  • @WillB. I'm pulling data from an existing SQL Server database, using the "Microsoft Drivers for PHP for SQL Server." I believe that is where the data type conversion is happening from `bit` to `integer`. At that point, I've lost the ability to determine if it's a boolean value or an actual integer of one or zero. I'm hoping a brilliant programmer out there has thought of a way around this.... – DauntlessRob Jun 08 '21 at 19:07
  • 1
    Right, I understood that part. My question was more directed toward determining the reasoning behind Javascript/PHP being used at all. Since a standard `$_POST` request would also lose the bit/boolean and other value relationships, as `true` /`false` would be translated to `1` or `0` in HTML unless you convert it to a string or have some other form of schema definition elsewhere to validate against. My suggestion would be to retrieve and parse the table schema rules for the columns and use it to determine the appropriate export/import. EG: `DECIMAL(10,8)` or `BIT NOT NULL` – Will B. Jun 08 '21 at 19:21
  • 1
    The fewest lines of code would mean not checking boxes with Javascript, and just use the `checked` attribute on the element. I also don't understand why you are having a problem with this "conversion." You know that 0==false and 1==true in both PHP and JS, right? You are already aware of the underlying database schema and which columns are `BIT` types because they are checkboxes in your form. – miken32 Jun 08 '21 at 19:23
  • 1
    Perhaps sharing more code and less exposition could help people understand your requirements better? – miken32 Jun 08 '21 at 19:29
  • @DauntlessRob - I'll admit, I probably didn't understand the full context of the question. But I think miken32's suggestions are closer to the mark - it sounds like you're trying to do something on the client side (with javascript) that you should really do on the server side (with PHP) - on the server side you're likely to find it much easier to iterate through records/columns and render the HTML as needed (ie. as miken32 suggests, just rendering – Craig Jun 08 '21 at 21:10
  • 1
    It sounds like the least amount of code would be by using [schema mapping](https://stackoverflow.com/q/13405572/1144627), as opposed to relying on the implicit data-type. Iterate over the associated columns and add the explicit data-type to their `$vars` value counter-parts. `$vars['InvalidEMail'] = [ 'value' => '1', 'type' => 'BIT' ]`, Add conditionals based on `BIT`, `VARCHAR`, etc to render the appropriate HTML elements and do away with the JavaScript. Then you can use the same schema mapping to validate and convert the submitted form data to prevent other issues. – Will B. Jun 09 '21 at 01:03
  • Ahah! Schema Mapping is exactly what I needed @WillB. Thank you! If you post it as an answer instead of a comment I'll gladly mark it as the answer. – DauntlessRob Jun 09 '21 at 23:52

0 Answers0