Updating SQL database from a dropdown using PHP script

Question

I am just self-learning PHP and SQL. This question seems repetitive, but I cannot find exact solution for my problem.

I have a SQL database for inventory management of mobile phones. I have implemented PHP script to display the database contents in a table. I have to further enhance the script, so the user can change the status of the mobile phone such as Working or Not working. For this I have created another SQL database storing this info. I am able to display the details in a dropdown, but when I change from Working to Not working and select Submit button, no change is seen in the database and also in the web server.

<?php
$servername="localhost";
$username="root";
$password="XXXXX";
$dbname="inventory_db";

//Connection

$conn =mysqli_connect($servername,$username,$password);
$db_handle = $conn->select_db($dbname);

//Connection check
if($conn->connect_error)
{
    die("Connection failed:" .$conn->connect_error);
}
else {
    echo "connection setup";
}

if($db_handle)
{
$sql="SELECT asset_no,asset_name,current_holder,location,status FROM Phone_table ";
$sql_status="SELECT idstatus,status_name FROM status_table";
?>

<!DOCTYPE html>
<HTML>
    <HEAD>
        <STYLE>

            .asset_table
            {
                width: 100%;
            border :1px solid black;
            }

            td{
                text-align: left;
                padding: 15px;
                border: 1px solid black;
            }
            th{
                border: 1px solid black;
            }
        </STYLE>
    </HEAD>
<BODY>
    <form method="post">

<TABLE class="asset_table">
      <TR>
        <TH>Asset Number</TH>
        <TH>Asset Name</TH>
        <TH>Asset Holder</TH>
        <TH>Location</TH>
        <TH>Status</TH>
      </TR>
    <?php
    $result=$conn->query($sql);
    $count=mysqli_num_rows($result);
    if($result->num_rows > 0)
    {
    while($row=$result->fetch_assoc())
    {?>
    <TR>

            <TD> <?php echo $row['asset_no']; ?> </TD>
            <TD>  <?php echo $row['asset_name']; ?></TD>
            <TD>  <?php echo $row['current_holder']; ?></TD>
            <TD>  <?php echo $row['location']; ?></TD>
            <TD><select>
                <?php
<!-- *****This is where I am stuck*****-->     
                $result_status=$conn->query($sql_status);
                if($result_status->num_rows > 0)
                {
                    while($row_status=$result_status->fetch_assoc())
                    { ?>
                        <option value =' <?php echo $row_status['idstatus']?> '> 
                       <?php echo $row_status['status_name'];?> </option>
                       <?php $row['status']=$row_status['status_name'];
                       }} ?></select>
               </TD>
    </TR>
    <?php
    }}?>
    <input type="submit">
    </form>

    <?php
    if($submit)
    {
        for($i=0;$i<$count;$i++)
        {
            $sql="UPDATE Phone_table SET status='$status[$i]' WHERE asset_no='$asset_no[$i]'";
            $result=$conn->query($sql);
        }
    }
    ?>

</TABLE>
</BODY>
</HTML>
<?php }
ob_end_flush(); ?>

oh yes... I see it now. It is, however, in the wrong place - you need to open and close elements in mirror order rather than just when it feels right ;-) glad I was able to help btw - it is easy to extend that to allow updating of all fields ( asset name,holder,location etc ) — Professor Abronsius
– Professor Abronsius, Commented Jan 30, 2019 at 9:06

Professor Abronsius · Accepted Answer · 2019-01-30 08:05:10Z

One problem with the existing code is that there was no way to relate the submitted value for the asset status ( even if it had been given a name! ) to the particular record in the database. The update statement would require, usually, an ID in the where clause so that the releveant record can be updated as opposed to ALL records being updated equally. To that end, given the HTML structure and generaly approach ( no javascript & single form ) using a hidden input field for each row in the table to hold the record ID seems to make sense. When the form is submitted the value for the select menu and the id should be relatable - you will see by the demo below.

Because there is a single form with multiple records the select menu and the hidden input will need to be treatable as arrays - that is to say their names should be of the form name[]

Another thing to note perhaps is the use of variables directly within the sql. This practise leaves your code vulnerable to sql injection and whilst this may be on a closed system somewhere with trusted users etc you never know what might happen!

