Searching a Database (Multiple Fields)
I checked out mysql.com and saw a few different ways to do it. What I want to do is search a database for keywords and order them by the number of times the keyword appears. So, I would need a COUNT() on the number of apperences of the string per field I would assume. The fields I am searching are both blobs and varchars, the database is under 100 fields so performance isn't that big of an issue. What do you guys think the best method would be to do this, it is for a business directory for my place of employment. You guys pretty much always give me a great answer, and I hope you can come though again.
Here is a preview of the script so far, nothing big, and the presentable layout isn't finished yet.
Thanks
Mark Hensler posted this at 05:01 — 6th January 2001.
He has: 4,048 posts
Joined: Aug 2000
link didn't work for me....
Peter J. Boettcher posted this at 14:05 — 8th January 2001.
They have: 812 posts
Joined: Feb 2000
PDavis,
It depends on how you setup your database. The COUNT(*) function works on a row basis, not field. So if you had a table called Person with the fields FirstName & LastName and 20 records containing a first name of 'john', the following statement:
SELECT COUNT(*) AS Expression, FirstName
FROM Person
WHERE (FirstName = 'john')
GROUP BY FirstName
Would return:
20, john
From the sounds of it you want to search multiple fields within the same row for a particular string, Count won't work for this. You would have to search for the string, then setup a counter and a loop that counts the number of matches for each field.
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.