php mysql select all with same data

Question

I have a table with 3 cols, incremeting id, name, and data

What i'm trying to achieve

you enter a $name... and returns $data...
then it takes $data and finds all $names that have the same $data.

SELECT * FROM table WHERE name ='$name' data='$data' and data!='0'

does not seem to cut it..

I'm basically trying to get all rows that have the same data as the entered $name's $data.

thanks in advance.

Answer 1

SELECT * FROM table WHERE name ='$name' and data='$data' and data!='0'

You were missing an and

EDIT:

select * from table where
 name in 
   ( select data from table where name = '$name' )

Answer 2

Try joining the table with itself, if you must do this in one query:

SELECT * FROM table t1,table t2 WHERE t1.name = '$name' AND t1.data=t2.data AND t1.name != t2.name

Alternately, use a nested query:

SELECT name FROM table WHERE data IN (SELECT data FROM table WHERE name='$name')

I recommend the nested query. It's easier to read.

What's wrong with your original query as posted is that you're missing an 'AND'. But it also won't do what you say you want it to.

Answer 3

You may want to try the following:

SELECT  t2.name
FROM    `table` t1
JOIN    `table` t2 ON (t2.data = t1.data)
WHERE   t1.name = '$name' AND t1.data != '0';

Test case:

CREATE TABLE `table` (id int, name varchar(10), data varchar(10));

INSERT INTO `table` VALUES (1, 'name1', 'data-a');
INSERT INTO `table` VALUES (2, 'name2', 'data-b');
INSERT INTO `table` VALUES (3, 'name3', 'data-a');
INSERT INTO `table` VALUES (4, 'name4', 'data-b');
INSERT INTO `table` VALUES (5, 'name5', 'data-a');

Result:

SELECT  t2.name
FROM    `table` t1
JOIN    `table` t2 ON (t2.data = t1.data)
WHERE   t1.name = 'name2' AND t1.data != '0';

+-------+
| name  |
+-------+
| name2 |
| name4 |
+-------+
2 rows in set (0.00 sec)

The nested query solution suggested by @Borealid is another valid solution that returns the same result:

SELECT  name 
FROM    `table` 
WHERE   data IN (SELECT data FROM `table` WHERE name = 'name2');

+-------+
| name  |
+-------+
| name2 |
| name4 |
+-------+
2 rows in set (0.00 sec)