#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Question Trouble differentiating return value from mysqli udpate query


    Hi all,

    I'm testing an UPDATE query, which works fine when Col1 is not already equal to $var1 and WHERE Col2 = $var2 AND Col3 = $var3.

    If I test where Col2 != $var2 and/or Col3 != $var3...I get affected_rows = 0.
    If I test with Col1 already equal to $var1...I get affected_rows = 0.

    Which of course makes sense.

    However, what I'd like to do, is tell the difference between a Col1 already equal to $var1 update attempt...and a Col2 != $var2 and/or Col3 != $var3.
    In this case, the WHERE clause failing is a much more critical issue (actually, viewed as an error) than simply Col1 already equal to $var1.

    How do I differentiate between those two failed update attempts?
    I realize that the actual mysqli_query was sucessful, but the update wasn't (i.e. no update occured - for different reasons).

    Note: the results for mysqli_error($link) is always blank in each case, and mysqli_errno($link) is always 0 (zero) in each case. Also, the echo "here" doesn't occur.

    PHP Code:
    $sqlUpdate mysqli_query($link"UPDATE table SET Col1 = '$var1' WHERE Col2 = '$var2' AND Col3 = '$var3' LIMIT 1") or exit ("Error updating."); //This or exit is only for testing
        
    if (mysqli_affected_rows($link) == 0) {
        
    $result "<font color=red><strong>Error: Update was not successful.  Col1 was already same as $var1</strong></font><br>" ;
    } elseif (
    mysqli_affected_rows($link) == -1) {  //This doesn't ever seem to happen
        
    $result "<font color=red><strong>Error: Update was not successful.  Mismatch between Col2 and $var2 and/or Col3 and $var3</strong></font><br>" ;
    } else {
        
    $result "<font color=#006600><strong>Success Updating.</strong></font><br>" ;    
    }

    echo 
    "error: ".mysqli_error($link)."<br>";
    echo 
    "error no: ".mysqli_errno($link)."<br>";
    $errorList mysqli_error_list($link);
    echo 
    "error list: ".$errorList."<br>";
    if (
    $sqlUpdate === "false") {
        echo 
    "here";

    Edit: Also, when I try to echo "error list: " I get this error
    Call to undefined function mysqli_error_list()
    Last edited by we5inelgr; May 30th, 2013 at 03:17 PM.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    a 30 seconds Google "research" reveals the CLIENT_FOUND_ROWS flag, which can be specified when using mysqli_real_connect() instead of mysqli_connect().

    What's wrong with the mysqli_error_list() I don't know. But what I do know is that you should learn about prepared statements. Injecting variables into query strings is dead.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,128
    Rep Power
    9398
    mysqli_error_list() is only available in PHP 5.4+.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    lol. thanks for the reply. I had done about a 60 minute google search and didn't come across those solutions you mentioned. I did find, however, all of those "checks" that I listed in my OP. After determining what I found wasn't cutting it, I came here

    I've since come across mysqli_info ($link)

    and am using it this way with intended outcomes from testing so far:

    PHP Code:
    $query_info mysqli_info ($link);
    list(
    $matched$changed$warnings) = sscanf($query_info"Rows matched: %d Changed: %d Warnings: %d");

    if (
    $matched == "1" && $changed == "1" && $warnings == "0") {
        
    $result "Success" ;
    } elseif (
    $matched == "1" && $changed == "0" && $warnings == "0") {
        
    $result "Warning: Update was not successful.  Col1 was already set to $var1;
    } elseif (
    $matched == "0" && $changed == "0" && $warnings == "0") {
        
    $result "Error: Update was not successful.  Data provided did not match the record." ;
    } elseif (
    $warnings != "0") {
        
    $result "Warning ($warnings): Update was not successful." ;
    } else {
        
    $result "Error: Update was not successful." ;

    Question becomes...which method is better overall? I'm leaning towards mysqli_real_connect() because I'm thinking there is a greater chance (relatively speaking) of the output of mysqli_info changing format than of mysqli_real_connect() being depricated. However, if I change from mysqli_connect() to mysqli_real_connect(), that will affect all of my other db queries that are currently based on mysqli_connect()? Or, would that change be transparent?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Originally Posted by requinix
    mysqli_error_list() is only available in PHP 5.4+.
    Ah, that explains that error. Thanks!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Originally Posted by Jacques1
    But what I do know is that you should learn about prepared statements. Injecting variables into query strings is dead.
    And yeah, I know about stored procedures while using MS SQL/C#,VB.

    I need to get the basic functionality of the pages done before converting over to prepared statements.
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by we5inelgr
    I need to get the basic functionality of the pages done before converting over to prepared statements.
    Why? Why do the job twice and maybe mess up the conversion? Why not write correct code from the beginning?

    As to the mysqli_info(): This is a hack rather than an actual solution. Scraping data from messages is sometimes necessary, but it's inefficient, unrealiable (like you already said) and simply the wrong approach. Messages like this are meant for a human sitting in front of the console, they're not meant for applications to read them.

    So definitely go with "real connect". Both mysqli_connect() and mysqli_real_connect() eventually call the same library function. As far as I can tell, the only difference is that you can specify those additional flags.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo