[SOLVED] 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.  :'(

[sasa]

look your 1st page

your 1st record #1(Aanstoos Edward & Virginia M) isn't 1st

[FuturShoc]

look your 1st page

your 1st record #1(Aanstoos Edward & Virginia M) isn't 1st

 

Wow, I don't know how I overlooked that.

Ok, I've eliminated those errant records and it does appear to cause "Daniel..." to search and be found correctly.

 

However, the other "Ellison" appears as the FIRST name on the NEXT page.

I can't see how I might change my calculations to account for that. (?)

[sasa]

in 1st page after found=Adams+Normer#121 is found=Adams+Oscar#123

where is #122?

etc.

[FuturShoc]

in 1st page after found=Adams+Normer#121 is found=Adams+Oscar#123

where is #122?

etc.

 

Hmm. I'll bet thats the remaining issue.

When I deleted those no-named records from the front, that also eliminated 5 or so numbers from the overall sequence!

 

I'll re-import the data and update those extraneous records and see if that solves all of this. Will report back shortly.

[FuturShoc]

* 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 you pointed 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 out of sequence. (Though they still had their correct auto-incremented number.)

 

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.)

 

There IS a god.

 

I want to thank you for the time you took to respond, sasa. I really do appreciate that. It was your having noticed and pointed out the incorrect records at the front of the page results which led me to the solution. In my troubleshooting, I did so much searching that never landed me on the first page that I never saw the first page, so I never noticed it.

 

Thank you.

[sasa]

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

[Barand]

If you have missing ids, such a formula falls over. Similarly, if sorted by, say, name if fails.

 

But if you are sorted by name and you want page number for, eg, "Google" then

 

SELECT COUNT(*) FROM tablename WHERE name < 'Google'

 

Now you can apply the formula to the resulting count.

[FuturShoc]

If you have missing ids, such a formula falls over. Similarly, if sorted by, say, name if fails.

 

But if you are sorted by name and you want page number for, eg, "Google" then

 

SELECT COUNT(*) FROM tablename WHERE name < 'Google'

 

Now you can apply the formula to the resulting count.

 

:o

Holy moly. So simple, yet so beautiful.

When I originally wrote this application, this ID increment approach was the only way I could come up with to give me the searching and page numbering at the same time.

 

But your solution makes a guy feel awfully stupid. Arg!

 

Thank you. Wow, and I thought I actually knew what I was doing with this stuff.