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?

Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Nov 16 '16 at 16:32
The `ISBN` (`$row[0]`) needs to be quoted. That also opens you to second level SQL injection. — chris85, Nov 16 '16 at 16:33
Also if you tutorial is suggestion you use the `@` error silencing character **DITCH THAT TUTORIAL NOW** All it is doing is teaching you BAD Habits — RiggsFolly, Nov 16 '16 at 16:36
Whats the books table look like? Is ISBN a primary key field? — TDWebDev, Nov 16 '16 at 16:40
@RiggsFolly Actually, my class Professor taught us to use the "@" after mysqli_query. — Young J, Nov 16 '16 at 16:42
**Ouch** Maybe you should be looking for another Professor. An error ignored is just something that will ___bite you in the backside later___ — RiggsFolly, Nov 16 '16 at 16:46
@TDWebDev Book_id goes first then ISBN, Author, Title, Year. — Young J, Nov 16 '16 at 16:46
@RiggsFolly only Professor teaching a intro to php this semester. So not an option. — Young J, Nov 16 '16 at 16:47
Then you should pass on our disgust at his/her lack of understanding — RiggsFolly, Nov 16 '16 at 16:48
Rather than `@` ignoring errors, you should be actively checking the resulting status after almost all `mysqli_` function calls — RiggsFolly, Nov 16 '16 at 16:49
@RiggsFolly I will also modify and get rid of the "@" in my code. Thank you. — Young J, Nov 16 '16 at 16:53

RiggsFolly · Accepted Answer · 2016-11-16T16:40:45.227

2

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.

edited Nov 16 '16 at 16:40

answered Nov 16 '16 at 16:33

RiggsFolly

93,638
21
103
149

Thank you, I have fixed it. – Young J Nov 16 '16 at 16:51

score 1 · Answer 2 · answered Nov 16 '16 at 16:34

1

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'

answered Nov 16 '16 at 16:34

Ralph Thomas Hopper

733
4
15

You have actually fixed the error message but not the code generating it – RiggsFolly Nov 16 '16 at 16:41
just thought it would be more graphical to highlight syntax on the result. :) your answer does push a little harder in the right direction for a better code structure. you have my vote :P – Ralph Thomas Hopper Nov 16 '16 at 16:44

Update function php

2 Answers2