Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Question Unanswered: PHP and Postgres - db query problem

    I have recently migrated from MySQL to Postgresql (v8.4).

    I have just run into a bug where my current PHP code will not return a value from a database query. The same code works fine using MySQL.

    This is my database connection script. The variable values are stored in the application config file:

    Code:
    // Fire up the database library
            $this->db = NewADOConnection(DATABASE_SOFTWARE);
            $this->db->debug = DEBUG_DATABASE;
    
            $this->db->Connect(DB_LOCATION, DB_ACCOUNT, DB_PASSWORD, DB_DATABASE);
    The sql query looks like this:

    Code:
    $myMACADDRESS = $this->db->qstr( strtolower( $this->x['macAddress']) );
    
    $sql = "SELECT * FROM device WHERE macAddress = $myMACADDRESS";
    $results = $this->db->Execute($sql);
    consumerId is a field in the device table, therefore when I look for the value of $results->fields['consumerId'], I should get the id number 2 (there is only 1 record in this table so I know what the value should be)

    Code:
    $this->x['consumerId'] = $results->fields['consumerId'];
    When I added a log file to see the output for $result it returned the following:

    Code:
    deviceid,consumerid,devicetype,vemgid,shortdescription,macaddress,devicehash,ipaddresstv,ipaddresshome,ipaddresspublic,publicport,df,dfmax,lastseenby,lastswversion,status,createddate,lastupdate
    15,2,2,1,Chantelle,00:0d:48:30:0b:75,723148661,192.168.5.38,192.168.5.99,,,135162524,152648512,XMPP1,,1,1249478213,1263829535
    So why am I NOT getting a value when this works in MySQL. This shouldn't be a postgres issue, or is it? The only value that I do get back, from that entire table is the macAddress (which was condition on the query).

    This is what I add the following code (after the sql execution):

    Code:
    $this->log( LOG_DEBUG, "WATCHME", "WATCHME:  X Structure: " . print_r(
    $this->x, true) );
    Result:

    Code:
    WATCHME - WATCHME:  X Structure: Array
    (
        [deviceConsumer] =>
        [deviceId] => 0
        [consumerId] => 0
        [deviceType] => 0
        [vemgId] => 0
        [shortDescription] =>
        [macAddress] => 00:0D:48:30:0B:75
        [deviceHash] =>
        [ipAddressTV] =>
        [ipAddressHOME] =>
        [ipAddressPUBLIC] =>
        [publicPort] => 0
        [df] => 0
        [dfmax] => 0
        [lastSeenBy] =>
        [lastSwVersion] =>
        [status] => 0
        [lastUpdate] => now()
        [createdDate] => now()
        [searchResult] => 0
        [vemg] => Array
            (
            )

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    You have posted a lot of PHP code but this is a forum for SQL problems...

    Please post (and test) the actual query that gets executed (e.g. print it out before execution).

    Edit: you should also include the CREATE TABLE statement for the table in question

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Yeah, with all of this PHP code, I was beginning to think I stumbled into the MySQL forum

    I never go to the MySQL forums because I get tired of all the PHP questions.

  4. #4
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    You need to quote literals. Change
    $sql = "SELECT * FROM device WHERE macAddress = $myMACADDRESS";
    to
    $sql = "SELECT * FROM device WHERE macAddress = '$myMACADDRESS'";

Posting Permissions

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