[Resolved] Building Next/Prev Buttons for Query Results

Davidc316

Today I spent some time carefully going through the article on this website which explains how to add next, previous and page number buttons when a result is returned from a MySQL database query.

(the article can be found at http://www.phpbuilder.com/columns/rod20000221.php3)

I'm having one or two problems with the guy's proposed solution, but for fear of going into a state of severe overwhelm, I'd like to just focus on two issues that seem to crop up when I go through the method that's explained in the article:

FIRST PROBLEM: When you do a search with the limit set at 10 (as recommended and discussed in the article) there seems to be a problem when there are more than 10 results on offer.

For example: To test the script I first entered some new records into the database which contained the word "dragon" (an unusual and fairly unique word). Now, when I have one record with dragon or three records or five or eight records that contain the word "dragon" then ALL IS WELL. I can run searches and the script seems to be ok.

BUT, as soon as I have more than ten records in my database which have the word "dragon", then the search returns no results.

SECOND PROBLEM: The first thing that the script does is check for an offset value and if there is no offset, then the script declares the offset to be 1.

But here's the problem... MySQL offset results start counting from zero! So, if you have three records that contain the result you're looking for then the PHPbuilder script will only give you two results.

This strikes me as being a serious and somewhat amature blunder from some a respectable website article. Have I missed something???

Thanks!

pete_bisby

I think the offset should have been zero, not 1 - Zero was stated within the commented lines.

I wouldn't call it a "serious blunder", but a small typo ..... nobody's perfect!

Remember:

To err is human; to forgive, divine

Davidc316

Thanks.

I've got to admit, I never even noticed the comments box saying that it should be zero.

I guess it's safe to put my second problem down to a typo in the article.

But that still leaves the first problem.

Can anyone explain why search results where the num of rows is greater than the limit are NOT returned and treated as if the script didn't find any results?

Davidc316

After several hours of research, I'm convinced that the problem lies with the MySQL query that I'm executing. But, I'm not sure what the problem is.

I'm doing a full text search and my query line is as follows:

$query = " 
        SELECT *, 
            MATCH(petname, pettype, petdescription) AGAINST ('$fixinterest') AS score 
        FROM pet 
        WHERE MATCH(petname, pettype, petdescription) AGAINST('$fixinterest') order by petname limit $offset,$limit 
    ";

Davidc316

Ok, I'm sorry for talking to myself, but this is getting freaky and I hope someone can sort this out.

I've been experimenting with MySQL and it turns out that if I do a basic mysql search of the format:

SELECT * FROM tablename WHERE MATCH(name, type, description) AGAINST ("searchword");

...then it can only handle up to ten results.

If there are ANY MORE than ten valid results then the search will display the result 'Empty Set'.

Folks, this is not some bizzare and awkward piece of script I'm using here. Please do not dismiss me as some guy with a weird and unique piece of code. I'm using the search command that's recommended in the MySQL manual and elsewhere!!!

What's going on here?????????

Is the conclusion to all of this that MySQL can't handle full text searches that yeild more than 10 results??? That would be the shock of the century.

Davidc316

I think I've got it!

The trouble was that mysql (quite rightly!) will not deliver a result if more than 50% of the rows contain the queeried string.

So, it just so happens that my table was 20 rows long and when I added the extra row that contained the magic word, then suddenly more than 50% of the rows were valid and "empty set" was returned.

I've not even tested this yet, but I'm sure I'm right.

That concludes two days of hell for me.

Thanks for the help guys.

Phewwww!!! :o

stolzyboy

FYI:

quick search of mysql.com, found user contributed notes

Note: you should add at least 3 rows to the table
before you try to match anything, and what you're
searching for should only be contained in one of
the three rows. This is because of the 50%
thereshold. If you insert only one row, then now
matter what you search for, it is in 50% or more
of the rows in the table, and therefore
disregarded.

Davidc316

Yes, I remember reading that.

Of course, the trouble in my case was that I had a table with 20 rows so the warning in the MySQL manual didn't set off the alarm bells that it should have.