Mysql UPDATE failing to excute

General PHP Help
#1

Hello everyone, new to site hoping to find help with a MySQL Query problem. Been banging my head on the wall for weeks and can not figure out what the issue is.

Quick BreakDown
user selects option from form (single value)
on submit processes in same page.
Script gets to

$sql = "UPDATE `status` SET `system_status`= '<label class='text-danger'> Maint Mode </label>' WHERE `id`= '$maint_mode'";

And fails to Execute unsure as to why as I have ran:

UPDATE `status` SET `system_status`= '<label class='text-danger'> Maint Mode </label>' WHERE `id`= '1'

with phpmyadmin simulate query and have no issues. Record is updated no errors

Page out:

Form Data Received.
Data = 1
ERROR:Could Not Execute
UPDATE status SET system_status= ’ Maint Mode ’ WHERE id= ‘1’

I have PHP error reporting E_ALL turned on
in php.ini
have these turned on
php_flag display_startup_errors on
php_flag display_errors on

error log shows no errors

-----------Form------------

<form action="<?=$_SERVER['PHP_SELF']; ?>" method="POST">
        <div class="row g-3">
            <div class="col">
                <h5 class="modal-title">Maintenance Mode</h5>
                <p>Select system below to PLACE in Maintenance Mode.</p>
                <div class="input-group mb-3">
                    <div class="input-group">
                        <select class="form-select" id="maint_mode_on" name="maint_mode_on">
                            <option value=""></option>
                            <option value="1"> 1 </option>
                            <option value="2"> 2 </option>
                            <option value="3"> 3 </option>
                            <option value="4"> 4 </option>
                            <option value="5"> 5 </option>
                            <option value="6"> 6 </option>
                            <option value="7"> 7 </option>
                            <option value="8"> 8 </option>
                            <option value="9"> 9 </option>
                            <option value="10"> 10 </option>
                            <option value="11"> 11 </option>
                            <option value="12"> 12 </option>
                        </select>
                    </div>
                    <input name="maint_mode_active" type="submit" id="maint_mode_active" value=" Maint Mode " class="btn btn-outline-warning">
                </div>
            </div>
        </div>
    </form>

----Processing Section -----

<?php
    // Check Form Was Submitted

    if (isset($_POST['maint_mode_active'])) {
        $maint_mode = $_POST['maint_mode_on'];
        echo "Form Data Received.<br /> Form Data = $maint_mode ";

   //  Connection   
    $mysqli = new mysqli("localhost", "username", "password", "database");
  
    if($mysqli === false){
   	die("ERROR: Could Not Connect To Database. <br /> "
            	. $mysqli->connect_error);

     } else{

        echo 'Connected To Database. ';
    }
	
	
    $sql = "UPDATE `status` SET `system_status`= '<label class='text-danger'> Maint Mode </label>' WHERE `id`= '$maint_mode'";
	
    if($mysqli->query($sql) === true){
        echo " <br />$maint_mode_on System is in Maint Mode!";

    } else{
        echo "<br />ERROR:Could Not Execute   <br />  $sql " 
                                        . $mysqli->error;
    }
    
    $mysqli->close();

    }
?>

----- FULL SCRIPT —

<?php
ini_set('error_reporting', E_ALL);
?>
<html>
   <head>
      <title>Updating MySQL Update Record Test 2.0</title>
   </head>
   <body>
	
<?php

    if (isset($_POST['maint_mode_active'])) {
        $maint_mode = $_POST['maint_mode_on'];
        echo "Form Data Received.<br /> Data = $maint_mode ";
     
    $mysqli = new mysqli("localhost", "username", "password", "database");
  
    if($mysqli === false){
   	die("ERROR: Could Not Connect To Database. <br /> "
            	. $mysqli->connect_error);
    }
    else{
        echo 'Connected To Database. ';
    }
	
    $sql = "UPDATE `status` SET `system_status`= '<label class='text-danger'> Maint Mode </label>' WHERE `id`= '$maint_mode'";
	
    if($mysqli->query($sql) === true){
        echo " <br />$maint_mode_on System is in Maint Mode!";
    } else{
        echo "<br />ERROR:Could Not Execute   <br />  $sql " 
                                        . $mysqli->error;
    }
    
    $mysqli->close();
    }
?>      
 

<form action="<?=$_SERVER['PHP_SELF']; ?>" method="POST">
        <div class="row g-3">
            <div class="col">
                <h5 class="modal-title">Maintenance Mode</h5>
                <p>Select system below to PLACE in Maintenance Mode.</p>
                <div class="input-group mb-3">
                    <div class="input-group">
                        <select class="form-select" id="maint_mode_on" name="maint_mode_on">
                            <option value=""></option>
                            <option value="1"> 1 </option>
                            <option value="2"> 2 </option>
                            <option value="3"> 3 </option>
                            <option value="4"> 4 </option>
                            <option value="5"> 5 </option>
                            <option value="6"> 6 </option>
                            <option value="7"> 7 </option>
                            <option value="8"> 8 </option>
                            <option value="9"> 9 </option>
                            <option value="10"> 10 </option>
                            <option value="11"> 11 </option>
                            <option value="12"> 12 </option>
                        </select>
                    </div>
                    <input name="maint_mode_active" type="submit" id="maint_mode_active" value=" Maint Mode " class="btn btn-outline-warning">
                </div>
            </div>
        </div>
    </form>



   </body>
</html>
#2

The single-quotes within the string data value should have produced an error when you ran this query directly within phpmyadmin. About the only way this wouldn’t have is if the character encoding for them is something other than your database table’s character encoding, possibly due to copy/pasting that markup from somewhere on the web, rather than typing it yourself.

In the php code, you are getting a query error, based on the branch the conditional logic is taking, but are not seeing the mysqli->error output. I suspect this is because the sql you are echoing contains html markup and/or the questionably encoded single-quotes. If this is what is happening, you should be able to see the error output in the ‘view source’ in your browser.

As to why the php code executing the query would produce an error, but executing it directly within phpmyadmin does not, is most likely due to a character conversion taking place over the connection between php and the database server.

In any case, you shouldn’t store html markup in a database. Databases are for storing data, not html markup. Also, a <label></label> tag is used with form fields. You should just store the ‘Maint Mode’ text in the database and style it when it is output on a web page.

Next, don’t put external, unknown, dynamic values direction into sql query statements. Use a prepared query instead. For those cases where a data value can contain sql special characters, such as a quote, using a prepared query will allow them to be inserted/updated into a database.

And, don’t echo the raw $_SERVER[‘PHP_SELF’] value on a web page. It is open to cross site scripting. To get a form to submit to the same page, simply leave out the whole action attribute.

Sponsor our Newsletter | Privacy Policy | Terms of Service