PHP SQL - Text search table with reference to another table

Author

Text search table with reference to another table

SteveK

2008-03-19, 4:16 am

Hi Guys,

I'm using MATCH to perform a text search of two fields in a table, below:

SELECT *,
MATCH(name, description) AGAINST ('$item' IN BOOLEAN MODE) AS score
FROM items
WHERE type = '2' AND MATCH(name, description) AGAINST ('$item' IN BOOLEAN
MODE) LIMIT 0,2000

I now want to incorporate another statement in my query which will limit the
results based on another table.

This code does not work but try to understand where I was going with. I'll
explain below.

SELECT items.id, items.ref, items.type, items.name, items.description,
list.id, list.name, list.description, list.type

MATCH(menu_items.name, menu_items.description) AGAINST ('$item' IN BOOLEAN
MODE) AS score

FROM items, list

WHERE list.id = items.ref AND list.type = '$type'

AND items.type = '2' AND MATCH(items.name, items.description) AGAINST
('$item' IN BOOLEAN MODE) LIMIT 0,2000

Basically, I want to text search items.name and items.description and use
items.ref, which is a reference back to the list table, to make sure
list.type = '$type' is satisfied.

And to explain that in a simple worded example: say we have a list of items
(items) that below that belong to a number of different people and we
reference those back to the people (list). Now we want to search items of a
particular person so instead of searching all items and then narrowing down
those results to that particular person we first want to find that person
through the reference, then search for items.

Hopefully someone understands what I'm talking about.

Thanks,
SteveK

Captain Paralytic

2008-03-19, 8:06 am

On 19 Mar, 06:47, "SteveK" <steve...@gmail.com> wrote:
> Hi Guys,
>
> I'm using MATCH to perform a text search of two fields in a table, below:
>
> SELECT *,
> MATCH(name, description) AGAINST ('$item' IN BOOLEAN MODE) AS score
> FROM items
> WHERE type = '2' AND MATCH(name, description) AGAINST ('$item' IN BOOLEAN
> MODE) LIMIT 0,2000
>
> I now want to incorporate another statement in my query which will limit the
> results based on another table.
>
> This code does not work but try to understand where I was going with. I'll
> explain below.
>
> SELECT items.id, items.ref, items.type, items.name, items.description,
> list.id, list.name, list.description, list.type
>
> MATCH(menu_items.name, menu_items.description) AGAINST ('$item' IN BOOLEAN
> MODE) AS score
>
> FROM items, list
>
> WHERE list.id = items.ref AND list.type = '$type'
>
> AND items.type = '2' AND MATCH(items.name, items.description) AGAINST
> ('$item' IN BOOLEAN MODE) LIMIT 0,2000
>
> Basically, I want to text search items.name and items.description and use
> items.ref, which is a reference back to the list table, to make sure
> list.type = '$type' is satisfied.
>
> And to explain that in a simple worded example: say we have a list of items
> (items) that below that belong to a number of different people and we
> reference those back to the people (list). Now we want to search items of a
> particular person so instead of searching all items and then narrowing down
> those results to that particular person we first want to find that person
> through the reference, then search for items.
>
> Hopefully someone understands what I'm talking about.
>
> Thanks,
> SteveK

1) Since this is a pure SQL question it would be better asked over in
comp.databases.mysql
2) The statement "This code does not work" is as useful as a chocolate
fireguard! In what way does it "not work"? What results do you get?
What results did you expect to get?
3) In a case like this some sample data for the tables and sample
output is worth a thousand words. Offering said sampledata as CREATE
TABLE and INSERT statements is even better.

Peter van Schie

2008-03-24, 7:14 pm

SteveK wrote:

[snip]

> This code does not work but try to understand where I was going with. I'll
> explain below.
>
>
> SELECT items.id, items.ref, items.type, items.name, items.description,
> list.id, list.name, list.description, list.type
>
> MATCH(menu_items.name, menu_items.description) AGAINST ('$item' IN BOOLEAN
> MODE) AS score
>
> FROM items, list
>
> WHERE list.id = items.ref AND list.type = '$type'
>
> AND items.type = '2' AND MATCH(items.name, items.description) AGAINST
> ('$item' IN BOOLEAN MODE) LIMIT 0,2000
>
>
> Basically, I want to text search items.name and items.description and use
> items.ref, which is a reference back to the list table, to make sure
> list.type = '$type' is satisfied.

