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.

 

[letimati]

Hi How is everyone today?

I have to add another bit onto this query yous helped me with. I now have to check the author is in my author table so

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

AND n.author IN the table author a

n.author is the primary key field of the author table.

n.author = 29

author table
id | name | email
29 | john | john@blah.blah

Thanks in advance

 

[letimati]

I did it like this and thought i should post it here anyway.

SELECT n. *
FROM news n, author a
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
AND n.author = a.id

I imagine there is a more elegent way of doing it but hay ho

thanks

 

[PHV]

SELECT n.*
FROM news n
INNER JOIN author a ON n.author=a.id
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]

Hi i am back again as i have been thinking about the way i do my search and i think it would be better for me to get the content_general entry from this query rather than getting the ids. However this would mean i would have to get entries from more than one row in the content_english 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.

Now the query i use (got from this thread, thanks) returns me the entry of the news article which satisfies the search result. So I added a little bit to give me the content field as well so i have this query now

SELECT n.* , c.content_general
FROM news n
INNER JOIN author a ON n.author=a.id
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

However this means i still have to go back to the db to get the content_general field for the news title cause i only have an id for that entry with this query.

Now usually when i get the details of a news article i go back to get the content_general from the db with another query - this i know is not very efficient. For example, if i wanted to print to screen a news article i would get the details of the news article with a query

SELECT * FROM news WHERE id = 1

put it in $news

then for each content id i have i would go back to the db.

SELECT content_general FROM news WHERE id = '$news[headline]'
SELECT content_general FROM news WHERE id = '$news[body]'

So i would like to combine all these queries into one

I came up with this
SELECT c.content_general, c2.content_general
FROM content_english c, content_english c2, news n
WHERE c.id = a.headline
AND c2.id = a.body
AND a.id =7

And it works. However i am sure there is a more efficent way of doing it, plus the results are both named content_general and i was wondering if there was a way to name the result headings.

Hope this makes sense.

Thanks in advance for any help you can offer.

 

[letimati]

PLEASE IGNORE THE ABOVE POST I HAVE NAILED IT

SELECT c.content_general AS headline, c2.content_general AS body
FROM news n
INNER JOIN content_english c ON c.id = n.headline
INNER JOIN content_english c1 ON c1.id = n.body
WHERE n.id =7