Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    Jul 2013
    Location
    Finland/Estonia
    Posts
    53
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Question Using UPDATE in php query will fail

    Hello. I have this script, that is supposed to UPDATE database rows according to the ID of the post. When I click the submit button, it will in fact return the positive feedback (echo "Post edited in the database.";), but doesn't actually edit any rows in the database.

    The $db variable is the connection with the database.
    PHP Code:
    if(isset($_GET['editPost'])){
    $id2 $_GET['editPost'];
    }
    /*
    Some code that doesnt belong to this.
    */
    if(isset($_POST['submit_edit'])):
        
    $newTitle mysqli_real_escape_string($db$_POST['newTitle']);
            
    $newPost mysqli_real_escape_string($db$_POST['newPost']);
                
    $newPostPreview mysqli_real_escape_string($db$_POST['newPostPreview']);
            
    $newCategory mysqli_real_escape_string($db$_POST['newCategory']);
            if(!empty(
    $newTitle))
                if(!empty(
    $newPostPreview)){
                    include(
    '../includes/db_connect.php');
                    if(isset(
    $_GET['editPost'])){$the_super_real_id $_GET['editPost'];}
                    global 
    $id2;
                    
    $query_edit $db->query("UPDATE ´posts´ SET ´title´ = '$newTitle', ´body´ = '$newPost', ´category_id´ = '$newCategory', ´post_preview´ = '$newPostPreview' WHERE ´post_id´ = '$id2'");
                    if(
    $id2 NULL){$id2 "NULL";}
                    echo 
    "ID:",$id2;
        
    //$query_edit = $db->query("");
        
    if($query_edit){
                echo 
    "Post edited in the database.";
            }else{
            echo 
    "Error in the query.";    
            }
        }
        endif; 
    I'm using the SQL UPDATE for the first time, but I've got some experience with PHP & MySQL by now, but I've tried editing different parts of this snippet with no success.
    The forms are indeed working, because I use the same forms on the page normally with no issues. I use this page as a form landing page and a "normal" page.
    This is what the URL looks like: /newpost.php?editPost={dynamic id here}
    Thanks!
    Last edited by estXcrew; 11-12-2013 at 08:18 PM. Reason: updating snippets

  • #2
    New Coder
    Join Date
    Jul 2013
    Location
    Finland/Estonia
    Posts
    53
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Updated snippets, hopefully someone can help me with this!

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    The value being assigned to $query_edit simply tells you whether the query was able to run. It doesn't tell you how many rows were updated - it could be any number from 0 upward.

    Replace

    if($query_edit){

    with

    if ($db->affected_rows){

    to get the message to display ony if one or more rows are actually updated.


    That doesn't answer the question of why the update doesn't update anything but it does fix it so that it will only say it has updated when it actually has done.
    Last edited by felgall; 11-12-2013 at 09:08 PM.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #4
    New Coder
    Join Date
    Jul 2013
    Location
    Finland/Estonia
    Posts
    53
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by felgall View Post
    The value being assigned to $query_edit simply tells you whether the query was able to run. It doesn't tell you how many rows were updated - it could be any number from 0 upward.

    Replace

    if($query_edit){

    with

    if ($db->affected_rows){

    to get the message to display ony if one or more rows are actually updated.


    That doesn't answer the question of why the update doesn't update anything but it does fix it so that it will only say it has updated when it actually has done.
    Thanks... It's something But I'd like it so the error message wouldn't be required 100% of the time, if anyone has any suggestions, they are all welcome. I've made changes to this part of the page for 2 days and I still haven't figured it out.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    DEBUG DEBUG DEBUG DEBUG DEBUG

    Kill:
    Code:
    $query_edit = $db->query("UPDATE ´posts´ SET ´title´ = '$newTitle', ´body´ = '$newPost', ´category_id´ = '$newCategory', ´post_preview´ = '$newPostPreview' WHERE ´post_id´ = '$id2'");
    Try:
    Code:
    $sql = "UPDATE ´posts´ SET ´title´ = '$newTitle', ´body´ = '$newPost', ´category_id´ = '$newCategory', ´post_preview´ = '$newPostPreview' WHERE ´post_id´ = '$id2'";
    echo "DEBUG SQL: " . $sql . "<hr/>";
    $query_edit = $db->query($sql);
    What does the debug output show you? What happens when you copy/paste that debug output into a query tool?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    And a *HUGE* hint: The apostrophe character ' is *NOT* the same as the back-tick ` character.

    And neither of them are the same as the funky word processing ´ character that you are using.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Jul 2013
    Location
    Finland/Estonia
    Posts
    53
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    And a *HUGE* hint: The apostrophe character ' is *NOT* the same as the back-tick ` character.

    And neither of them are the same as the funky word processing ´ character that you are using.

    And I thought using back-ticks around database objects was a good practice? And Strings/variables required a '
    EDIT1:
    Didn't realize I used the wrong kind of ticks. I've removed the ´s.
    EDIT2:
    Okay.. The ID in the query remains empty. The ID is set in the URL and is required with the $_GET variable/statement. Weird.
    Last edited by estXcrew; 11-13-2013 at 07:06 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    Using back ticks is a horrible hack that is only needed because you named a database object wrong: You either used a keyword or the name contains illegal characters. I, personally, have not had to use a back tick in my queries any time in the last 3 years, at least. Yes, you can use them, just to be safe, but if you are smarter you will make sure you don't need to use them.

    *********
    Since you are doing
    $id2 = $_GET['editPost'];
    it should be trivial to verify that editPost is being passed in the query string.

    Just look at the address bar of your browser: It should cointain something like
    http://www.yoursite.com/yourpage.php?editPost=773

    If it doesn't, then of course you have a bug in the the HTML that is posting that URL.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    Jul 2013
    Location
    Finland/Estonia
    Posts
    53
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Using back ticks is a horrible hack that is only needed because you named a database object wrong: You either used a keyword or the name contains illegal characters. I, personally, have not had to use a back tick in my queries any time in the last 3 years, at least. Yes, you can use them, just to be safe, but if you are smarter you will make sure you don't need to use them.

    *********
    Since you are doing
    $id2 = $_GET['editPost'];
    it should be trivial to verify that editPost is being passed in the query string.

    Just look at the address bar of your browser: It should cointain something like
    http://www.yoursite.com/yourpage.php?editPost=773

    If it doesn't, then of course you have a bug in the the HTML that is posting that URL.
    Thanks for the information on back-ticks.
    It is in the URL. However it is not getting it from the URL For some reason.
    This is the URL: MySite.com/admin/newpost.php?editPost=119
    To test if the query worked now or not i tried with a static ID and it worked, but it's not getting the ID with $_GET from URL.

    This is what's supposed to fetch it:
    PHP Code:
    if(isset($_GET['editPost'])){
    $id2 $_GET['editPost'];

    It is on the top of the page, so I need to refer to it as global $id2;, otherwise it will not find it.
    Last edited by estXcrew; 11-14-2013 at 07:08 PM.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,687
    Thanks
    80
    Thanked 4,655 Times in 4,617 Posts
    Well, at this point it's a PHP problem, clearly, nothing to do with MySQL. And I don't use PHP, so I'll leave it to others to help you with the PHP.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New Coder
    Join Date
    Jul 2013
    Location
    Finland/Estonia
    Posts
    53
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Smile

    Quote Originally Posted by Old Pedant View Post
    Well, at this point it's a PHP problem, clearly, nothing to do with MySQL. And I don't use PHP, so I'll leave it to others to help you with the PHP.
    Thanks for your help.. Reposting in PHP Board


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •