While updating database with php i'm experiencing some issue

Question

I'm working on library management system. and in update field. if user didn't pass data the i want to keep my data as it is, instead of changing it into blank

<?php
if( isset($_POST['update']) )
{
    $conn = mysql_connect('localhost', 'root', '');
    if( !$conn )
    {
        die('Could not connect: ' . mysql_error());
    }
    $db_selected = mysql_select_db('library', $conn);
    $Book_ID     = $_POST['Book_ID'];
    $Book_Name   = $_POST['Book_Name'];
    $Author      = $_POST['Author'];
    $Quantity    = $_POST['Quantity'];

    $sql    = "update books set Book_Name='$Book_Name', Author='$Author', 
            Quantity='$Quantity' where Book_ID='$Book_ID'";
    mysql_select_db('test_db');
    $retval = mysql_query($sql, $conn);

    if( !$retval )
    {
        die('Could not update data: ' . mysql_error());
    }
    echo "Updated data successfully\n";
    header("refresh:2; url=updatebook.php");
    mysql_close($conn);
}
else
{
    ?>
    <form method = "post" action = "<?php $_PHP_SELF ?>">
        <table width = "400" border =" 0" cellspacing = "1" cellpadding = "2">
            <tr>
                <td width = "100">Book ID</td>
                <td><input name = "Book_ID" type = "number" id = "Book_ID"></td>
            </tr>
            <tr>
                <td width = "100">Book Name</td>
                <td><input name = "Book_Name" type = "text" id = "Book_Name"></td>
            </tr>
            <tr>
                <td width = "100">Author</td>
                <td><input name = "Author" type = "text" id = "Author"></td>
            </tr>
            <tr>
                <td width = "100">Quantity</td>
                <td><input name = "Quantity" type = "text" id = "Quantity"></td>
            </tr>

            <tr>
                <td width = "100"> </td>
                <td> </td>
            </tr>

            <tr>
                <td width = "100"> </td>
                <td>
            <tr>
                <td width = "100"> </td>
                <td> </td>
            </tr>

            <tr>
                <td width = "100"> </td>
                <td>
                    <input name = "update" type = "submit" id = "update" value = "Update">
                </td>
            </tr>
        </table>
    </form>
    <?php
}
?>

If user didn't want to change value of some field and keep it blank. i don't want to update that field with blank. I want to keep the data of that field, as it is.

from this code if i enter Book ID and kept Book Name, blank then this code passes the value

you can check for `isset($_POST['Book_Name'])` and then after pass it to query — Ritesh Khatri, Dec 05 '17 at 06:05
You can use `isNull()` the link might be help you check [sql-query-for-updating-database-if-value-is-not-null] (https://stackoverflow.com/questions/6099570/sql-query-for-updating-database-if-value-is-not-null) — Ritesh Khatri, Dec 05 '17 at 06:24
If you're writing new code, **_please_ don't use the `mysql_*` functions**. They are old and broken, were deprecated in PHP 5.5 (which is so old it no longer even receives security updates), and completely removed in PHP 7. Use [`PDO`](https://secure.php.net/manual/en/book.pdo.php) or [`mysqli_*`](https://secure.php.net/manual/en/book.mysqli.php) with _prepared statements_ and _parameter binding_ instead. See http://stackoverflow.com/q/12859942/354577 for details. — ChrisGPT was on strike, Dec 05 '17 at 19:49

score 2 · Accepted Answer · answered Dec 05 '17 at 06:37

2

You can do this with the help of simple if...else condition in mysql.

$sql = "UPDATE books SET
        Book_Name=IF(LENGTH('$Book_Name')=0, Book_Name, '$Book_Name'),
        Author=IF(LENGTH('$Author')=0, Author, '$Author'),
        Quantity=IF(LENGTH('$Quantity')=0, Quantity, '$Quantity')
        WHERE Book_ID='$Book_ID'";

Don't use direct query to database. Its like an open invitation to Sql Injection.

answered Dec 05 '17 at 06:37

Yash Parekh

1,513
2
20
31

My pleasure. You can also upvote the answer to raise up from other answers. Happy Coding... – Yash Parekh Dec 05 '17 at 07:03

score 0 · Answer 2 · answered Dec 05 '17 at 06:10

You need to fetch the original values first then use IF ELSE to use that old value instead of the new one if the variable is empty. Refer to the code below which i've modified:

`

    $conn = mysql_connect('localhost', 'root', '');

    if(!$conn ) {
       die('Could not connect: ' . mysql_error());
    }
    $db_selected = mysql_select_db('library', $conn);
    $old_data = mysql_fetch_array(mysql_query("select * from books where Book_ID='".$_POST['Book_ID']."'"));

    $Book_ID = $_POST['Book_ID'];
    $Book_Name = ($_POST['Book_Name'] == '' ? $old_data['Book_Name'] : $_POST['Book_Name']);
    $Author = ($_POST['Author'] == '' ? $old_data['Author'] : $_POST['Author']);
    $Quantity = ($_POST['Quantity'] == '' ? $old_data['Quantity'] : $_POST['Quantity']);

    $sql = "update books set  Book_Name='$Book_Name', Author='$Author', 
    Quantity='$Quantity' where Book_ID='$Book_ID'";
    mysql_select_db('test_db');
    $retval = mysql_query( $sql, $conn );

    if(! $retval ) {
       die('Could not update data: ' . mysql_error());
    }
    echo "Updated data successfully\n";
    header("refresh:2; url=updatebook.php");

    mysql_close($conn);
 }else {
    ?>
       <form method = "post" action = "<?php $_PHP_SELF ?>">
          <table width = "400" border =" 0" cellspacing = "1" 
             cellpadding = "2">

             <tr>
                <td width = "100">Book ID</td>
                <td><input name = "Book_ID" type = "number" 
                   id = "Book_ID"></td>
             </tr>

             <tr>
                <td width = "100">Book Name</td>
                <td><input name = "Book_Name" type = "text" 
                   id = "Book_Name"></td>
             </tr>
             <tr>
                <td width = "100">Author</td>
                <td><input name = "Author" type = "text" 
                   id = "Author"></td>
             </tr>

             <tr>
                <td width = "100">Quantity</td>
                <td><input name = "Quantity" type = "text" 
                   id = "Quantity"></td>
             </tr>

             <tr>
                <td width = "100"> </td>
                <td> </td>
             </tr>

             <tr>
                <td width = "100"> </td>
                <td>
                <tr>
                <td width = "100"> </td>
                <td> </td>
             </tr>

             <tr>
                <td width = "100"> </td>
                <td>
                   <input name = "update" type = "submit" 
                      id = "update" value = "Update">
                </td>
             </tr>

          </table>
       </form>

    <?php
 }

?> `

While updating database with php i'm experiencing some issue

2 Answers2