Jump to content


Photo

Double Query


  • Please log in to reply
10 replies to this topic

#1 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 16 October 2006 - 09:31 PM

Hi all

I 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;

	$sql = "SELECT * FROM types WHERE word LIKE '%".$_GET['word']."%'";
	$result = mysql_query($sql, $connection);
	while ($rows = mysql_fetch_array($result))
	{
        print $rows['id'];
	}

If the word "computer" is typed into the input box, for example, and the form submitted, the following is returned

12, 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 example

23, 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, if

2, 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 possible

Any help would be ace

Thanks in advance

Ed

#2 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 16 October 2006 - 09:37 PM

Database type and exact version please!


me!

#3 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 16 October 2006 - 09:46 PM

Database Type: SQL
Server version: 4.1.21-standard
MySQL client version: 4.1.10
Server: Localhost via UNIX socket

#4 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 16 October 2006 - 09:57 PM

Is there any other information that you need to assist me?

#5 printf

printf
  • Staff Alumni
  • Advanced Member
  • 889 posts

Posted 16 October 2006 - 10:20 PM

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!

$sql = "SELECT * FROM users WHERE types IN (SELECT id FROM types WHERE word LIKE '%" . mysql_real_escape_string ( $_GET['word'] ) . "%') ORDER BY types";


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!

types IN (SELECT id FROM types WHERE word LIKE '%" . mysql_real_escape_string ( $_GET['word'] ) . "%')

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 id numbers which then as used in the main query in the user table, to select all rows that contain types matching what the sub query loaded into the IN()!

me!



#6 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 17 October 2006 - 09:46 AM

Hi

Thanks 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 like

ID      VALUE
1        Computers
2        The Internet
3        Websites
4        Hardware

Then the table users, looks like

ID        VALUE            TOPIC
1        Dave Small      2, 4,
2        John Biggins    1, 2, 3,
3        Sarah Hall
4        John Wicky      2, 3, 4

So 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

$sql = "SELECT * FROM users WHERE types IN (SELECT id FROM types WHERE word LIKE '%" . mysql_real_escape_string ( $_GET['word'] ) . "%') ORDER BY types";

Any help would be fab, been trying to crack this for the last 4 hours and going no where fast

Thanks in advance

Ed

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 17 October 2006 - 03:52 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 18 October 2006 - 09:15 AM

Thanks, I think I will do that from now on, but until then, is there a solution I can use for my problem

Still trying to crack it

Any advice would be great

Thanks

Ed

#9 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 18 October 2006 - 10:30 AM

Thanks, I think I will do that from now on, but until then, is there a solution I can use for my problem

Still trying to crack it

Any advice would be great

Thanks

Ed


SELECT
*
FROM
users
WHERE
`TOPIC`
REGEXP(
           CONCAT('(^|,)[[:space:]]*(', 
                (SELECT GROUP_CONCAT(`ID` SEPARATOR '|') FROM types WHERE `VALUE` LIKE '%$word%'),         
                 ')[[:space:]]*(,|$)')
          )


#10 CanMan2004

CanMan2004
  • Members
  • PipPipPip
  • Advanced Member
  • 254 posts

Posted 18 October 2006 - 10:47 AM

A massive thank you Shoz and everyone else who helped, it works fantastic now

#11 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 19 October 2006 - 04:16 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users