Jump to content

damned mysql!!


Mattyspatty

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?

Link to comment
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.

Link to comment
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

 

Link to comment
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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.