Jump to content


Photo

damned mysql!!


  • Please log in to reply
5 replies to this topic

#1 Mattyspatty

Mattyspatty
  • Members
  • PipPipPip
  • Advanced Member
  • 76 posts
  • LocationEngland :D

Posted 25 September 2005 - 05:44 PM

"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?
ok, so my PHP is a bit rusty... my replies are 90% ok.
the other 10% either have syntax errors or are the "hard solution"
http://www.php.net  http://www.google.com - Are your friends!

I have a C++ problem with 'keybd_event' in certain applications. if you can help me PM me! :P
Needs motivation to create another quick cash website. I need a new TV

#2 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 25 September 2005 - 05:55 PM

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.
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#3 Mattyspatty

Mattyspatty
  • Members
  • PipPipPip
  • Advanced Member
  • 76 posts
  • LocationEngland :D

Posted 25 September 2005 - 06:23 PM

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

ok, so my PHP is a bit rusty... my replies are 90% ok.
the other 10% either have syntax errors or are the "hard solution"
http://www.php.net  http://www.google.com - Are your friends!

I have a C++ problem with 'keybd_event' in certain applications. if you can help me PM me! :P
Needs motivation to create another quick cash website. I need a new TV

#4 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 25 September 2005 - 06:48 PM

I guess you want OR, not AND.
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#5 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 25 September 2005 - 06:55 PM

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
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.

#6 Mattyspatty

Mattyspatty
  • Members
  • PipPipPip
  • Advanced Member
  • 76 posts
  • LocationEngland :D

Posted 25 September 2005 - 08:53 PM

woohoo!! tyvm :D works now
weldone both of u :P
ok, so my PHP is a bit rusty... my replies are 90% ok.
the other 10% either have syntax errors or are the "hard solution"
http://www.php.net  http://www.google.com - Are your friends!

I have a C++ problem with 'keybd_event' in certain applications. if you can help me PM me! :P
Needs motivation to create another quick cash website. I need a new TV




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users