CODEWITHSUNDEEP
mysqlsqlip-address
Jesika
Jesika

Reputation: 143

Getting ip address using MySQL query

In MySQL query:

SELECT host
FROM information_schema.processlist
WHERE ID = connection_id( )
LIMIT 0 , 30

The result of ^ this query is: localhost.

SELECT @@hostname;

The result of ^ this query is: localhost.

But I need to get ipaddress like 192.168.1.2.

Question: How to get this result using mysql query?

Upvotes: 12

Views: 49116

Answers (5)

Philipp
Philipp

Reputation: 11341

I found that many MySQL servers use the BIND_ADDRESS global variable. This variable returns the IP address(es) that the server listens to. However, in my tests, this value was usually "127.0.0.1".

SHOW VARIABLES LIKE 'HOSTNAME';     -- Returns: MyHostName
SHOW VARIABLES LIKE 'BIND_ADDRESS'; -- Returns: 127.0.0.1

Alternative Syntax:

SELECT @@hostname, @@bind_address;  -- Returns: MyHostName, 127.0.0.1

Note:

This is not a bulletproof solution. Depending on server configuration, the key BIND_ADDRESS (or @@bind_address) can also return the value *.

Upvotes: -1

John
John

Reputation: 1004

With the help of this post and is great split function you can rewrite it like this :

Select user,CONCAT(Split_fn(host, "-", 2),'.',Split_fn(host, "-", 3),'.',Split_fn(host, "-", 4),'.',Split_fn(Split_fn(host, "-", 5),".",1)) as Ip_Address from information_schema.PROCESSLIST where ID = CONNECTION_ID()

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

To get the IP address only without the port number.

 Select SUBSTRING_INDEX(host,':',1) as 'ip' 
 From information_schema.processlist 
 WHERE ID=connection_id();

Upvotes: 11

Nisham Mahsin
Nisham Mahsin

Reputation: 1399

The query
select host from information_schema.processlist WHERE ID=connection_id();

Will give you the host name .You will get IP address( like 192.168.1.2.) if name resolution is not enabled, which it is usually not.

Upvotes: 0

user3162968
user3162968

Reputation: 1046

Please tell me whether you need to get IP from visitor of the site, or something else. If you just need IP of the visitor, you could use php remote_addr function.

$_SERVER['REMOTE_ADDR'];

Is it something like a table where IP adresses are stored? I think that your query is looking for host, not for IP. But I am not sure if I understood you properly.

Upvotes: -7

Related Questions