Jump to content

Double Query


CanMan2004

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

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