UPDATE query doesn't update

[b.wickham]

I've been having trouble with an UPDATE statement in the code below. The problem is that each time I submit the form to update an existing record in the products table, the records won't update. When I submit the form there is no error message and it redirects to the correct URL, defined after query:

header("Location: " . $config_basedir . "glass/?id=" . $validentry);

I have tried using the preg_replace php function to replace any single quotes that may prematurely end the UPDATE command. This hasn't resolved the problem. Advice would be appreciated. Thanks!

<?php session_start();

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

    if(isset($_SESSION['USERNAME']) == FALSE) {
        header("Location: " . $config_basedir);
    }

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

    // 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 products 
            SET type_id =  " . $_POST['cat'] . ",
                title = '" . preg_replace("/'/","&#146;",$_POST['title']) . "',
                summary = '" . preg_replace("/'/","&#146;",$_POST['summary']) . "',
                body = '" . preg_replace("/'/","&#146;",$_POST['body']) . "'
            WHERE id = " . $validentry . ";";
        //run query
        $db->query($query);
        header("Location: " . $config_basedir . "glass/?id=" . $validentry);
    }
    else {
        require("../go/header.php");
        $fill_query = "SELECT *
            FROM products
            WHERE id = " . $validentry;
        $fill_result = $db->query($fill_query);
        $fill_row = $fill_result->fetch_assoc();
?>

<form action="<?php echo $SCRIPT_NAME ."../glass/?id=" . $validentry; ?>" method="post">
    <label>Title</label>
    <input type="text" name="title" value="<?php echo $fill_row['title']; ?>" class="site-input" />
    <label>Category</label>
    <select name="cat" class="site-input">
        <?php
            $cat_query = "SELECT *
                FROM products_type";
            $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>
    <textarea name="summary" class="summary site-input" rows="3"><?php echo $fill_row['summary']; ?></textarea>
    <label>Body</label>
    <textarea name="body" class="site-input" rows="25"><?php echo $fill_row['body']; ?></textarea>
    <input type="submit" name="submit" value="Update Entry" class="button" />
</form>

<?php

    }

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

[EvolutionFallen]

Haven't had a chance to go into your code much, but first off ditch the regex's and use mysqli_real_escape_string() instead: PHP Manual Entry

EDIT: Just to clarify, you can clean up your query and make it much easier to read --

if($_POST['submit']) {
    $typeid = $db->real_escape_string($_POST['cat']);
    $title = $db->real_escape_string($_POST['title']);
    $summary = $db->real_escape_string($_POST['summary']);
    $body = $db->real_escape_string($_POST['body']);
    $validentry = (int) $validentry; // make sure this is a number, nothing malicious

    // since your string is in double quotes you can just drop the variable names right in there.
    $query = "UPDATE products 
                SET type_id = $typeid,
                    title = '$title',
                    summary = '$summary',
                    body = '$body'
                WHERE id = $validentry";

                // continue script....

[mlesniak]

My first suggestion would be to add a debugging statement to just display the resulting sql query for the update before you try to execute it. It's surprising how easy it can be to spot the basic errors that way.

[b.wickham]

Ok, got it working now. Thanks for the help. I realized <form action="<?php echo $SCRIPT_NAME ."../glass/?id=" . $validentry; ?>" method="post"> on line 48 was pointing to another page. I changed it to <form action="<?php echo $SCRIPT_NAME ; ?>" method="post"> to point to the current page which is where the script is located. After this the UPDATE query worked like a charm! I also replaced the preg_replace() function with $db->mysqli_real_escape_string().