MySQL database won't UPDATE specific records

[jonathan.flesher]

I'm new to MySQL and following a tutorial in a book to design a PHP MySQL website. Following this tutorial, I created a PHP page from which I can update entries in the MySQL database. What's strange is that only specific entries in the database will not update, while others (the majority) will update without any trouble. I can update all entries using PHP MyAdmin. Just curious, has anyone run into this issue before?

[JorgeM]

It could be something related to the content in your statement. Note if you have any apostrophe's or other characters that would be intrepretted by PHP as the end of the SQL statement. You may want to post your MySQL and PHP code to take a look.

[LastMitch]

@jonathan.flesher

I'm new to MySQL and following a tutorial in a book to design a PHP MySQL website. Following this tutorial, I created a PHP page from which I can update entries in the MySQL database. What's strange is that only specific entries in the database will not update, while others (the majority) will update without any trouble. I can update all entries using PHP MyAdmin. Just curious, has anyone run into this issue before?

It really would be helpful if you can post your query and your php code to see the whole issue.

[jonathan.flesher]

Here's the code for the page I'm using to UPDATE the MySQL records. As defined in ../includes/config.php: $host = "localhost", $dbuser = "root", $dbpassword = "", $dbdatabase = "itiglass.com".

<?php session_start();

    require("../includes/config.php");

    // connect to server, select database
    $db = new mysqli($host,$dbuser,$dbpassword,$dbdatabase);

    if(isset($_SESSION['USERNAME']) == FALSE) {
        header("Location: " . $config_basedir);
    }
    // validation
    if(isset($_GET['id']) == TRUE) {
        if(is_numeric($_GET['id']) == FALSE) {
            $error = 1;
        }
        if($error == 1) {
            header("Location: " . $config_basedir);
        }
        else {
            $validentry = $_GET['id'];
        }
    }
    else {
        $validentry = 0;
    }
    // process form
    if($_POST['submit']) {
        $query = "UPDATE entries
            SET cat_id =  " . $_POST['cat'] . ",
            dateedited = NOW(),
            title = '" . $_POST['title'] . "',
            summary = '" . $_POST['summary'] . "',
            body = '" . $_POST['body'] . "'
            WHERE id = " . $validentry . ";";
        //run query
        $db->query($query);
        header("Location: " . $config_basedir . "publications/viewentry.php?id=" . $validentry);
    }
    else {
        require("../includes/header.php");
        $fill_query = "SELECT *
            FROM entries
            WHERE id = " . $validentry . ";";
        $fill_result = $db->query($fill_query);
        $fill_row = $fill_result->fetch_assoc();
?>

<form action="<?php echo $SCRIPT_NAME . "?id=" . $validentry;?>" method="post">
    <label>Title</label>
    <input type="text" name="title" value="<?php echo $fill_row['title']; ?>">
    <label>Category</label>
    <select name="cat">
        <?php
            $cat_query = "SELECT *
                FROM categories";
            $cat_result = $db->query($cat_query);
            while($cat_row = $cat_result->fetch_assoc()) {
                echo "<option value='" . $cat_row['id'] . "'";
                if($cat_row['id'] == $fill_row['cat_id']) {
                    echo "selected";
                }
                echo ">" . $cat_row['name'] . "</option>";
            }
        ?>
    </select>
    <label>Summary</label>
    <input type="text" name="summary" value="<?php echo $fill_row['summary']; ?>">
    <label>Body</label>
    <textarea name="body"><?php echo $fill_row['body']; ?></textarea>
    <input type="submit" name="submit" value="Update Entry">
</form>

<?php }

require("../includes/footer.php"); ?>

[jonathan.flesher]

I have spent a bit of time looking into whether the content could be distrupting the code, but I don't see any problems with the records that won't update compared to the records that do update.

[jonathan.flesher]

I have spent a bit of time looking into whether the content could be distrupting the code, but I don't see any problems with the records that won't update compared to the records that do update.

[LastMitch]

@jonathan.flesher

I have spent a bit of time looking into whether the content could be distrupting the code, but I don't see any problems with the records that won't update compared to the records that do update.

Did you write this code?

On this line 48:

<form action="<?php echo $SCRIPT_NAME . "" . $validentry; ?>" method="post">

Change it to

<form action="<?php echo $SCRIPT_NAME; ?>" method="post"> 

This header doesn't make any sense:

header("Location: " . $config_basedir . "publications/viewentry.php?id=" . $validentry);

What is in $validentry?

[b.wickham]

@LastMitch

Just realized I've been posting under jonathan.flesher by accident. (My friend left his Facebook logged in on my computer and I didn't realize it until now! I use my Facebook login to access Daniweb.)

Thanks for the replies. But I'm still having the same problem after making the change you suggested. Specific records in the MySQL "entries" table will not update.

Did you write this code?

The code comes straight from the tutorial I'm following.

$validentry is defined on line 20:

$validentry = $_GET['id'];

It stores the id from the MySQL table (named "entries") of the entry that is being edited or viewed. So in the header, after the user clicks the submit button, it directs the user to view the entry that they just edited. That part of the code works. (Even if the record does not update, it still directs the user to view correct entry after clicking submit. It just hasn't been changed.) There are specific id's that will not UPDATE and they appear to be random, such as id 4, id 5, id 7, id 8, id 10, id 11.

[LastMitch]

@b.wickham

That part of the code works. (Even if the record does not update, it still directs the user to view correct entry after clicking submit. It just hasn't been changed.) There are specific id's that will not UPDATE and they appear to be random, such as id 4, id 5, id 7, id 8, id 10, id 11.

OK, the sound like the db works and it does redirected.

$validentry = $_GET['id'];

Since you are using a form you $_POST the data. Instead your $validentry = $_GET['id']; is to $_GET the id so it won't $_POST the id correctly.

On line 45:

$fill_row = $fill_result->fetch_assoc();

This is where you fetch the id from $validentry

But in your form I don't see $validentry being part of the form.

Try to include this line in the form:

<input type="hidden" name="id" value="<?php echo $validentry;?>" /> 

Regarding about the query not Update.

On line 27 to line 38 instead of this:

if($_POST['submit']) { $query = "UPDATE entries SET cat_id = " . $_POST['cat'] . ", dateedited = NOW(), title = '" . $_POST['title'] . "', summary = '" . $_POST['summary'] . "', body = '" . $_POST['body'] . "' WHERE id = " . $validentry . ";";
//run query
$db->query($query);
 header("Location: " . $config_basedir . "publications/viewentry.php?id=" . $validentry);
}

Change it to this:

if($_POST['submit']) {
$query = "UPDATE entries SET cat_id = " . mysqli_real_escape_string($_POST['cat']) . ", dateedited = NOW(), title = '" . mysqli_real_escape_string($_POST['title']) . "', summary = '" . mysqli_real_escape_string($_POST['summary']) . "', body = '" . mysqli_real_escape_string($_POST['body']) . "' WHERE id = " . $validentry . ";";
//run query
$query->real_escape_string($db,$query);
header("Location: " . $config_basedir . "publications/viewentry.php?id=" . $validentry);
}

Let me know how it goes.

[b.wickham]

@LastMitch

mysqli_real_escape_string() returned the following error: "expects exactly two parameters" on the all the lines where the function appears.

However, I did run across another function: preg_replace(). This is working for my application. I use the preg_replace() function to look up single quote characters and replace them with ' This has resolved the issue -- as suggested by @JorgeM.

Note if you have any apostrophe's or other characters that would be intrepretted by PHP as the end of the SQL statement.

The only problem I see with this: I'm sure another character could interrupt the query. What do you think?