what can make an update query not to update but return success

Question

I have this class am using to perform queries - insert, delete, drop create etc, but this time i created a method to update a table when the update have been submitted and to my surprise and hours of headache it is return success but not actually updating the record in the database am so confused, I have been debugging for hours to no avail so i decided to share my worries to see if i can receive help as am actually 2 weeks old In OOP PHP

so here my class

   class queryClass extends MYSQL{ //MYSQL is for connecting to database 
        //table fields
        var $user_table = '';          //table names that will be used in all names, each query method will input its own table name

        //connect to database
            function dbconnect(){
                 MYSQL::dbconnect();
            }
          //prevent injection
       function qry($query) {
          $this->dbconnect();
         $args  = func_get_args();
          $query = array_shift($args);
          $query = str_replace("?", "%s", $query);
          $args  = array_map('mysql_real_escape_string', $args);
          array_unshift($args,$query);
          $query = call_user_func_array('sprintf',$args);
          $result = mysql_query($query) or die(mysql_error());
              if($result){
                return $result;
              }else{
                 $error = "Error";
                 return $result;
              }
             //update quote function
    function updatequote($table, $message1, $message2, $column_name1, $column_name2, $column_name3, $quote_id){
         $this->dbconnect();
         $this->quote_id = $quote_id; 
        echo $message1, $message2;

        //make sure table name is set
          $this->user_table = $table;
          $this->column_name1 = $column_name1;
           $this->column_name2 = $column_name2;
         $this->column_name3 = $column_name3;

        //execute login via qry function that prevents MySQL injections
        $result = $this->qry("UPDATE ".$this->user_table." SET ".$this->column_name2."='?', ".$this->column_name3."='?'
        WHERE ".$this->column_name1."='?';", $message1, $message2, $this->quote_id );
       // $result = mysql_query("INSERT INTO ".$this->user_table."(username, password) VALUES('$username', '$password')");
      if($result){
     $_SESSION['success'] = "The Update Was Successfully Saved";
       header('location: edit_quotes.html');

       exit();
        return true; 
    }else{
      $_SESSION['success'] = "The Update Was Not Saved".mysql_error();
       header('location: edit_quotes.html'); 
       exit();   //do something on FAILED login
        return false;
    }
        }

         //quote form
        function quoteEditorform($formname, $formclass, $formaction, $helptext, $first, $second){
            //conect to DB
            $this->dbconnect();

            echo"
    <form name=\"$formname\" method=\"post\" id=\"$formname\" class=\"$formclass\" enctype=\"application/x-www-form-urlencoded\" action=\"$formaction\">

    <h2>$helptext</h2>
    <div><label for=qoute>NGWA QUOTE 
    <input type=button value='Quote' onclick=\"wrapInTags(this.form.message1,'quote')\">insert [quote].[/quote]tags
     </label>
    <textarea name=\"message1\" cols=\"40\" rows=\"4\" onclick=\"copySelection(this)\">$first</textarea><br>
    </div>
    <div><label for=\"qoute\">ENGLISH MEANING
    <input type=button value='Meaning' onclick=\"wrapInTags(this.form.message2,'meaning')\">
    insert [meaning].[/meaning]tags
    </label>
    ".$record['meaning']."
    <textarea name=\"message2\" cols=\"40\" rows=\"4\" onclick=\"copySelection(this)\">$second</textarea></div>
    <input name=\"action\" id=\"action\" value=\"sendeditedquote\" type=\"hidden\">
    <div>

    <input name=\"submit\" id=\"submitV value=\"Save\" type=\"submit\"></div>

    </form>
    <div align=\"center\"><a href=\"edit_quotes.html?do=read_bb_codes\">Read Before Posting</a></div>
    ";    }     

        function createquotetable($tablename){
        //connect to DB
        $this->dbconnect();
        $qry = "CREATE TABLE IF NOT EXISTS ".$tablename."(
    quote_id        INT(8) NOT NULL AUTO_INCREMENT,
    ngwaquote           TEXT NOT NULL,
    meaning         TEXT NOT NULL,
    saved_date      date, 
    PRIMARY KEY (quote_id)
    ) TYPE=INNODB
        ";
        $result = $this->qry($qry);
            return;
        }

here's my quote-editor.html after including my class files

 // instantiate all other needed classes
$cleaner = new cleanPost();
$connect = new MySQL();
$connect->dbconnect();// connect to a database
$bbcode = new BBCode(); 
$log = new logmein();

    if($_REQUEST['action'] == "sendeditedquote"){
  //post all the values to the database using our main class

/*topic field checking */
        if($_REQUEST['message1'] == "" || $_REQUEST['topic'] > 600) {
        $errmsg_arr[] = 'Sorry You Can\'t Send An Empty Qoute OR quote greater than 500 characters at a time';
        $errflag = true;
                                                                    }                                           

        if($_REQUEST['message2'] == "" ) {
        $errmsg_arr[] = 'Sorry You Can\'t Update With An Empty Qoute';
        $errflag = true;
                                        }

            //If there are input validations, redirect back 
    if($errflag) {
        $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
        session_write_close();
        header("location: edit_quotes.html");
        exit();
                 }
 $log->updatequote("quotes", $_REQUEST['message1'], $_REQUEST['message2'], "quote_id", "ngwaquote", "meaning", $cleaner->clean($_GET['quote_id']));

}

ai'ght when i perform the query the success/error line returns that the update was successful but on the other page where i display all available quotes the particular quote still is NOT updated

Anyone who's experienced such please tell me what am gon' do.

BEING ASKED THE LINE FOR THE RAW QUERY HERE IS IT- first is the the method that cleanse ouy my post and the I use it for query using $this->qry(somequeries here)

function qry($query) {
              $this->dbconnect();
             $args  = func_get_args();
              $query = array_shift($args);
              $query = str_replace("?", "%s", $query);
              $args  = array_map('mysql_real_escape_string', $args);
              array_unshift($args,$query);
              $query = call_user_func_array('sprintf',$args);
              $result = mysql_query($query) or die(mysql_error());
                  if($result){
                    return $result;
                  }else{
                     $error = "Error";
                     return $result;
                  }

     //update quote function using $this->qry()
        function updatequote($table, $message1, $message2, $column_name1, $column_name2, $column_name3, $quote_id){
             $this->dbconnect();
             $this->quote_id = $quote_id; 
            echo $message1, $message2;

            //make sure table name is set
              $this->user_table = $table;
              $this->column_name1 = $column_name1;
               $this->column_name2 = $column_name2;
             $this->column_name3 = $column_name3;

            //execute login via ****qry function**** that prevents MySQL injections
            $result = $this->qry("UPDATE ".$this->user_table." SET ".$this->column_name2."='?', ".$this->column_name3."='?'
            WHERE ".$this->column_name1."='?';", $message1, $message2, $this->quote_id );
           // $result = mysql_query("INSERT INTO ".$this->user_table."(username, password) VALUES('$username', '$password')");
          if($result){
         $_SESSION['success'] = "The Update Was Successfully Saved";
           header('location: edit_quotes.html');

           exit();
            return true; 
        }else{
          $_SESSION['success'] = "The Update Was Not Saved".mysql_error();
           header('location: edit_quotes.html'); 
           exit();   //do something on FAILED login
            return false;
        }
            }

Use mysql_affected_rows() to find affected rows. http://php.net/manual/en/function.mysql-affected-rows.php — Riz, Oct 27 '11 at 07:31
`mysql_affected_rows()` is returning `0` meaning no row is affected by the update - exactly what I was saying — Normal9ja, Oct 27 '11 at 07:54

score 1 · Answer 1 · edited May 23 '17 at 11:47

If the where clause of your update statement does not match any rows, the update statement will return success.
However it will not change anything.
Note that MySQL knows when a value has not really changed so the statement

UPDATE table1 SET col1 = 0 WHERE col1 = 0

Will always return 0 for the number of affected rows.

If you want to know if anything has been changed you need to call:

$rows_updated = mysql_affected_rows($this->connection);
or
$rows_updated = mysqli_affected_rows($this->connection);  //if you're using mysqli

An update statement will only indicate failure is an error has occured.

A warning about SQL-injection
I notice that you use dynamic table and column names.
If those values are in any way alterable by a user or pass through superglobals that can be affected by another php session that can be affected by a user, you have an SQL-injection hole.

Here's how to secure yourself against that: How to prevent SQL injection with dynamic tablenames?

score 1 · Accepted Answer · answered Oct 27 '11 at 17:15

I think i have found the answer to my problem In the place i had the $this->quote_id i later figured out that the page editor url was editor.html?quote_id=1 then when I submitted it will now process the form on a flat url === editor.html so my mistake was that I didn't request for the QUOTE ID when i was still on the editing url editor.html?quote_id=1 instead requesting for it when it was not possible ie in editor.html so it was meant to return empty quote id which i used to update thereby resulting in update success but not really updating anything so all I did was add an input tag hidden to get the quote_id being edited and then post it along with the rest of the form

So simple but took me me hours of rereading and re coding, wonderful, small things cause much frustration

Thanks all

Congrats on the solution! When you are able, please make sure to mark your answer as 'accepted' so that others might learn from your success. Cheers~ — Andrew Kozak, Dec 22 '11 at 01:50

score 0 · Answer 3 · answered Oct 27 '11 at 14:13

0

if the fields you are updating is not the same has the fields in the database, it will not update. although it return success simple means that it sees the table and connect to the database

answered Oct 27 '11 at 14:13

user836910

484
2
9
22

what can make an update query not to update but return success

3 Answers3