Jump to content

Archived

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

CanMan2004

Double Query

Recommended Posts

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;

[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 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

Share this post


Link to post
Share on other sites
Database Type: SQL
Server version: 4.1.21-standard
MySQL client version: 4.1.10
Server: Localhost via UNIX socket

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
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

[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 fast

Thanks in advance

Ed

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[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 problem

Still trying to crack it

Any advice would be great

Thanks

Ed
[/quote]

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

Share this post


Link to post
Share on other sites
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.

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.