Get all Usernames by IP row from MySQL

Question

So, everytime when someone registers on my server, their IP get's saved on ROW named "IP".

I want to get, every "USERNAME" row, by the same "IP" row saved.

Eg: Accounts: Mike, John are saved on IP 127.0.0.1

I want to select IP 127.0.0.1 and show me Mike, John.

Currently I did this:

$sql10 = "SELECT IP, username, pHour FROM users  order by pHour DESC LIMIT 5;";
$results10 =  array();
$result10 = mysqli_query($con,$sql10);

$results10 = mysqli_fetch_all($result10, MYSQLI_ASSOC);

$output10 = '';

foreach ($results10 as $row) {
    $output10 .= ' ' . $row['username'] . ' and ' . $row['pHour'] . '
';
}

I think that, I should add

SELECT username, IP, pHour FROM users WHERE IP = '$row['IP']' order by pHour

I'm on a good way to do it or not even close?

Simply, I want to get every username and pHour from the row 'IP'

Possible duplicate of [How to get the client IP address in PHP?](https://stackoverflow.com/questions/3003145/how-to-get-the-client-ip-address-in-php) — Cid, Oct 25 '18 at 19:03
@Cid it's not duplicate, I know how to get client IP address, I just don't know how to get "Username" rows based on his IP adress. — Emin Eryilmaz, Oct 25 '18 at 19:07
update your question and add the table schema a data sample and the expetced result and the db you are using too — ScaisEdge, Oct 25 '18 at 19:14
which db you are using .. and which data type is usedc for column ip? — ScaisEdge, Oct 25 '18 at 19:17
@scaisEdge I want to echo it inside the HTML, I'm using MySQL db, it's used varchar. — Emin Eryilmaz, Oct 25 '18 at 19:22
`WHERE IP = '$row['IP']'` - You would first need to have another query before that. You should be getting errors here. — Funk Forty Niner, Oct 25 '18 at 19:27
You probably might have to join the usernames using something equivalent like `STUFF` and `FOR XML PATH` in sqlserver — Nilesh, Oct 25 '18 at 20:24

ScaisEdge · Accepted Answer · 2018-10-25T19:26:34.080

1

You should add where condition for filter the rows ip

assuming your ip column is saved in a string column named ip

$sql10 = "SELECT username, pHour 
            FROM users 
            where ip = '127.0.0.1'
            order by pHour DESC LIMIT 5;";

you could use for passing the ip as a var

    $sth = $dbh->prepare('SELECT username, pHour 
        FROM users
        where ip = ?');
    $sth->bind_param("s",  $your_ip_var);

    $sth->execute();

edited Oct 25 '18 at 19:26

answered Oct 25 '18 at 19:19

ScaisEdge

131,976
10
91
107

Instead of where ip = '127.0.0.1' can I add where ip = '$row['IP']' ? which gets the current session's IP row from mysql – Emin Eryilmaz Oct 25 '18 at 19:23
*"assuming you are PDO"* - They're using the mysqli_ api here; look at their code ;-) – Funk Forty Niner Oct 25 '18 at 19:24
Noticing the edit: `where ip = :my_ip'` and `bind_param()` are of 2 different apis. – Funk Forty Niner Oct 25 '18 at 19:26
@FunkFortyNiner ..thanks for the suggestion ... answer updated – ScaisEdge Oct 25 '18 at 19:28
Thanks. That was what I was looking for. – Emin Eryilmaz Oct 25 '18 at 19:29

Get all Usernames by IP row from MySQL

1 Answers1