<?php
    error_reporting( E_ALL );
    ini_set( 'display_errors', 1 );


    /* connect to the db */
    $dbhost =   'localhost';
    $dbuser =   'root'; 
    $dbpwd  =   'xxx'; 
    $dbname =   'inventory_db';
    $db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );


    /* declare the sql statements for later use */
    $sql=(object)array(
        'phones'    =>  'select `asset_no`, `asset_name`, `current_holder`, `location`, `status` from `phone_table`',
        'status'    =>  'select `idstatus`, `status_name` from `status_table`',
        'update'    =>  'update `phone_table` set `status`=? where asset_no=?'
    );



    /* very basic utility function to generate select menu */
    function createselect( $name, $data, $value ){
        $html=array();
        $html[]=sprintf( '<select name="%s">', $name );
        foreach( $data as $id => $status ){
            $selected = $id == $value ? ' selected=true' : '';
            $html[]=sprintf('<option value="%s"%s>%s', $id, $selected, $status );
        }
        $html[]='</select>';
        return implode( PHP_EOL, $html );
    }



    /* query database to get possible status values which are used to create the SELECT menus */
    $status=array();
    $results=$db->query( $sql->status );
    if( $results ){
        while( $rs=$results->fetch_object() )$status[ $rs->idstatus ]=$rs->status_name;
    }




    if( $_SERVER['REQUEST_METHOD']=='POST' ){
        ob_clean();

        /* using `user supplied data` - use a prepared statement to be safe! */
        $stmt=$db->prepare( $sql->update );
        $stmt->bind_param( 'ii', $assetstatus, $assetid );

        /* Perform the update */
        foreach( $_POST['id'] as $i => $assetid ){
            $assetstatus = $_POST['status'][ $i ];
            $stmt->execute();
        }

        $stmt->close();
        exit( header( 'Location: ?db=updated' ) );
    }
?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title>Asses or Assets?</title>
        <style>
            body{font-family:verdana}
            table{ width: 100%; border :1px solid black; }
            td{ text-align: left; padding: 15px;  border: 1px solid black;text-align:center; }
            th{ border: 1px solid black; }
            input,select{padding:1rem;width:100%}
        </style>
    </head>
    <body>
    <?php

        /* get the phones... no user input so no need for prepared statement */
        $results = $db->query( $sql->phones );
        if( $results ){

    ?>
        <form method='post'>
            <table>
                <thead>
                    <tr>
                        <th>Asset Number</th>
                        <th>Asset Name</th>
                        <th>Asset Holder</th>
                        <th>Location</th>
                        <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                    while( $rs = $results->fetch_object() ){
                        printf(
                            "<tr>
                                <td>%s<input type='hidden' name='id[]' value='%d' /></td>
                                <td>%s</td>
                                <td>%s</td>
                                <td>%s</td>
                                <td>%s</td>
                            </tr>",
                            $rs->asset_no,
                            $rs->asset_no,
                            $rs->asset_name,
                            $rs->current_holder,
                            $rs->location,
                            createselect( 'status[]', $status, $rs->status )
                        );
                    }
                ?>
                </tbody>
                <tfoot>
                    <tr>
                        <td colspan=5>
                            <input type='submit' />
                        </td>
                    </tr>
                </tfoot>
            </table>
        </form>
        <?php

        }//close `if`

        ?>
    </body>
</html>

Sven Mich · Accepted Answer · 2019-01-30 06:22:28Z

In general the form creation/display and the acting upon the submitted form are two entirely unrelated HTTP requests.

You have to change a few things to get your script to work:

1. Add a hidden field for each asset no.:

<TD>
<input type="hidden" name="asset_no[]" value="<?php echo $row['asset_no']; ?>">
<?php echo $row['asset_no']; ?>
</TD>

2. Add name attribute to your select fields:

<TD><select name="asset_status[]">

3. Make your select fields preselect the current status and remove the whitespace in the value:

<option value ='<?php echo $row_status['idstatus'] ?>' <?= $row['status'] == $row_status['idstatus'] ? ' selected' : '' ?>>

4. Remove this statement, as it does nothing (you're not writing to the array you read from the database):

$row['status']=$row_status['status_name'];

5. Add a name property to your submit field:

<input type="submit" name="submit">

6. Read your submitted form from the superglobal array $_POST (see php.net):


    if(isset($_POST['submit']))
    {
        for($i=0;$i<count($_POST['asset_no']);$i++)
        {
            $asset_status = mysqli_real_escape_string($conn, $_POST['asset_status'][$i]);
            $asset_no     = mysqli_real_escape_string($conn, $_POST['asset_no'][$i]);
            $sql = "UPDATE Phone_table SET status='$asset_status' WHERE asset_no='$asset_no'";
            $result = $conn->query($sql);
        }
    }

Thank you. I have made the changes as mentioned, but the problem continues still

Collectives™ on Stack Overflow

Updating SQL database from a dropdown using PHP script

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related