Update function php

Question

I'm working in a update file using php and mysql but the update function doesn't work. I wrote the code using an example and modified according to the requirements. The file does work and doesn't really drop any error but it doesn't change anything in the database. It is suppose to update a book database.

Code:

<?php 

$page_title = 'Add Books';
include ('bookincludes/header.html');

// Check for form submission:
if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    require ('../mysqli_connect.php'); // Connect to the db.

    $errors = array(); // Initialize an error array.

    if (empty($_POST['title'])) {
        $errors[] = 'Please add title.';
    } else {
        $e = mysqli_real_escape_string($dbc, trim($_POST['title']));
    }

    if (empty($_POST['author'])) {
        $errors[] = 'Please add the name of the author.';
    } else {
        $p = mysqli_real_escape_string($dbc, trim($_POST['author']));
    }


    if (!empty($_POST['isbn1'])) {
        if ($_POST['isbn1'] != $_POST['isbn2']) {
            $errors[] = 'ISBN number does not match.';
        } else {
            $np = mysqli_real_escape_string($dbc, trim($_POST['isbn1']));
        }
    } else {
        $errors[] = 'You need to enter ISBN number.';
    }

    if (empty($errors)) { // If everything's OK.

        $q = "SELECT ISBN FROM Books WHERE (Title='$e' AND Author ='$p')";
        $r = @mysqli_query($dbc, $q);
        $num = @mysqli_num_rows($r);
        if ($num == 1) { // Match was made.

            $row = mysqli_fetch_array($r, MYSQLI_NUM);

            // Make the UPDATE query:
            $q = "UPDATE Books SET ISBN='$np' WHERE ISBN = $row[0] ";       
            $r = mysqli_query($dbc, $q);

            if (mysqli_affected_rows($dbc) == 1) { // If it ran OK.

                // Print a message.
                echo '<h1>Thank you!</h1>
                <p>Thank you, Book has been added or modified</p><p><br /></p>';    

            } else { // If it did not run OK.

                // Public message:
                echo '<h1>System Error</h1>
                <p class="error">System error. We apologize for any inconvenience.</p>'; 

                // Debugging message:
                echo '<p>' . mysqli_error($dbc) . '<br /><br />Query: ' . $q . '</p>';

            }

            mysqli_close($dbc); // Close the database connection.

            // Include the footer and quit the script (to not show the form).
            include ('includes/footer.html'); 
            exit();

        } else {
            echo '<h1>Error!</h1>
            <p class="error">ISBN number is incorrect.</p>';
        }

    } else { // Report the errors.

        echo '<h1>Error!</h1>
        <p class="error">The following error(s) occurred:<br />';
        foreach ($errors as $msg) { // Print each error.
            echo " - $msg<br />\n";
        }
        echo '</p><p>Please try again.</p><p><br /></p>';

    } // End of if (empty($errors)) IF.

    mysqli_close($dbc); // Close the database connection.

} // End of the main Submit conditional.
?>
<h1>Update</h1>
<form action="Bupdate.php" method="post">
    <p>ISBN number: <input type="text" name="isbn1" size="20" maxlength="60" value="<?php if (isset($_POST['isbn1'])) echo $_POST['isbn1']; ?>"  /> </p>
    <p>Confirm ISBN: <input type="text" name="isbn2" size="20" maxlength="60" value="<?php if (isset($_POST['isbn2'])) echo $_POST['isbn2']; ?>"  /> </p>
    <p>Author: <input type="text" name="author" size="20" maxlength="60" value="<?php if (isset($_POST['author'])) echo $_POST['author']; ?>"  /></p>
    <p>Title: <input type="text"" name="title" size="20" maxlength="60" value="<?php if (isset($_POST['title'])) echo $_POST['title']; ?>"  /></p>
    <p>Year: <input type="text"" name="year" size="20" maxlength="60" value="<?php if (isset($_POST['year'])) echo $_POST['year']; ?>"  /></p>
    <p><input type="submit" name="submit" value="Update" /></p>
</form>
<?php include ('bookincludes/footer.html'); ?>

This is what If I try to change the ISBN got:

System error. We apologize for any inconvenience.

Query: UPDATE Books SET ISBN='978-1782175910' WHERE ISBN = 978-1782175919

If I tried to update the ISBN or the year but I get the message above. How can I fix this?

Answer 1

The query requires that text values are wrapped in quotes like this

$q = "UPDATE Books SET ISBN='$np' WHERE ISBN = '$row[0]'";   

Although I would look for a tutorial that uses parameterised and prepared queries rather than string concatenated queries to avoid SQL Injection

And any tutorial that suggests using the @ error silencing prefix should tell you the author has no idea what they are doing and should be avoided like the plague.

Answer 2

you seem to be missing single quotes on your where clause

 UPDATE Books SET ISBN='978-1782175910' WHERE ISBN = 978-1782175919

should be

 UPDATE Books SET ISBN='978-1782175910' WHERE ISBN = '978-1782175919'