[snip]

Hi Steve,

I think what you're looking for is simply a JOIN to join the list table.
Your query will be somewhat like this:

SELECT items.id, items.ref, items.type, items.name, items.description
MATCH(menu_items.name, menu_items.description) AGAINST ('$item' IN
BOOLEAN MODE) AS score
FROM items
LEFT JOIN list ON list.id=items.ref
WHERE list.type = '$type'
AND items.type = '2' AND MATCH(items.name, items.description) AGAINST
('$item' IN BOOLEAN MODE) LIMIT 0,2000

Note that this is just a modified version of your own 'pseudo-query' but
you'll probably get the idea.

Peter.
--
http://www.phpforums.nl

no@way.com

2008-03-24, 7:14 pm

In article <47e7a9e7$0$57416$dbd49001@news.wanadoo.nl>, Peter van Schie
<"vanschie.peter [at] NOSPAMgmail.com"> says...
> I think what you're looking for is simply a JOIN to join the list table.
> Your query will be somewhat like this:
>

And when you use join go out and have a 3 course dinner - it may be
finished when you get back .... if you're lucky.

Peter van Schie

2008-03-24, 7:14 pm

no@way.com wrote:
> In article <47e7a9e7$0$57416$dbd49001@news.wanadoo.nl>, Peter van Schie
> <"vanschie.peter [at] NOSPAMgmail.com"> says...
>
> And when you use join go out and have a 3 course dinner - it may be
> finished when you get back .... if you're lucky.

What makes you think so? I did a small test for you with two tables with
both 1 million rows (randomly generated). The items table is of type
MyISAM to support the fulltext index, the list table is of type InnoDB.
See below for the results. You're either a very fast eater or you have a
serious misconception about table joins.

========================================
========================================
=================
mysql> use test;
Database changed
mysql> select count(*) from items;
+----------+
| count(*) |
+----------+
| 1000002 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT count(*) from list;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.75 sec)

mysql> SELECT items.id,items.ref,items.type,items.name,items.description,
-> MATCH(items.name,items.description) AGAINST ('testname24' IN
BOOLEAN MODE) AS score
-> FROM items
-> LEFT JOIN list ON list.id=items.ref
-> WHERE MATCH(items.name,items.description) AGAINST
-> ('testname24' IN BOOLEAN MODE) LIMIT 0,2000;
+----+--------+------+------------+---------------+-------+
| id | ref | type | name | description | score |
+----+--------+------+------------+---------------+-------+
| 27 | 808777 | 2 | testname24 | description24 | 1 |
+----+--------+------+------------+---------------+-------+
1 row in set (0.01 sec)

--
http://www.phpforums.nl

SteveK

2008-03-28, 4:33 am

Thanks Peter.

Perfect. And faster than no@way.com lead on.

Cheers! :)

"Peter van Schie" <"vanschie.peter [at] NOSPAMgmail.com"> wrote in message
news:47e7a9e7$0$57416$dbd49001@news.wanadoo.nl...
> SteveK wrote:
>
> [snip]
>
>
> [snip]
>
> Hi Steve,
>
> I think what you're looking for is simply a JOIN to join the list table.
> Your query will be somewhat like this:
>
> SELECT items.id, items.ref, items.type, items.name, items.description
> MATCH(menu_items.name, menu_items.description) AGAINST ('$item' IN BOOLEAN
> MODE) AS score
> FROM items
> LEFT JOIN list ON list.id=items.ref
> WHERE list.type = '$type'
> AND items.type = '2' AND MATCH(items.name, items.description) AGAINST
> ('$item' IN BOOLEAN MODE) LIMIT 0,2000
>
> Note that this is just a modified version of your own 'pseudo-query' but
> you'll probably get the idea.
>
> Peter.
> --
> http://www.phpforums.nl

Sponsored Links