Use of SQL INET_NTOA in joins

richardvk

Hi,

I have a database with ip addresses stored as integers. Usually i select these out using:

select INET_NTOA(ip_address) as dotted_quad.....

However, this seems to cause a SQL failure when used in a datatables editor join:

->join(
Join::inst( subnets_table', 'array' )
->join( 'range_pk','range_fk')
->fields(
Field::inst('INET_NTOA(ip_address) as dotted_quad' )
)
)

NOTE if i have:
Field::inst(ip_address)
on its own, this wors fine (i get back the IP as an integer.

I also tried, using things like UPPER on strings and that seemed to work - is INET_NTOA supported?

allan

Hi,

Unfortunately no SQL function is currently supported by the PHP libraries. If you want to format the data in a particular way, you need to do it using PHP at this time:

Field::inst('ip_address' )
  ->getFormatter( function ( $val ) {
    return long2ip( $val );
  } );

There is also a setFormatter if you need to set it.

Allan

richardvk

Thanks Allan, works perfectly as an alternative!

allan

Update - see also this thread from @ewmanthei for how to do this with a getFormatter in Editor.

Regards,
Allan