Before I start, here is the sample format of the database:
id date title content
--------------------------------------------------------------
1 YYYY-MM-DD some text here lots of stuff to search here
2 YYYY-MM-DD some text here lots of stuff to search here
3 YYYY-MM-DD some text here lots of stuff to search here
I currently have something set up to get the id of a row, then assign the date, title, and content to PHP variables to display in different places on a page (it's a blog, these are posts).
Now I'm trying to make a function that will search the content for each string in an array (the code already splits words entered into the search box by spaces into an array, so entering cat hotdog suit
will give [0]cat [1]hotdog [2]suit
).
I have a loop set up that performs a query on each of the strings:
foreach ($queryArray as $queryArrayString) {
// query the content column for $queryArrayString
}
However, I can't figure out what query I should use to find the id of the post that the string occurs in. I'd like to assign it to a variable ($results
).
After reversing the array, so higher ids (newer results) show up first, I'd like to find the titles of each id and store it like this:
id (let's say it is 3) => matching title
id (let's say it is 1) => matching title
Is this possible? I've tried several different queries, and none worked, but one took 2 minutes and loads of memory to attempt.
Any help would be appreciated.
EDIT: I've created a text index of the title and content columns (I'd like to search both), shown in this image.
Is it possible to search this? Like I've said in the comments, I'd just need it to return the corresponding id and title somehow. (Title for result link text, id for link destination: <a href="blog?p=[id]">[title]</a>
is the format I'm using