Search on multiple fields.

[letimati]

Is there a easy way to search on different fields for a entry of a text string.

So if i had one table product that had name, code, description and I had a search string 'something' would i have to do

select * from product where name = '%something%' OR code = '%something%' OR description = '%something%'

or is there a way of doing the seach on all the fields with out naming then so i could do a search on any table without knowing the names of the fields.

Thanks in advance.

 

[PHV]

You may try something like this:
SELECT *
FROM product
WHERE name || code || description LIKE '%something%'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[letimati]

I was more wodering if you can search for an entry without knowing the field name so i could run the same query over different table and only know the table name.

But its not to much hastel to do it this way

thanks for the help

 

[r937]

i'm still wondering under what circumstances one would be writing an app that has no knowledge of the tables it's supposed to deal with



r937.com | rudy.ca

 

[Golom]

You could probably do this in some procedural code but likely not in pure SQL. The problem is that, without knowledge of field names and data types you need to resort to some indexed way to deal with the information being returned. That is, you would need to talk about

Field(0)
Field(1)
:
Field(10)
etc.

Rather than
FirstName
LastName
:
Address
etc.

SQL is designed to deal with the second (i.e. field names) approach but not the first.

 

[letimati]

Sorry i thought i had replied to this to explane this better.

I have all the content in one table called content and then numeric values for the entrys so i want to do a search on a table so i am trying to do something like this

SELECT n.*
FROM news n, content_english c
WHERE c.content_general LIKE '%Glasgow%' && n.body || n.headline = c.id

this however gives me loads of entrys back.

again thanks in advance for any help

 

[r937]

tip: use standard sql ANDs and ORs, not those horrible and proprietary mysql operators

select n.*
from news n
inner
join content_english c
on c.id = n.headline
where c.content_general LIKE '%Glasgow%'

r937.com | rudy.ca

 

[letimati]

Cheers that was quick!

How do i add extra fields to it, cause i have to check weather the body field has the word incase the headline does not.

So want to see if this row has the word glasgow in it anywhere in the headline or the body.

 

[r937]

okay, then the query i gave won't work -- you didn't say which columns were used for the join, and i guessed wrong

could you please show the table layouts

r937.com | rudy.ca

 

[letimati]

I have just worked it out not really hard i should have tried before that post. Sorry

SELECT n. *
FROM news n
INNER JOIN content_english c ON c.id = n.headline
OR c.id = n.body
WHERE c.content_general LIKE '%Glasgow%'

 

[r937]

think about that query for a second -- how can an id be equal to a headline or a body?

r937.com | rudy.ca

 

[letimati]

cause the headline and body are numeric values that represent rows in the content table. So i have these table.

news table

id | headline | body | live
1 | 1 | 2 | 1
2 | 3 | 4 | 1

Content table

id | content_general
1 | "this is headline for new article id 1"
2 | "this is the body for news article id 1"
3 | "this is the headline for news article id 2"
4 | "this is the body for the news article id 2"

I have it like this because i have more than one language so I have content_english content_spanish

 

[r937]

then you might want to try this --

SELECT n. *
FROM news n
INNER JOIN content_english c
ON c.id IN ( n.headline, n.body, n.live )
AND c.content_general LIKE '%Glasgow%'


r937.com | rudy.ca

 

[letimati]

This gives back duplicate entrys i.e. it gives back two entrys if the word is in the heading and the body. Plus the live is a boolean value i want to check is one so i dont give a none live article in the search.

 

[letimati]

I changed it to this

SELECT DISTINCT (
n.id
), n . *
FROM news n
INNER JOIN content_english c ON c.id
IN (
n.headline, n.body
)
AND c.content_general LIKE '%Glasgow%'

and it works

 

[r937]

nope -- DISTINCT is not a function

and in this case you don't need it anyway

r937.com | rudy.ca

 

[letimati]

Ok again before i start thanks for the help. I have changed it slightly to deal with another field 'author' that is not in the content table.


SELECT n. *
FROM news n
INNER JOIN content_english c ON c.id
IN (
n.headline, n.body
)
AND c.content_general LIKE '%test%'
AND n.live =1
OR (
n.author LIKE '%test%'
AND n.live =1
)

This works when i have tested it quickly but is there a better way of doing the OR?

 

[r937]

yes, that last query is almost certainly not going to return what you want

ANDs take precedence over ORs, so if you write

a AND b AND c OR d

then this is equivalent to


( a AND b AND c ) OR d


r937.com | rudy.ca

 

[PHV]

SELECT n. *
FROM news n
INNER JOIN content_english c ON c.id IN (n.headline, n.body)
AND (c.content_general LIKE '%test%' OR n.author LIKE '%test%')
AND n.live =1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[letimati]

Great this works perfect.