0

I'm just a weekend programmer so forgive my ignorance.

I have a "Status Board" webpage which shows the operational status of our command centre. It's built very basically with html/css/js/php.

I use ajax to call to the backend whenever a change of condition is submitted. Example below, updating passenger, crew & total souls on board numbers.

Is this the best way to query and update multiple values to my MySQL database? Or can I prepare all of them and then execute at the end of the code, to lessen the number of queries? (I'm used to python where you can set out all the SQL updates and then do a db.session.commit() at the end to apply all of the updates)

I've used PDO to initiate the DB connection. The json_encode sends the values back to the webpage once successful and updates the status board.

`

         <?php
         include "db.inc.php";
         if(isset($_POST['sob'])){
            $pax = $_POST['pax'];
            $crew = $_POST['crew'];
            $sob = $_POST['sob'];
        
        $res = [];
        
        if($pax='0'){
            $pax = '&nbsp;&nbsp;&nbsp;&nbsp;';
            $sql="UPDATE bridge_text_box SET wording = $pax WHERE id = 'pax1' LIMIT 1";
            $query = $db->prepare($sql);
             
        } else {
            $sql="UPDATE bridge_text_box SET wording = $pax WHERE id = 'pax1' LIMIT 1";
            $query = $db->prepare($sql);
        };
        
        if($query->execute()){
            $res["rpax"] = $pax;
        } else {
            echo "Failure";
        };
        
            if($crew='0'){
            $crew = '&nbsp;&nbsp;&nbsp;&nbsp;';
            $sql="UPDATE bridge_text_box SET wording = $crew WHERE id = 'cre1' LIMIT 1";
            $query = $db->prepare($sql);
             
        } else {
            $sql="UPDATE bridge_text_box SET wording = $crew WHERE id = 'cre1' LIMIT 1";
            $query = $db->prepare($sql);
        };
        
        if($query->execute()){
            $res["rcrew"] = $crew;
        } else {
            echo "Failure";
        };
        
            if($sob = '0'){
            $sob = '&nbsp;&nbsp;&nbsp;&nbsp;';
            $sql="UPDATE bridge_text_box SET wording = $sob WHERE id = 'sob' LIMIT 1";
            $query = $db->prepare($sql);
             
        } else {
            $sql="UPDATE bridge_text_box SET wording = $sob WHERE id = 'sob' LIMIT 1";
            $query = $db->prepare($sql);
        };
        
        if($query->execute()){
            $res["rsob"] = $sob;
        } else {
            echo "Failure";
        };
     
     echo json_encode($res);
    
    
    } else {
        echo 'Failed';
    };
?>

`

java_jaybles
  • 1
  • 1
  • 1
    Why are you inserting ` ` into the database? Also, that’s not how [prepared statements](https://www.php.net/manual/en/pdo.prepare.php) work. You should be setting placeholders and providing their values later. – Chris Haas Sep 11 '22 at 13:01
  • On the table side, if there was no values, then there would be nothing to click on to bring up the modal to update the value. So I figured a way to get around this is to enter   , the means there's something to click on to bring up the modal. Understood with the prepared statements so I would add these later using $query->bindParam? – java_jaybles Sep 11 '22 at 13:20
  • If there’s no values, that could just be rendered and stored as `0`. When building your database, don’t think too much about the display, keep that separate. If you are storing a quantity, it should always be numeric. Your render code could then check for zero and even convert that to “none” or similar. – Chris Haas Sep 11 '22 at 13:33

0 Answers0