[RESOLVED] Simple Math Puzzler - Searching for name and page it appears on?

FuturShoc

I have a MySQL database (several tables which include sets of alphabetical names, a phone book).
I get the data for these tables as a flat file and import them into the tables. At that time, my PHP script and MySQL auto-increment assigns a sequential number for each name. (Data is pre-sorted, so no sorting goes on via MySQL). * Although this does involve some MySQL, the majority is PHP-specific, so I think it belongs in this forum.

In the application, these names are presented in a paged format and its searchable.
Now, my application uses a search script to find the page number on which a found name lives by doing a bit of simple math based on its sequential ID number.

My problem is that although much of the time it'll find the correct page and work beautifully, many times the page it calculates is ONE PAGE short. (Meaning, the page it finds is one lower than the correct one.) My script never has trouble finding the desired name or the next closest one. It just takes the user to the wrong page sometimes. It seems to me that it MUST have something to do with the math I'm doing at the end.

When the search string comes into the script, it splits the string into individual characters so that it can run a loop and search for a name which includes all characters. If no result is found, it runs again with one less character on the end until it finds the next closest match.

A couple of examples:

Good results that go to the proper page:

ID NAME
1780 Bean Clay & Judy
1694 Batson Elijah

BAD results that go to the WRONG page (one page short):

ID NAME
6782 Daniel Alvin L
8595 Ellison Calvin

$Found_Row = array();

$chars = preg_split('//', $name, -1, PREG_SPLIT_NO_EMPTY);
$char_count = count($chars);
$c = 0;
$found_case = false;

while ($found_case == false) {

if ($c == 0) {	
   $search_phrase = "name LIKE '$name%'";
} else {
   $search_phrase = 'name LIKE \'' . substr($name, 0, -$c) . '%\'';	   
} 

$c = $c + 1;
$Link = mysql_connect ($Host, $User, $Pass);
//echo $Query;

$Query = "SELECT * from $Table WHERE " . $search_phrase . " LIMIT 1";
$Result = mysql_db_query($DB, $Query, $Link);
$Row = mysql_fetch_array($Result, MYSQL_ASSOC);
if ($Row) {
  $Found_Row = $Row;
  $found_case = true;
}

}


$found_name = $Found_Row['name'];
$found_id = $Found_Row['id'];
$found = urlencode($found_name);
if (($Found_Row['id'] / 200) <= 1) {
  $page_num = 1;
} else {

  $remainder = fmod($Found_Row['id'], 200);
  $rounded_id = $Found_Row['id'] - $remainder;
  $page_num = ($rounded_id / 200) + 1;

}

mysql_close($Link);

header('Location: ../' . $results_page . '?page=' . $page_num . '&found=' . $found . '#' . $found_id);

I've been banging my head on this for weeks now. I've changed the math up a few different ways using ceil(), floor() and round() with no apparent gain. This has caused some results to be fixed, but others to break, of course.

Here is my live application. It hits the same script regardless of which county you search and the same script for business or residential. All use same script, but the unique table name is passed for searching the specific county. Tables are structured exactly the same.

Also note: When you get redirected to the assumed proper page, the database ID number, found name and page number are all visible in the URL, so you should be able to see all the parts involved.

http://www.hometowndir.net/dirFayette.php

At this point, I'm more than willing to pay something to get a solution to this. Thats a serious and honorable incentive. I mean it. I'm going insane over this.

Many, many thanks in advance.
If you need further details or have a proposal for fixing/troubleshooting the issue, don't hesitate to PM me or something. I need my sanity back.

alimadzi

A couple things first...

Please use the special PHP tags instead of Code tags when citing PHP code. Makes things much easier to read.
You really shouldn't use an auto-increment primary key to denote anything meaningful. You'd be better off adding a column called 'pagenumber' and just storing the page number in there. Save yourself all these gyrations.

That being said, does the following work for you?

$page_num = ceil( $Found_Row['id'] / 200 );

FuturShoc

alimadzi wrote:
A couple things first...

Please use the special PHP tags instead of Code tags when citing PHP code. Makes things much easier to read.

You really shouldn't use an auto-increment primary key to denote anything meaningful. You'd be better off adding a column called 'pagenumber' and just storing the page number in there. Save yourself all these gyrations.

That being said, does the following work for you?
$page_num = ceil( $Found_Row['id'] / 200 );

Apologies. Will do!
Thats a good idea I hadn't considered. I'm assuming you are suggesting that I let PHP generate the incremented number and do the math on-the-fly at import time?
I think I now have a solution. As it turns out, it appears I had an extraneous ORDER BY statement in my SQL code. I didn't think it was doing anything (left over from a snippet I snagged off the web), but someone else pointed out something that made me look again. See, on the first page of my paged results data, a series of records with NO NAME were at the top of the first page. But the "A" listings should have been first, of course. This led me to see that ORDER BY NAME was still in use.

My math was always working correctly.

ORDER BY was sorting the records and causing some to get repositioned in sequence.

Once I eliminated this ORDER BY clause, results were displayed in the order of their import (and order of their sequential ID). Now, all failing searches are successful. (Note, the equation DOES have to refigure if there is no remainder. This is the case with any result whose sequential ID is evenly divisible by 200. ie. the LAST name on any given page.)

:o :o

There IS a god.

I want to thank you for the time you took to respond, though. I really do appreciate that.

alimadzi

No problem. Go ahead and mark this thread as RESOLVED if your problem has been solved.