www.webdeveloper.com
Results 1 to 7 of 7

Thread: [RESOLVED] MySQL UPDATE WHERE NOT EQUAL not working

  1. #1
    Join Date
    Dec 2011
    Posts
    163

    resolved [RESOLVED] MySQL UPDATE WHERE NOT EQUAL not working

    I am currently finishing up part of my theme manager page, but have hot a roadblock

    My code is follows...

    PHP Code:
    <?php
        
    if($_POST['submit']){
            
    $name mysqli_prep($_POST['name']);
            
    // SET PREVIOUSLY ACTIVE THEME TO INACTIVE
            
    $sql "UPDATE themes SET
                    active = 0
                    WHERE (active = 1 AND name != 
    $name)
                    LIMIT 1"
    ;
            
    $result mysqli_query($connect$sql);
            echo 
    "Confirming Inactive Theme...\n";
            
    confirm_query($result);
            if(
    $result){
                echo 
    "Theme Unselected";
            }
            
    // SET SELECTED THEME TO ACTIVE
            
    $sql "UPDATE themes SET
                    active = 1
                    WHERE name = 
    $name
                    LIMIT 1"
    ;
            
    $result mysqli_query($connect$sql);
            echo 
    "Confirming Active Theme...\n";
            
    confirm_query($result);
            if(
    $result){
                echo 
    "Theme Selected";
            }
            
    //redirect_to("/home/admin/themes.php");
        
    }
    ?>
    I get the following in the browser...

    Code:
    Confirming Inactive Theme... Database query failed: Unknown column 'Easter' in 'where clause'
    I have no clue what's going on, I must have missed something, but can't figure it out.

  2. #2
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    986
    Out of curiosity, perhaps you should echo the $sql variable just to see what the full query that is being processed ends up looking like. Also, I'm not really an expert on SQL, but I don't see why you need the parenthesis around your WHERE clause. It shouldn't be needed at all, but I can't say it's causing your issue. In either case I'd pull it off and give the query another whirl.
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  3. #3
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    817
    Is $name an integer or a string? If the field it is being matched to is not a numeric one, you need quotes around $name.

  4. #4
    Join Date
    Dec 2011
    Posts
    163
    $name is a string

    echoed $sql

    This is what happened after I put the LIMIT 1 in it...

    Code:
    UPDATE themes SET active = 0 WHERE (active = 1 AND name != Default) LIMIT 1
    Confirming Inactive Theme... 
    Database query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') LIMIT 1' at line 3
    Taking away parenthesis doesn't change anything.
    Last edited by Dragonfire2008; 03-31-2014 at 01:19 PM.

  5. #5
    Join Date
    Mar 2005
    Location
    Behind you...
    Posts
    986
    But now after looking at your SQL, you should follow ginerjm's note on putting quotations around your $name value since it is a string. As you can see in your query you are checking "name != Default" which is invalid. It should read "name != 'Default'".
    "Given billions of tries, could a spilled bottle of ink ever fall into the words of Shakespeare?"

  6. #6
    Join Date
    Dec 2011
    Posts
    163
    Huh, don't remember having that problem, but anyways that did it! Thanks.

  7. #7
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    817
    If you are using normal queries (as in, not 'prepared') you must quote any argument that is not a number.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles