Jump to content

Archived

This topic is now archived and is closed to further replies.

Mattyspatty

damned mysql!!

Recommended Posts

"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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 :P

 

Share this post


Link to post
Share on other sites

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 :P
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.