Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: MySQL select user with the same IP address
Top Forums Programming MySQL select user with the same IP address Post 302860175 by durden_tyler on Saturday 5th of October 2013 01:04:10 AM
Old 10-05-2013
Quote:
Originally Posted by Lord Spectre
...
I woul like to make a query to output all the users having the same IP address.
... I'm expecting somethig like:
Code:
name          logged_ip   count
========      =========   ======
user1,user4   127.0.0.1   2
user2,user5   127.0.0.2   2

How can I output the usernames with the same IP as the example?
...
Code:
mysql>
mysql> select * from users;
+-------+-----------+
| name  | logged_ip |
+-------+-----------+
| user1 | 127.0.0.1 |
| user2 | 127.0.0.2 |
| user3 | 127.0.0.3 |
| user4 | 127.0.0.1 |
| user5 | 127.0.0.2 |
| user6 | 127.0.0.5 |
+-------+-----------+
6 rows in set (0.00 sec)

mysql>
mysql> select group_concat(name) as name, logged_ip, count(*) as count from users group by logged_ip having count(*) > 1;
+-------------+-----------+-------+
| name        | logged_ip | count |
+-------------+-----------+-------+
| user1,user4 | 127.0.0.1 |     2 |
| user2,user5 | 127.0.0.2 |     2 |
+-------------+-----------+-------+
2 rows in set (0.00 sec)

mysql>
mysql>

This User Gave Thanks to durden_tyler For This Post:
Lord Spectre
 

10 More Discussions You Might Find Interesting

1. Programming

Get user name from ip address

hi all i am new in unix programming i want some help from this forum, i want to know how to get user name of other machine in local network from its ip address or suggest any books for networking programming on unix sachin (6 Replies)
Discussion started by: munnu
6 Replies

2. Shell Programming and Scripting

Insert IP address into MySQL int field

Greets all, I'm using Perl trying to insert an IP address from a log file into an INT field in a MYSQL table. So it needs to be converted into an int first. I thought the pack() function could do this for me, but I'm not sure I get the right thing. I also need to be able to extract it correctly... (3 Replies)
Discussion started by: otheus
3 Replies

3. UNIX for Dummies Questions & Answers

script to ask the user to select a number

hi all, I have 4 scripts which i would need to run 1. BP.sh 2.DB.sh 3.LK.sh 4.TB.sh I would like write a script which would ask the user to select a number from 1-4 accordingly and run only that script for him/her I succeeded till reading the user input but not sure how to run that... (5 Replies)
Discussion started by: family_guy
5 Replies

4. OS X (Apple)

Ho do I masquerade the "user@user.local" address in mail/mailx?

Hi, I'm brand new here and looking for a solution: I'm using mail or mailx. The default reply address is «myshortusername@mylongusername.local» which makes absolutely no sense for anybody receiving my emails. But how do I change it? There seem to be many solutions but none for Mac OS X.... (0 Replies)
Discussion started by: gczychi
0 Replies

5. UNIX for Dummies Questions & Answers

Panic kernal-mode address fault on user address 0x14

:) Firstly Hi all!!, im NEW!! and on here hoping that someone might be able to offer me some help... i have a server that keeps crashing every few days with the error message: PANIC KERNAL-MODE ADDRESS FAULT ON USER ADDRESS 0X14 KERNAL PAGE FAULT FROM (CS:EIP)=(100:EF71B5BD) EAX=EF822000... (10 Replies)
Discussion started by: Twix
10 Replies

6. Programming

Select several minimum values from row (MySQL)

Hello there. I've got the query like that SELECT count(tour_id) AS cnt FROM orders JOIN tours ON orders.tour_id=tours.id GROUP BY tour_id The result Is cnt 1 4 2 1 1 Now i have to select all records with minimum values in field "cnt" MySQL function min() returns only one.... (2 Replies)
Discussion started by: Trump
2 Replies

7. Shell Programming and Scripting

Select record with MAX value in MySQL

Hi there, I have trouble selecting record that contain one biggest value for a group of other values. I mean, this is my table: mysql> SELECT * FROM b; +----+------+-------+ | id | user | value | +----+------+-------+ | 1 | 1 | 100 | | 3 | 1 | 150 | | 5 | 1 | 300 | | 6... (20 Replies)
Discussion started by: chebarbudo
20 Replies

8. Shell Programming and Scripting

mysql how to select a specific row from a table

i have a table records ------------ id | user | time | event 91 admin | 12:00 | hi 92 admin | 11:00 | hi 93 admin | 12:00 | bye 94 admin | 13:00 | bye 95 root | 12:00 | hi 96 root | 12:30 | hi 97 root | 12:56 | hi how could i only select and display only the user and event from... (6 Replies)
Discussion started by: kpddong
6 Replies

9. Shell Programming and Scripting

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (0 Replies)
Discussion started by: senkerth
0 Replies

10. UNIX for Advanced & Expert Users

mysql select query optimization..

hi.. i need to optimize my select query .. my situation is like this .. i have 15 lac recors in my table.. the following query takes nine seconds to give the required output.. SELECT max(ah.AUC_AMT), SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (1 Reply)
Discussion started by: senkerth
1 Replies

LEARN ABOUT OPENSOLARIS

mysql_fix_privilege_tables

mysql(1)							  MySQL database							  mysql(1)

NAME

       mysql_fix_privilege_tables - Fixes MySQL privilege tables.

SYNOPSIS

       mysql_fix_privilege_tables [mysql_root_password]

DESCRIPTION

       This scripts updates the mysql.user, mysql.db, mysql.host and the mysql.func tables to MySQL 3.22.14 and above.

       This is needed if you want to use the new GRANT functions, CREATE AGGREGATE FUNCTION or want to use the more secure passwords in 3.23

       If you get 'Access denied' errors, run the script again and give the MySQL root user password as an argument.

SEE ALSO

       isamchk(1),  isamlog(1),  mysql(1),  mysqlaccess(1), mysqladmin(1), mysqld(1), mysqld_multi(1), mysqld_safe(1), mysqldump(1), mysqlshow(1),
       mysql_zap(1), perror(1), replace(1)

       For more information please refer to the MySQL reference manual, which may already be installed locally and which is also available  online
       at http://www.mysql.com/doc/en/

BUGS

       Please refer to http://bugs.mysql.com/ to report bugs.

AUTHOR

       This manpage was written by Christian Hammers <ch@debian.org>.

       MySQL is available at http://www.mysql.com/.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +--------------------+------------------------------------+
       |  ATTRIBUTE TYPE    |	       ATTRIBUTE VALUE		 |
       +--------------------+------------------------------------+
       |Availability	    | SUNWmysqlr, SUNWmysqlu, SUNWmysqlt |
       +--------------------+------------------------------------+
       |Interface Stability | External				 |
       +--------------------+------------------------------------+
NOTES

       Source for mysql is available on http://opensolaris.org.

MySQL 4.0							   17 March 2003							  mysql(1)
All times are GMT -4. The time now is 05:01 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy