CanMan2004 Posted October 16, 2006 Share Posted October 16, 2006 Hi allI have a html page with a input box, it allows users to do a search on a sql table called "types", the query I use is;[code] $sql = "SELECT * FROM types WHERE word LIKE '%".$_GET['word']."%'"; $result = mysql_query($sql, $connection); while ($rows = mysql_fetch_array($result)) { print $rows['id']; }[/code]If the word "computer" is typed into the input box, for example, and the form submitted, the following is returned12, 34, 56, 121, 234, 432,I then have another table called `users` this table has a field called `types`, inside the field `types`, there are id numbers stored in it, for example23, 34, 45, 67, 89,What I want to do is to pick each of the numbers that are returned from my query and then return all the rows from the table `users` that have the returned numbers from the query, within the field `types`.Basically, if2, 16, 23,are returned in the query, then it should look through the table `users` and return all rows which have 2 and\or 16 and\or 23 within the field `types`.Does that make sense?Not something i've ever done before, so not sure if this is possibleAny help would be aceThanks in advanceEd Quote Link to comment Share on other sites More sharing options...
printf Posted October 16, 2006 Share Posted October 16, 2006 Database type and exact version please!me! Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted October 16, 2006 Author Share Posted October 16, 2006 Database Type: SQLServer version: 4.1.21-standardMySQL client version: 4.1.10Server: Localhost via UNIX socket Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted October 16, 2006 Author Share Posted October 16, 2006 Is there any other information that you need to assist me? Quote Link to comment Share on other sites More sharing options...
printf Posted October 16, 2006 Share Posted October 16, 2006 I don't know what your table layout is, but something like this should work! Change the ORDER BY to what ever column in the user table you want to sort on![code]$sql = "SELECT * FROM users WHERE types IN (SELECT id FROM types WHERE word LIKE '%" . mysql_real_escape_string ( $_GET['word'] ) . "%') ORDER BY types";[/code]There are more ways to do this, each can return different information, what you want returned is what will tell you is the best way to do the query! This is based on what you were asking in your question!What it does![code]types IN (SELECT id FROM types WHERE word LIKE '%" . mysql_real_escape_string ( $_GET['word'] ) . "%')[/code]Before the query is run on the user table, the sub select is run, it finds all rows where word like $_GET['word'] and returns all the [b]id[/b] numbers which then as used in the main query in the user table, to select all rows that contain [b]types[/b] matching what the sub query loaded into the IN()!me! Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted October 17, 2006 Author Share Posted October 17, 2006 HiThanks printf, that almost did it, but im not sure it's returning all results.Here is some further info if anyone can help.The table `types` looks likeID VALUE1 Computers2 The Internet3 Websites4 HardwareThen the table users, looks likeID VALUE TOPIC1 Dave Small 2, 4,2 John Biggins 1, 2, 3,3 Sarah Hall4 John Wicky 2, 3, 4So when a search is done on the html form, the query should return all the ID numbers for everry row found in the `types` table and then check those numbers returned against the field `types` in the `users` table and return all rows which have ANY of those values returned.As printf suggested, im using the following query[code]$sql = "SELECT * FROM users WHERE types IN (SELECT id FROM types WHERE word LIKE '%" . mysql_real_escape_string ( $_GET['word'] ) . "%') ORDER BY types";[/code]Any help would be fab, been trying to crack this for the last 4 hours and going no where fastThanks in advanceEd Quote Link to comment Share on other sites More sharing options...
fenway Posted October 17, 2006 Share Posted October 17, 2006 The real concern is that you're storing lists of values in a single field -- this is why you're running into all of these problems. You need a third lookup table, user_types, to link topics to users in a one-to-many relationship. Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted October 18, 2006 Author Share Posted October 18, 2006 Thanks, I think I will do that from now on, but until then, is there a solution I can use for my problemStill trying to crack itAny advice would be greatThanksEd Quote Link to comment Share on other sites More sharing options...
shoz Posted October 18, 2006 Share Posted October 18, 2006 [quote author=CanMan2004 link=topic=111706.msg453655#msg453655 date=1161162905]Thanks, I think I will do that from now on, but until then, is there a solution I can use for my problemStill trying to crack itAny advice would be greatThanksEd[/quote][code]SELECT*FROMusersWHERE`TOPIC`REGEXP( CONCAT('(^|,)[[:space:]]*(', (SELECT GROUP_CONCAT(`ID` SEPARATOR '|') FROM types WHERE `VALUE` LIKE '%$word%'), ')[[:space:]]*(,|$)') )[/code] Quote Link to comment Share on other sites More sharing options...
CanMan2004 Posted October 18, 2006 Author Share Posted October 18, 2006 A massive thank you Shoz and everyone else who helped, it works fantastic now Quote Link to comment Share on other sites More sharing options...
fenway Posted October 19, 2006 Share Posted October 19, 2006 Kudos to shoz for taking the time to construct that hideous looking REGEXP, but remember that this "fantastic" workaround is a band-aid solution for a DB normalization problem that will come back to haunt you very soon. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.