OK, here's my problem. I have a database of doctors and physicians. I have a page with a form that allows the user to look up a doctor by location and specialty or any combination thereof. I have a repeat region of 15 on the returned recordset with a navigation bar to get to the other records. However, when I click on NEXT, all hell breaks loose. EVERY record is returned, not just what the user searched for.

An example of the URL for the NEXT record link:
localhost/women2/plookup2.php?&value1=Nashville&value2=Cardiology&Submit=Submit&City=Ashland+City&Specialty=Addiction+Medicine&offset=15

In the above example, all I want to search is Nashville and Cardiology. Why does all that other crap get in there?

Here's the code I have for the entire page:

<?php

// Copyright (c) Interakt Online 2001
// www.interakt.ro/

require("./adodb/adodb.inc.php";
require("./Connections/connwomen.php";
?><?php
$Contacts__strvalue1 = "%";
if (isset($HTTP_POST_VARS["value1"]))
{$Contacts__strvalue1 = $HTTP_POST_VARS["value1"];}
?><?php
$Contacts__strvalue2 = "%";
if (isset($HTTP_POST_VARS["value2"]))
{$Contacts__strvalue2 = $HTTP_POST_VARS["value2"];}
?><?php
$Contacts=$connwomen->Execute("SELECT * FROM Contacts WHERE LastName Not Like '' AND Specialty Not Like '' AND City Like '" . ($Contacts__strvalue1) . "' AND Specialty Like '" . ($Contacts__strvalue2) . "' ORDER BY LastName" or DIE($connwomen->ErrorMsg());
$Contacts_numRows=0;
$Contacts__totalRows=$Contacts->RecordCount();
?><?php
$Repeat1__numRows = 15;
$Repeat1__index= 0;
$Contacts_numRows = $Contacts_numRows + $Repeat1__numRows;
?><?php
// *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

// set the record count
$Contacts_total = $Contacts->RecordCount();

// set the number of rows displayed on this page
if ($Contacts_numRows < 0) { // if repeat region set to all records
$Contacts_numRows = $Contacts_total;
} else if ($Contacts_numRows == 0) { // if no repeat regions
$Contacts_numRows = 1;
}

// set the first and last displayed record
$Contacts_first = 1;
$Contacts_last = $Contacts_first + $Contacts_numRows - 1;

// if we have the correct record count, check the other stats
if ($Contacts_total != -1) {
$Contacts_numRows = min($Contacts_numRows, $Contacts_total);
$Contacts_first = min($Contacts_first, $Contacts_total);
$Contacts_last = min($Contacts_last, $Contacts_total);
}
?><?php
// *** Recordset Stats: if we don't know the record count, manually count them

// set the number of rows displayed on this page
if ($Contacts_numRows < 0 || $Contacts_numRows > $Contacts_total) {
$Contacts_numRows = $Contacts_total;
}

// set the first and last displayed record
$Contacts_last = min($Contacts_first + $Contacts_numRows - 1, $Contacts_total);
$Contacts_first = min($Contacts_first, $Contacts_total);
?><?php $MM_paramName = ""; ?><?php
// *** Move To Record and Go To Record: declare variables

$MM_rs = &$Contacts;
$MM_rsCount = $Contacts_total;
$MM_size = $Contacts_numRows;
$MM_uniqueCol = "";
$MM_paramName = "";
$MM_offset = 0;
$MM_atTotal = false;
$MM_paramIsDefined = ($MM_paramName != "" && isset($$MM_paramName));
?><?php
// *** Move To Record: handle 'index' or 'offset' parameter

if (!$MM_paramIsDefined && $MM_rsCount != 0) {

// use index parameter if defined, otherwise use offset parameter
if(isset($index)){
$r = $index;
} else {
if(isset($offset)) {
$r = $offset;
} else {
$r = 0;
}
}
$MM_offset = $r;

// if we have a record count, check if we are past the end of the recordset
if ($MM_rsCount != -1) {
if ($MM_offset >= $MM_rsCount || $MM_offset == -1) { // past end or move last
if (($MM_rsCount % $MM_size) != 0) { // last page not a full repeat region
$MM_offset = $MM_rsCount - ($MM_rsCount % $MM_size);
}
else {
$MM_offset = $MM_rsCount - $MM_size;
}
}
}

// move the cursor to the selected record
for ($i=0;!$MM_rs->EOF && ($i < $MM_offset || $MM_offset == -1); $i++) {
$MM_rs->MoveNext();
}
if ($MM_rs->EOF) $MM_offset = $i; // set MM_offset to the last possible record
}
?><?php
// *** Move To Record: if we dont know the record count, check the display range

if ($MM_rsCount == -1) {

// walk to the end of the display range for this page
for ($i=$MM_offset; !$MM_rs->EOF && ($MM_size < 0 || $i < $MM_offset + $MM_size); $i++) {
$MM_rs->MoveNext();
}

// if we walked off the end of the recordset, set MM_rsCount and MM_size
if ($MM_rs->EOF) {
$MM_rsCount = $i;
if ($MM_size < 0 || $MM_size > $MM_rsCount) $MM_size = $MM_rsCount;
}

// if we walked off the end, set the offset based on page size
if ($MM_rs->EOF && !$MM_paramIsDefined) {
if (($MM_rsCount % $MM_size) != 0) { // last page not a full repeat region
$MM_offset = $MM_rsCount - ($MM_rsCount % $MM_size);
} else {
$MM_offset = $MM_rsCount - $MM_size;
}
}

// reset the cursor to the beginning
$MM_rs->MoveFirst();

// move the cursor to the selected record
for ($i=0; !$MM_rs->EOF && $i < $MM_offset; $i++) {
$MM_rs->MoveNext();
}
}
?><?php
// *** Move To Record: update recordset stats

// set the first and last displayed record
$Contacts_first = $MM_offset + 1;
$Contacts_last = $MM_offset + $MM_size;
if ($MM_rsCount != -1) {
$Contacts_first = $Contacts_first<$MM_rsCount?$Contacts_first:$MM_rsCount;
$Contacts_last = $Contacts_last<$MM_rsCount?$Contacts_last:$MM_rsCount;
}

// set the boolean used by hide region to check if we are on the last record
$MM_atTotal = ($MM_rsCount != -1 && $MM_offset + $MM_size >= $MM_rsCount);
?><?php
// *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

// create the list of parameters which should not be maintained
$MM_removeList = "&index=";
if ($MM_paramName != "" $MM_removeList .= "&".strtolower($MM_paramName)."=";
$MM_keepURL="";
$MM_keepForm="";
$MM_keepBoth="";
$MM_keepNone="";

// add the URL parameters to the MM_keepURL string
reset ($HTTP_GET_VARS);
while (list ($key, $val) = each ($HTTP_GET_VARS)) {
$nextItem = "&".strtolower($key)."=";
if (!stristr($MM_removeList, $nextItem)) {
$MM_keepURL .= "&".$key."=".urlencode($val);
}
}

// add the URL parameters to the MM_keepURL string
if(isset($HTTP_POST_VARS)){
reset ($HTTP_POST_VARS);
while (list ($key, $val) = each ($HTTP_POST_VARS)) {
$nextItem = "&".strtolower($key)."=";
if (!stristr($MM_removeList, $nextItem)) {
$MM_keepForm .= "&".$key."=".urlencode($val);
}
}
}

// create the Form + URL string and remove the intial '&' from each of the strings
$MM_keepBoth = $MM_keepURL."&".$MM_keepForm;
if (strlen($MM_keepBoth) > 0) $MM_keepBoth = substr($MM_keepBoth, 1);
if (strlen($MM_keepURL) > 0) $MM_keepURL = substr($MM_keepURL, 1);
if (strlen($MM_keepForm) > 0) $MM_keepForm = substr($MM_keepForm, 1);
?><?php
// *** Move To Record: set the strings for the first, last, next, and previous links

$MM_moveFirst="";
$MM_moveLast="";
$MM_moveNext="";
$MM_movePrev="";
$MM_keepMove = $MM_keepBoth; // keep both Form and URL parameters for moves
$MM_moveParam = "index";

// if the page has a repeated region, remove 'offset' from the maintained parameters
if ($MM_size > 1) {
$MM_moveParam = "offset";
if (strlen($MM_keepMove)> 0) {
$params = explode("&", $MM_keepMove);
$MM_keepMove = "";
for ($i=0; $i < sizeof($params); $i++) {
list($nextItem) = explode("=", $params[$i]);
if (strtolower($nextItem) != $MM_moveParam) {
$MM_keepMove.="&".$params[$i];
}
}
if (strlen($MM_keepMove) > 0) $MM_keepMove = substr($MM_keepMove, 1);
}
}

// set the strings for the move to links
if (strlen($MM_keepMove) > 0) $MM_keepMove.="&";
$urlStr = $PHP_SELF."?".$MM_keepMove.$MM_moveParam."=";
$MM_moveFirst = $urlStr."0";
$MM_moveLast = $urlStr."-1";
$MM_moveNext = $urlStr.($MM_offset + $MM_size);
$MM_movePrev = $urlStr.(max($MM_offset - $MM_size,0));
?>
<html>
<head>
<title>W.O.M.E.N.</title>
<link rel=stylesheet href="css/women.css" type="text/css">
</head>
<body bgcolor=#666666 topmargin=0 leftmargin=0 marginwidth=0 marginheight=0>
<div align=left>
<table border=0 width=100% cellpadding=0 cellspacing=0>
<tr valign=top>
<td class=normal colspan=8 background=images/bg.gif>
<div><img src=images/banner.gif alt="W.O.M.E.N. - Women On Maintaining Education and Nutrition"></div>
</td>
</tr>
<tr>
<td class=normal height=2 bgcolor=#ffffff colspan=8><img src=images/blank.gif height=2 width=1></td>
</tr>
<tr>
<td class=normal height=20 bgcolor=#000000 align=center><a href=index.htm>INTRO</a></td>
<td class=normal height=20 bgcolor=#000000 align=center><a href=overview.htm>OVERVIEW</a></td>
<td class=normal height=20 bgcolor=#000000 align=center><a href=mission.htm>MISSION STATEMENT</a></td>
<td class=normal height=20 bgcolor=#000000 align=center><a href=services.htm>SERVICES</a></td>
<td class=normal height=20 bgcolor=#000000 align=center><a href=plookup.php>PHYSICIAN LOOKUP</a></td>
<td class=normal height=20 bgcolor=#000000 align=center><a href=speakersbureau.htm>SPEAKERS BUREAU</a></td>
<td class=normal height=20 bgcolor=#000000 align=center><a href=newsletter.htm>NEWSLETTER</a></td>
<td class=normal height=20 bgcolor=#000000 align=center><a href=contact.htm>CONTACT</a></td>
</tr>
</table>
<table border=0 width=100% cellpadding=0 cellspacing=0>
<tr>
<td class=normal height=2 bgcolor=#ffffff colspan=7><img src=images/blank.gif height=2 width=1></td>
</tr>
<tr valign=top>
<!-- Beginning of Main Area -->
<td class=normal width=125 align=center bgcolor=#e1e1e1><img src=images/blank.gif height=18 width=125></td>
<td class=normal width=2 bgcolor=#ffffff><img src=images/blank.gif height=1 width=2></td>
<!-- Content -->
<td class=normal bgcolor=#e1e1e1 width=480>
<table border=0 cellspacing=0 cellpadding=0 width=100%>
<tr valign=top>
<td class=normal>
<div align=left><img src=images/section_pl.jpg></div>
</td>
</tr>
<tr>
<td class=normal height=2 bgcolor=#ffffff><img src=images/blank.gif height=2 width=480></td>
</tr>
<tr>
<td class=normal>
<table border=0 width=100% cellpadding=5 cellspacing=0>
<tr valign=top>
<td class=rsheader>
<? if ($Contacts->EOF) {
?>
No Matching Records Found
<? } else {?>
<?php if ($HTTP_POST_VARS["value1"] == ("%") { ?>
All Locations
<?php }
/* if ($HTTP_POST_VARS["value1"] == ("%") */
?>
<?php if ($Contacts->Fields("City" == $HTTP_POST_VARS["value1"]) { ?>
<?php echo $Contacts->Fields("City"?>
, 
<?php echo $Contacts->Fields("StateOrProvince"?>
<?php }
/* if ($Contacts->Fields("City" == $HTTP_POST_VARS["value1"]) */
?>
 :: 
<?php if ($HTTP_POST_VARS["value2"] == ("%") { ?>
All Specialties
<?php }
/* if ($HTTP_POST_VARS["value2"] == ("%") */
?>
<?php if ($Contacts->Fields("Specialty" == $HTTP_POST_VARS["value2"]) { ?>
<?php echo $Contacts->Fields("Specialty"?>
<?php }
/* if ($Contacts->Fields("Specialty" == $HTTP_POST_VARS["value2"]) */
?>
<? } ?>
</td>
</tr>
<tr valign=top>
<td class=normal colspan=2>
<table border=0 width=100% cellpadding=0 cellspacing=0>

<?php while (($Repeat1__numRows-- != 0) && (!$Contacts->EOF))
{
?>
<tr valign=middle>
<td class=normal height=20><?php echo $Contacts->Fields("LastName"?>, <?php echo $Contacts->Fields("FirstName"?><?php if ($Contacts->Fields("MiddleName" != ('')) { ?> <?php echo $Contacts->Fields("MiddleName"?><?php } /* if ($Contacts->Fields("MiddleName" != ('')) */ ?>, <?php echo $Contacts->Fields("Degree"?></td>
<td class=normal><?php echo $Contacts->Fields("City"?></td>
<td class=normal><?php echo $Contacts->Fields("Specialty"?></td>
</tr>
<tr>
<td colspan=3 height=1 bgcolor=#999999><img src=images/blank.gif height=1></td>
</tr>
<?php
$Repeat1__index++;
$Contacts->MoveNext();
}
?>
</table>
<table border=0 width=100% cellpadding=5 cellspacing=0>
<tr>
<td width="25%" align="center">
<?php if ($MM_offset != 0) { ?>
<a class=alt href="<?php echo $MM_moveFirst?>">First</a>
<?php } // end $MM_offset != 0 ?>
</td>
<td width="25%" align="center">
<?php if ($MM_offset != 0) { ?>
<a class=alt href="<?php echo $MM_movePrev?>">Previous</a>
<?php } // end $MM_offset != 0 ?>
</td>
<td width="25%" align="center">
<?php if (!$MM_atTotal) { ?>
<a class=alt href="<?php echo $MM_moveNext?>">Next</a>
<?php } // end !$MM_atTotal ?>
</td>
<td width="25%" align="center">
<?php if (!$MM_atTotal) { ?>
<a class=alt href="<?php echo $MM_moveLast?>">Last</a>
<?php } // end !$MM_atTotal ?>
</td>
</tr>
<tr>
<td class=rsstat colspan=4 align=center>Records <?php echo $Contacts_first?> to <?php echo $Contacts_last?> of <?php echo $Contacts_total?></td>
</tr>
</table>
</td>
</tr>
</table>
<img src=images/blank.gif width=480 height=1> </td>
</tr>
</table>
<!-- End of Main Body -->
</td>
<td class=normal width=2 bgcolor=#ffffff><img src=images/blank.gif height=1 width=2></td>
<td class=normal width=129 bgcolor=#e1e1e1> <img src=images/search_lo.gif alt="Search by Location">
</td>
<td class=normal width=2 bgcolor=#ffffff><img src=images/blank.gif height=1 width=2></td>
<td class=normal bgcolor=#c7c7c7 align=left><img src=images/sidebanner.gif></td>
</tr>
</table>
<table border=0 cellspacing=0 cellpadding=0 width=100%>
<tr>
<td class=normal height=2 bgcolor=#ffffff colspan=7><img src=images/blank.gif height=2 width=1></td>
</tr>
<tr valign=top>
<td class=copyright bgcolor=#a00000><img src=images/blank.gif height=15 width=1></td>
<td class=copyright bgcolor=#a00000 align=center><img src=images/blank.gif></td>
<td class=normal bgcolor=#a00000><img src=images/blank.gif></td>
</tr>
<tr>
<td class=normal height=2 bgcolor=#ffffff colspan=7><img src=images/blank.gif height=2 width=1></td>
</tr>
<tr valign=middle>
<td class=copyright bgcolor=#333333 height=30><img src=images/logo_small.gif></td>
<td class=copyright bgcolor=#333333 height=30>460 10th Circle North       Phone:
615-256-3882<br>
Nashville, TN 37203         Fax:
615-256-3885</td>
<td class=copyright bgcolor=#333333 height=30>
<div align=right>Copyright © 2001 W.O.M.E.N. All Rights Reserved</div>
</td>
</tr>
<tr>
<td class=normal height=2 bgcolor=#ffffff colspan=7><img src=images/blank.gif height=2 width=1></td>
</tr>
</table>
</div>
</body>
</html>
<?php
$Contacts->Close();
?>


ANY SUGGESTIONS?

Thanks,
Bobby




I'm Beginning to See the Light

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
localhost/women2/plookup2.php?&value1=Nashville&value2=Cardiology&Submit=Submit&City=Ashland+City&Specialty=Addiction+Medicine&offset=15
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

In the future do NOT post a full page like this, unless it is requested. As it stands, the issue occurs during a repeat region, so posting that segment of code is enough.

Now, the link. The other "crap" that is in the URL is there because that is what was required to get your page to the way it is when the link is created.

value1 & value2 - these are your parameters
Submit - the submit button
City - Your parameter, obviously used for the search
Specialty - likewise
offset - used in repeat regions to determine where in the Recordset you should be displaying.

The actual issue is caused in this instance because of your SQL:-

SELECT * FROM Contacts WHERE LastName Not Like '' AND Specialty Not Like '' AND City Like '" . ($Contacts__strvalue1) . "' AND Specialty Like '" . ($Contacts__strvalue2) . "' ORDER BY LastName"

Which doesn't look very tidy. Out of interest, when you created your recordset and tested it, did it display all of the records? If it did then this is where your problem is.

I think the issue is down to the NOT LIKE '' entries, and the fact that the value1 and value2 parameters default to %. The % should really be built into the SQL otherwise you will get unpredictable results.

Try the following SQL:-

SELECT * FROM Contacts WHERE City Like '%" . ($Contacts__strvalue1) . "%' AND Specialty Like '%" . ($Contacts__strvalue2) . "%' ORDER BY LastName"

Note that you might have to experiment with the % symbols (removing the one in front of the parameter if you don't want the beginning to be wild etc). But this should work.

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>

Sorry about the long post. I was just trying to be thorough. Since I'm new to PHP, I didn't know where the mistake could be. Anyway...

I tried the SQL statement that you suggested and my recordset looked fine. Thanks.

However, I'm still getting the messed up "next record" URL.

localhost/women2/plookup2.php?&value1=Nashville&value2=Cardiology&Submit=Submit&City=Ashland+City&Specialty=Addiction+Medicine&offset=15

If the form values of "Nashville" and "Cardiology" are the original variables, why do I keep getting "Ashland City" and "Addiction Medicine" in the string. Those two values are the first records of the recordset.

When I click on "Next" I get these errors:
Warning: Undefined index: value1 in c:\program files\apache group\apache\htdocs\women2\plookup2.php on line 275

Warning: Undefined index: value1 in c:\program files\apache group\apache\htdocs\women2\plookup2.php on line 280
::
Warning: Undefined index: value2 in c:\program files\apache group\apache\htdocs\women2\plookup2.php on line 288

Warning: Undefined index: value2 in c:\program files\apache group\apache\htdocs\women2\plookup2.php on line 293

Bobby

The reason that the city and specialty variables are still there is quite simple.

Each time you click on the first|previous|next|last links the page is reloaded. As it is reloaded the recordset is re-created using your SQL, and as you have parameters that are variable in the SQL the moveTo behaviours are aware of this and preserve those parameters.

It is done, so that each time the page refreshes (after clicking the navigational links) the recordset that is generated is the same as the original or things would really go kabloooey (technical term) <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>

That said, the undefined index errors for value1 and value2 are occurring because of the 'Show' behaviours on the page. Funnily I had this error while handcoding today, and it is because for some reason value1 and value2 are not being found, or are blank.

The reason for this is because of your references to $HTTP_POST_VARS["value1"] and $HTTP_POST_VARS["value2"] in your code. Unfortunately these variables are now GET variables as they have been preserved via the Query String.

My advice would be to replace all instances of $HTTP_POST_VARS["value1"] for $value1 and all instances of $HTTP_POST_VARS["value2"] for $value2.

This should get the whole think running, and please post back and let me know your progress.

All the best

Tim Green

Extension & PHP TalkZone Manager
<font size=1>-------------------------------------------
<i>Please read the Forum FAQ before posting
a question to this TalkZone.</i>
-------------------------------------------
www.UDzone.com : A dynamic Dreamweaver,
Ultradev and Fireworks site for developers
by developers.
-------------------------------------------</font id=size1>