PHP MYSQLI Search query using array as reference

Question

I know my logic here is going to be atrocious so bear with me. In essence, when a user fills out a form consisting of 3 entry boxes(representing 3 different columns in the database), I want to execute a search that takes keywords from the filled boxes and looks for all other user profiles (constituting of a first name, experience, dreams and profile picture) within the database table containing at least X% of the keywords from the initial user's input boxes.

I've decided to represent the keywords in an array since that is what I am most familar with in terms of element organization. Using this array as a reference, the search will scan the users table for any users whose profile information contain X% of the keywords in the reference array.

The array has already been prepared, but now I need guidance as to whether or not the manner in which I want to finish the task is appropriate/valid.

EDIT 1: Forgive me if I am unclear, let me try a more disjointed way of portraying the task.

User fills out 3 unique textarea boxes.
User saves inputted data, in which that data is stored in database (userstable to be specific)
User goes to "Find Others" page
User clicks on "Find" button
Query takes user information of the 3 textareas, filters them individually, then compresses them into a single array of keywords.
1. Another query will search the entire database and display the profiles of other users whose profiles contain X% of words that match the keywords in the array.
2. User sees the profiles of other users with similar words within the information of their profiles.

The array that will contain the keywords:

$compressed_array = array_merge($filtered1, $filtered2, $filtered3);

your question is unclear. could you elaborate a bit what you are trying to achieve? Ideally providing some (pseudo-)code? — Burki
– Burki, Commented Jul 31, 2015 at 8:43
@Burki I would include the code I am considering to conduct the search, but I've never done any search-related tasks at this scale and feel lost. — Mr-Kimbles
– Mr-Kimbles, Commented Jul 31, 2015 at 8:59
what you are describing should be technically possible. The biggest potential problem is performance: depending on the amount of records you are handling your queries will become very slow. Some of that can be overcome by having an index on your columns. If you are planning to have a large amount of records, read up on Solr. — Burki
– Burki, Commented Jul 31, 2015 at 9:01
@Burki Yes, performance was on my mind, thus I was wondering if I can display the results incrementally. So instead of displaying all of the users, only 10-15 users are displayed per session. I would love to utilize Solr in the near future, because right now I just want the code to work before improving technical defficiencies. — Mr-Kimbles
– Mr-Kimbles, Commented Jul 31, 2015 at 9:04
The problems don't stem from the amount of results you display. They start where you try to identify the results in the first place. As long as you are andling a couple of hundreds of records, you should be fine, though. Just keep in mind that high performance full text search is not trivial (follow the link i provided and do some reading, you might find it interesting) — Burki
– Burki, Commented Jul 31, 2015 at 9:06

Burki · Accepted Answer · 2015-07-31 09:19:49Z

1

You may want to try a slightly different approach.
When a user creates their entries, find the keywords and in a matching table store the relation of keywords to users.
This way, you can query the matching table, which should me much faster and easier to query.
Or you use a full-scale search platform, like Solr, which does more or less the same.

Any attempt to search your users table in real time will have huge performance problems as soon as you are handling a substantial amount of records.

answered Jul 31, 2015 at 9:19

Burki

1,22622 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr-Kimbles Over a year ago

The suggestion of a matching table seems more comfortable for me as I have no experience in using programs like Solr(I will certainly adopt it after I get the matching table method working), thank you. How would I setup and utilize the table? Looks like I have more reading to do than expected.

Burki Over a year ago

you need to take a look at the join syntax. If you read up on that, you should find good examples that will point you in the right direction.

Collectives™ on Stack Overflow

PHP MYSQLI Search query using array as reference

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related