damned mysql!!

[Mattyspatty]

"SELECT * FROM user_stats WHERE username LIKE '%m%' AND LIKE '%t%'";

 

i tried to make it search my DB for username were theyre like m and t (if u didnt undertsand it) basically i want things like

 

matty

bart

timmy

 

 

but with my old methods of doing the guery for each input (m and t) i wud get

matty

bart

timmy

matty

timmy

 

 

as it wud get m and t twice, so how can i make it work?!? cos the current script (see top) doesnt work at all :S whats wrong?

[neylitalo]

try the following:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT * FROM user_stats WHERE username LIKE '%m%' AND username LIKE '%t%' [!--sql2--][/div][!--sql3--]

 

The DISTINCT will make it only return unique values.

 

EDIT: actually, you may want to only return a specific column... I'm not sure how the DISTINCT is going to behave with multiple columns. Say, something like

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] DISTINCT username FROM user_stats WHERE username LIKE '%m%' AND username LIKE '%t%' [!--sql2--][/div][!--sql3--]

 

EDIT (again): modified the queries for "incorrect" syntax... if you have an AND clause, you should say what column the second part of the WHERE needs to look at.

[Mattyspatty]

thx thats an improvement, but i need it to search the whole table, i didnt use distinct but it didnt work anyway.

 

can u see any way round my problem? the full thing is:

 

i ahve a form and a textbox to find users, you can search m AND t and ive made it echo the query after i applied stuff to it and i get

 

username LIKE '%m%' AND username LIKE '%t%'

 

so i put that on the end of my main query, is this not working? cos i only get 1 result

 

btw i like ur quotes on ur profile

 

[ryanlwh]

I guess you want OR, not AND.

[neylitalo]

ryan's right - I made an oops.

 

Mattyspatty -

 

I have the following database structure (never mind that it's different than yours; the idea is the same)

 

mysql> select * from user_stats;
+----------+--------+--------+--------+--------+
| username | stat_1 | stat_2 | stat_3 | stat_4 |
+----------+--------+--------+--------+--------+
| matty    |      0 |      1 |      2 |      3 |
| bart     |      0 |      1 |      2 |      3 |
| timmy    |      0 |      1 |      2 |      3 |
| neal     |      0 |      1 |      2 |      3 |
+----------+--------+--------+--------+--------+
4 rows in set (0.00 sec)

 

I run the following query:

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * FROM user_stats WHERE username LIKE '%m%' OR username LIKE '%t%' [!--sql2--][/div][!--sql3--]

 

I get the following results:

 

mysql> select * from user_stats where username like '%m%' or username like '%t%';
+----------+--------+--------+--------+--------+
| username | stat_1 | stat_2 | stat_3 | stat_4 |
+----------+--------+--------+--------+--------+
| matty    |      0 |      1 |      2 |      3 |
| bart     |      0 |      1 |      2 |      3 |
| timmy    |      0 |      1 |      2 |      3 |
+----------+--------+--------+--------+--------+
3 rows in set (0.00 sec)

 

i like ur quotes on ur profile

So do I. The IE one is true, too... somebody actually thought it was broken, but it was a "feature" Way to go, Bill!

 

EDIT: MySQL version 4.1.14

[Mattyspatty]

woohoo!! tyvm works now

weldone both of u