SQL statement works in a query but not in a PHP script

Discussion in 'PHP' started by tyler_durden, Nov 16, 2010.

Thread Status:
Not open for further replies.
  1. #1
    The below code runs just fine in the SQl console. Non "pagetype" itmes are not updated as designed.
    UPDATE expire as A 
    LEFT JOIN content_type_pagetype as C ON A.nid = C.nid 
    LEFT JOIN node as N ON A.nid = N.nid 
    
    SET A.expire = C.field_endtime_value 
    WHERE N.type = 'pagetype' 
    Code (markup):

    When I run it through a PHP script like below, it changes the expire field on ALL items that are not 'pagetype' to '0'.
    <?php
        $conn = mysql_connect('localhost','password','username');                             // make an sql connection
    
        $db = mysql_select_db('dbname',$conn);                                     // navigate to db
        $query = "UPDATE expire AS A ";                                                    // update autoexpire
    
        $query .= "LEFT JOIN content_type_pagetype AS C ON A.nid=C.nid "; 
    
        $query .= "LEFT JOIN node AS N ON A.nid=N.nid ";                            
        $query .= "SET A.expire = C.field_endtime_value ";                            // set expire to end time
    
        $query .= "WHERE N.type='pagetype' "; 
    
        $updatedb = mysql_query($query); 
    
    ?>
    Code (markup):
    I have tried this line 3 ways:
        $query .= "WHERE N.type='pagetype' "; 
        $query .= "WHERE N.type="pagetype" "; 
        $query .= "WHERE N.type=\"pagetype\" "; 
    Code (markup):
    What am I doing wrong?
     
    tyler_durden, Nov 16, 2010 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,901
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #2
    echo out your sql and see if something has come unstuck

    and run the query like this

    $updatedb = mysql_query($query) or die(mysql_error().'<br>'.$query; 
    Code (markup):
    that way you discover what the error is and that should help. Post your results :)
     
    sarahk, Nov 16, 2010 IP
  3. tyler_durden

    tyler_durden Peon

    Messages:
    340
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks for the help, but it would help if you run the correct file. I can't believe I was doing this all morning??? It's working just fine.
     
    tyler_durden, Nov 16, 2010 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,901
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #4
    Bugger... but don't worry, we all have those moments.
     
    sarahk, Nov 16, 2010 IP
Thread Status:
Not open for further replies.