Grabing matching data, and showing it from mySQL

[tsunami]

Hi, i run a online multiplayer **** game, and one of our main rules that gets broken alot is creating multiple accounts. I have been searching and found a few things, but not anything that has helped me. I am wondering how i can grab all the data from the database, and echoing only the data that matches in order.

So lets say this is my database

id ¦ username ¦ password
1 komo rink
2 php rules
3 cgi gay
4 tsunami rules

Ok, so that is my database. So there is 2 rows with the same password. So i only want the database to show those 2 rows.

you have 2 possible multiple accounts....
1 php rules [click here to ban]
4 tsunami rules [click here to ban]

Just trying to make sense with what im trying to do, and why i need something like this.

Thanks guys,
Tsunami

[NickCoons]

tsunami,

That would be:

SELECT * FROM [table] WHERE [fieldname] = [value]

So if the fieldname is "Password" and you want to retrieve all rows where the password is "rules":

SELECT * FROM [table] WHERE Password = 'rules'

This should give you the desired results.

[tsunami]

no affence, but did you read what i wrote....

im trying to find multiple accounts, if i knew every single multiple player's ip, then i wouldnt of wrote this topic.

The database is sabose to echo all "matching" feild entrys in order.

Let me show u another example...

id user pass
1 jo as
2 ds re
3 fe na
4 pk as
5 ha eq
6 ew ok
7 ba re

if that was my database, it would echo

1 jo as
4 pk as

2 ds re
7 ba re

I want the query to find the multiple feilds, and only echo the multiple fields.

I want to be able to find multiple accounts without going to phpMyAdmin, and sort by row, and showing them ALL. i just want something that echos online more then 1 matches of say "passwords".

This is a example that i found off this board, but it doesnt work, and i didnt think it would because of WHERE count(password)>1, it makes sense, but ive never seen something like that
[code]
$dotest = mysql_query("SELECT username,password FROM users WHERE COUNT(password)>1 GROUP BY password;");
while ($test = mysql_fetch_array($dotest))
{?>
<table>
<tr>
<td><?=$test[0]?></td><td><?=$test[1]?></td>
</tr>
</table>
<?}?>
[code]

[NickCoons]

tsunami,

<no affence, but did you read what i wrote....>

No offense, but did *you* try to read what you wrote? :-)

<if i knew every single multiple player's ip, then i wouldnt of wrote this topic.>

Where did I say anything about IPs?

<if that was my database, it would echo

1 jo as
4 pk as

2 ds re
7 ba re>

Are you asking, "How can I show rows where a particular field has a duplicate entry?" If so, try this:

<?php
$Users = mysql_query("SELECT * FROM Users");
while($UserData = mysql_fetch_array($Users, MYSQL_ASSOC))
{
$TestDuplicate = mysql_query("SELECT * FROM Users WHERE Password = '" . $UserData['Password'] . "');
if(mysql_num_rows($TestDuplicate) > 1)
{
# $TestDuplicate contains list of some users with duplicate passwords, do what you want with it.
}
}
?>

This can probably be tweaked a bit to be more efficient. For instance, it will display users multiple times because they will match each time the password comes up. But it should get you going in the right direction.

Hope that helps.

[dcrombie]

SELECT DISTINCT password, COUNT(*) AS count FROM accounts GROUP BY password ORDER BY count DESC;

This should list all the passwords (IPs) and how many times they appear. Any multiples will appear at the top.

[tsunami]

thanks guys for all your help, these codes will start me off now.