Retrieve specific field value from Postgres db query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Chaund
Forum Newbie
Posts: 1
Joined: Fri Jan 22, 2010 6:28 am

Retrieve specific field value from Postgres db query

Post by Chaund »

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: Select all

       $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: Select all

$myMACADDRESS = $this->db->qstr( strtolower( $this->x['macAddress']) );
 
$sql = "SELECT * FROM device WHERE macAddress = $myMACADDRESS";
$results = $this->db->Execute($sql);
I added this code to get the value of $results:

Code: Select all

$this->log( LOG_DEBUG, "WATCHME", "WATCHME:  X Structure: " . print_r(
$results, true) );
Results:

Code: Select all

   [databaseType] => postgres7
    [_blobArr] =>
    [canSeek] => 1
    [dataProvider] => native
    [fields] => Array
        (
            [0] => 15
            [deviceid] => 15
            [1] => 1069
            [consumerid] => 1069
            [2] => 2
            [devicetype] => 2
            [3] => 1
            [vemgid] => 1
            [4] => Demo
            [shortdescription] => Demo
            [5] => 00:0d:48:30:0b:75
            [macaddress] => 00:0d:48:30:0b:75
            [6] => 723148661
            [devicehash] => 723148661
            [7] => 192.168.1.38
            [ipaddresstv] => 192.168.1.38
            [8] => 192.168.1.99
            [ipaddresshome] => 192.168.1.99
            [9] =>
            [ipaddresspublic] =>
            [10] =>
            [publicport] =>
            [11] => 135162524
            [df] => 135162524
            [12] => 152648512
            [dfmax] => 152648512
            [13] => XMPP1
            [lastseenby] => XMPP1
            [14] =>
            [lastswversion] =>
            [15] => 1
            [status] => 1
            [16] => 1249478213
            [createddate] => 1249478213
            [17] => 1263829535
            [lastupdate] => 1263829535
        )
 
    [blobSize] => 100
    [sql] => SELECT * FROM device WHERE macAddress = '00:0d:48:30:0b:75'
Here is where I have having difficulties with the PHP code:

I need to get the value from consumerId.

Code: Select all

$this->x['consumerId'] = $results->fields['consumerId'];
I added a line of code to see the value of $results->fields['consumerId'] and got nothing.

Why does this code work fine in MySQL and not when I switched to Postgres?
Post Reply