mysqli update not updating eventhough stating successful?

Question

I am building a project and for some reason, I am unable to get my Update statement to actually update.

I have some input fields in a form which provide the details for the variables below.

Here's my code:

//update database

$stmt = $mysqli->prepare("UPDATE pages SET 
                    pg_name= ?, 
            pg_title = ?, 
            pg_keywords = ?,
            pg_description = ?,
            pg_header1 = ?,
            pg_header2 = ?,
            pg_maintext = ?,
            pg_active = ? WHERE id = ?");

        $stmt->bind_param('sssssssii', 
            $pg_name,
            $pg_title,
            $pg_keywords,
            $pg_description,
            $pg_header1,
            $pg_header2,
            $pg_maintext,
            $pg_active,
            $id);
        if($stmt->execute() === TRUE){
        $stmt->close();
            echo "Database successfully updated";
        } else {
            echo "There was a problem updating the database.";
        }

I have tested and the variables are being set from my form ok, and when I run the script , I get the 'success' message but check my database and nothings happened.

Anything I've missed? :)

Thanks for your help/

OK< existing form/ table below:

<form action="" method="post">
    <table>
        <tr>
            <td colspan="2"><?php echo "<span style='color: red; font-weight:     bold;'>".$errmsg."</span><br />"; ?></td>
        </tr>
        <tr>
            <td>Page Name:</td>
            <td><input type="text" name="pg_name" id="pg_name" value="<?php     if(isset($id)){ echo $page['pg_title']; }?>" /></td>
        </tr>
        <tr>
            <td>Page Title:</td>
            <td><input type="text" name="pg_title" id="pg_title" value="<?php     if(isset($id)){ echo $page['pg_title']; }?>" /></td>
        </tr>
        <tr>
            <td>Keywords:</td>
            <td><input type="text" name="pg_keywords" id="pg_keywords" value="    <?php if(isset($id)){ echo $page['pg_keywords']; }?>" /></td>
        </tr>
        <tr>
            <td>Description:</td>
            <td><input type="text" name="pg_description" id="pg_description"      value="<?php if(isset($id)){ echo $page['pg_description']; }?>"/></td>
        </tr>
        <tr>
            <td>Main page header:</td>
            <td><input type="text" name="pg_header1" id="pg_header1"  value="<?    php if(isset($id)){ echo $page['pg_header1']; }?>"/></td>
        </tr>
        <tr>
            <td>Subheader:</td>
            <td><input type="text" name="pg_header2" id="pg_header2" value="<?    php if(isset($id)){ echo $page['pg_header2']; }?>" /></td>
        </tr>
        <tr>
            <td>Page text</td>
            <td><textarea name="pg_maintext" id="pg_maintext"><?php     if(isset($id)){ echo $page['pg_maintext']; }?></textarea></td>
        </tr>
        <tr>
            <td>Active?</td>
            <td><select name="pg_active">
                <option value="1">Yes</option>
                <option value="0">No</option>
                </select></td>
        </tr>
        <tr>
            <td><input type="submit" name="submit" id="submit" value="<?php     if(isset($_GET['page_id'])){ echo "Update"; } else { echo "Add"; } ?>" /><?php     if(isset($_GET['page_id'])){ echo "<a href='pages.php' /><input type='button' name='new'     id='new' value='New' /></a>"; } ?></td>
            <td></td>
        </tr>
    </table>
</form>

I have tried the SQL commend in my database direct, and it works. Just doesnt work via this form. DB is connected and form is able to pull data from the database.

UPDATE: I've tried everything I an think of - nothing is working here. I've added error reporting to each step, and because it thinks nothing is wrong, no errors are flagging up! I have update access to the SQL as I use it all the time.

There is no function `PDOStatement::close()`. Probably not what's causing your error in the first place, but should still be corrected. — TheWolf, Sep 27 '13 at 08:27
Try running the same query in MySql with exact arguments and see if it succeeds. — Gurminder Singh, Sep 27 '13 at 08:28
@Anthony I would recommend you to use error handler for each step of your code [see here an example](http://stackoverflow.com/a/18971788/342740). You can see if `bind_param` was OK, if execute was OK, if the prepared query was OK. You can also use `$stmt->affected_rows` to check if the record was changed or not. — Prix, Sep 27 '13 at 09:17

score 0 · Answer 1 · answered Sep 27 '13 at 09:17

0

This is likely to be due to one of two problems.

-Either your $id is not matching an entry in your DB table or:

-Your mysql user does not have update priveleges.

Given what you said about the query working on the back end, the second option seems most likely.

answered Sep 27 '13 at 09:17

Robert Seddon-Smith

987
1
9
13

Really stuggling to solve this issue. No errors are flagging but still the table remains unchanged. – Anthony Sep 27 '13 at 17:09
RESOLVED - managed to discover that my $id wasn't pulling through and I needed to move it into the query. Thanks for everyones help! – Anthony Sep 27 '13 at 18:03

mysqli update not updating eventhough stating successful?

1 Answers1