search with next/previous button in php/mysql

[UD2006]

I have created a search page (for a contact database), but I would like to add an option that if (searching can be done on surname or zipcode), the surname or zipcode has multiple records, the next/previous button should be displayed.

I only have no idea on how to do that.
The searchpage I have, does have multiple inputfields, checkboxes and a dropdown box where the contact info is being displayed.

Here is the search code I have (I hope I don't have to change the code completely):


<?php

// Get the search variable from URL

$var = @$_GET['search'];
$trimmed = trim($var); //trim whitespace from the stored variable

if ($trimmed) {
// rows to return
$limit=10;

//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("host","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
mysql_select_db("test3") or die("Unable to select database"); //select which database we're using

// Build SQL Query
$sql_query = "SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" ";
// EDIT HERE and specify your table and field names for the SQL query

$result = mysql_query($sql_query) or die(mysql_error());

$check2 = mysql_num_rows($result);
if ($check2 == 0) {
echo('<SCRIPT>
alert("Contact bestaat niet, controlleer of uw goed heeft ingevoerd, eventueel voert u een nieuw contact in."); location.href("index.php");</SCRIPT>');
}
else
{

// now you can display the results returned
$row = mysql_fetch_array($result);

}
}

?>
<script type="text/javascript">
function editcontact()
{
if(document.form3.nbs_contact_id.value == "")
{
alert("Geen contact geselecteerd, vul een zoekterm in zoek veld in!");
return false;
}
else
{
return true;
}
}

</script>
<script type="text/javascript">

function checkNLPostCode(nbs_zip){ // nbs_zip is the value of the postcode field
if (!/^[1-9]\d{3}\s[A-Z]{2}$/.test(nbs_zip)) {
alert (nbs_zip + " is niet de juiste formaat Postcode, zorg dat dit 1234 AA is (met spatie en hoofdletters)!!");
return false;
}
else {
return true;
}
}

</script>
<div id="left">
<div id="left_01">
<div id="toppage">
<h3><?php echo $tcm_label[8]; ?></h3>
</div>
<div style="margin:0px 1px 0px 1px">
<form id="form_ins_contact" name="form3" method="post" action="wijzigen.php">
<table width="780" height="600" border="0" align="center" cellpadding="0" cellspacing="2">
<tr>
<td width="122" align="right"><strong><?php echo $tcm_label[54]; ?></strong></td>
<td class="td_no_border">
<select name="nbs_group_id" id="nbs_group_id">
<option value="1">NBS</option>
</select>
</td>
<td align="right"></td>
<td class="td_no_border2"></td>
<td width="192" align="right" class="td_no_border2"><input name="nbs_contact_id" type="hidden" id="nbs_contact_id" size="10" value="<?php echo ($row['nbs_contact_id']); ?>" /></td>
<td class="td_no_border2"></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[19]; ?></strong></td>
<td width="75" class="td_no_border">
<select name="nbs_contact_aanhef">
<option value="De heer" <?php if ($row['nbs_contact_aanhef'] == "De heer") echo "selected" ?> >Dhr</option>
<option value="Mevrouw" <?php if ($row['nbs_contact_aanhef'] == "Mevrouw") echo "selected" ?> >Mevr</option>
<option value="Familie" <?php if ($row['nbs_contact_aanhef'] == "Familie") echo "selected" ?> >Fam</option>
<option value="De heer, mevrouw" <?php if ($row['nbs_contact_aanhef'] == "De heer, mevrouw") echo "selected" ?> >De heer, mevrouw</option>
<option value="De heer en mevrouw" <?php if ($row['nbs_contact_aanhef'] == "De heer en mevrouw") echo "selected" ?> >De heer en mevrouw</option>
</select>
</td>
<td align="right"><strong><?php echo $tcm_label[61]; ?></strong></td>
<td class="td_no_border"><input name="nbs_companyname" type="text" id="nbs_companyname" size="35" value="<?php echo($row['nbs_companyname']); ?>" /></td>
<td class="td_no_border2"></td>
<td class="td_no_border2"></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[21]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_prechar" type="text" id="nbs_contact_prechar" size="20" value="<?php echo ($row['nbs_contact_prechar']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[22]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_name" type="text" id="nbs_contact_name" size="35" value="<?php echo ($row['nbs_contact_name']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[23]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_tussenv" type="text" id="nbs_contacttussenv" size="15" value="<?php echo ($row['nbs_contact_tussenv']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[24]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_sur" type="text" id="nbs_contact_sur" size="35" value="<?php echo ($row['nbs_contact_sur']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[25]; ?></strong></td>
<td class="td_no_border"><input name="nbs_address" type="text" id="nbs_address" size="45" value="<?php echo ($row['nbs_address']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[26]; ?></strong></td>
<td class="td_no_border"><input name="nbs_number" type="text" id="nbs_number" size="15" value="<?php echo ($row['nbs_number']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[27]; ?></strong></td>
<td class="td_no_border" width="194"><input name="nbs_numberadd" type="text" id="nbs_numberadd" size="15" value="<?php echo ($row['nbs_numberadd']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[28]; ?></strong></td>
<td class="td_no_border"><input name="nbs_postbusnr" type="text" id="nbs_postbusnr" size="20" value="<?php echo ($row['nbs_postbusnr']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[29]; ?></strong></td>
<td class="td_no_border"><input name="nbs_zip" type="text" id="nbs_zip" maxlength="7" size="7" value="<?php echo ($row['nbs_zip']); ?>" onblur="checkNLPostCode(this.value)" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[30]; ?></strong></td>
<td class="td_no_border"><input name="nbs_city" type="text" id="nbs_city" size="45" value="<?php echo ($row['nbs_city']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[31]; ?></strong></td>
<td class="td_no_border"><input name="nbs_country" type="text" id="nbs_country" size="35" value="<?php echo ($row['nbs_country']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[32]; ?></strong></td>
<td class="td_no_border"><input name="nbs_phone" type="text" id="nbs_phone" maxlength="10" size="10" value="<?php echo ($row['nbs_phone']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[33]; ?></strong></td>
<td class="td_no_border"><input name="nbs_mobphone" type="text" id="nbs_mobphone" maxlength="10" size="10" value="<?php echo ($row['nbs_mobphone']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[34]; ?></strong></td>
<td class="td_no_border"><input name="nbs_emailadres" type="text" id="nbs_emailadres" size="45" value="<?php echo ($row['nbs_emailadres']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[35]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_day" type="text" id="nbs_contact_day" maxlength="2" size="1" value="<?php echo ($row['nbs_contact_day']); ?>" />
<input name="nbs_contact_month" type="text" id="nbs_contact_month" maxlength="2" size="1" value="<?php echo ($row['nbs_contact_month']); ?>" />
<input name="nbs_contact_year" type="text" id="nbs_contact_year" maxlength="4" size="3" value="<?php echo ($row['nbs_contact_year']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[36]; ?></strong></td>
<td class="td_no_border"><input name="nbs_lid_vmb" type="checkbox" value="1" <?php if ($row['nbs_lid_vmb'] == "1") {echo "checked";} ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[37]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_selfburns" type="checkbox" <?php if ($row['nbs_selfburns'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[38]; ?></strong></td>
<td class="td_no_border"><input name="nbs_year" type="text" id="nbs_year" maxlength="4" size="4" value="<?php echo ($row['nbs_year']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[39]; ?></strong></td>
<td class="td_no_border"><select name="nbs_relativeto" id="nbs_relativeto">
<option selected="selected" id="0" value="">Selecteer relatie</option>
<option id="1" value="Partner" <?php if ($row['nbs_relativeto'] == "Partner") echo "selected" ?> >Partner</option>
<option id="2" value="Ouder(s)" <?php if ($row['nbs_relativeto'] == "Ouder(s)") echo "selected" ?> >Ouder(s)</option>
<option id="3" value="Broer/zus" <?php if ($row['nbs_relativeto'] == "Broer/zus") echo "selected" ?> >Broer/zus</option>
<option id="4" value="Familie" <?php if ($row['nbs_relativeto'] == "Familie") echo "selected" ?> >Familie</option>
<option id="5" value="Vriend(in)" <?php if ($row['nbs_relativeto'] == "Vriend(in)") echo "selected" ?> >Vriend(in)</option>
</select></td>
<td align="right"><strong><?php echo $tcm_label[62]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_medew_NBD" type="checkbox" <?php if ($row['nbs_medew_NBD'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[40]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_profrelatie" type="checkbox" <?php if ($row['nbs_profrelatie'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[41]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_psychonet" type="checkbox" <?php if ($row['nbs_psychonet'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[42]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_revalinet" type="checkbox" <?php if ($row['nbs_revalinet'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[43]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_reintegranet" type="checkbox" <?php if ($row['nbs_reintegranet'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[44]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_juridischehulp" type="checkbox" <?php if ($row['nbs_juridischehulp'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[45]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_nomail" type="checkbox" <?php if ($row['nbs_nomail'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[58]; ?></strong></td>
<td class="td_no_border">
<ul class="checklist">
<li><label for="1"><input id="1" name="ScarWars_2009" type="checkbox" <?php if ($row['ScarWars_2009'] == "1") echo "checked"; ?> /> ScarWars 2009</label></li>
<li><label for="2"><input id="2" name="Brandwondendag_2009" type="checkbox" <?php if ($row['Brandwondendag_2009'] == "1") echo "checked"; ?> /> Brandwondendag 2009</label></li>
<li><label for="3"><input id="3" name="vijfentwintig_plus_2009" type="checkbox" <?php if ($row['vijfentwintig_plus_2009'] == "1") echo "checked"; ?> /> 25 Plus 2009</label></li>
<li><label for="4"><input id="4" name="ScarTrek_2009" type="checkbox" <?php if ($row['ScarTrek_2009'] == "1") echo "checked"; ?> /> ScarTrek 2009</label></li>
<li><label for="5"><input id="5" name="Zwemmen_2009" type="checkbox" <?php if ($row['Zwemmen_2009'] == "1") echo "checked"; ?> /> Zwemmen 2009</label></li>
<li><label for="6"><input id="6" name="LLL_2009" type="checkbox" <?php if ($row['LLL_2009'] == "1") echo "checked"; ?> /> LLL 2009</label></li>
<li><label for="7"><input id="7" name="Terug_naar_School_2009" type="checkbox" <?php if ($row['Terug_naar_School_2009'] == "1") echo "checked"; ?> /> Terug naar School 2009</label></li>
<li><label for="8"><input id="8" name="Terug_naar_Werk_2009" type="checkbox" <?php if ($row['Terug_naar_Werk_2009'] == "1") echo "checked"; ?> /> Terug naar Werk 2009</label></li>
<li><label for="9"><input id="9" name="Verder_met_littekens_2009" type="checkbox" <?php if ($row['Verder_met_littekens_2009'] == "1") echo "checked"; ?> /> Verder met littekens 2009</label></li>
<li><label for="10"><input id="10" name="P2daagse_2009" type="checkbox" <?php if ($row['P2daagse_2009'] == "1") echo "checked"; ?> /> P2daagse 2009</label></li>
<li><label for="11"><input id="11" name="ScarWars_2010" type="checkbox" <?php if ($row['ScarWars_2010'] == "1") echo "checked"; ?> /> ScarWars 2010</label></li>
<li><label for="12"><input id="12" name="Brandwondendag_2010" type="checkbox" <?php if ($row['Brandwondendag_2010'] == "1") echo "checked"; ?> /> Brandwondendag 2010</label></li>
<li><label for="13"><input id="13" name="vijfentwintig_plus_2010" type="checkbox" <?php if ($row['vijfentwintig_plus_2010'] == "1") echo "checked"; ?> /> 25 Plus 2010</label></li>
<li><label for="14"><input id="14" name="ScarTrek_2010" type="checkbox" <?php if ($row['ScarTrek_2010'] == "1") echo "checked"; ?> /> ScarTrek 2010</label></li>
<li><label for="15"><input id="15" name="Zwemmen_2010" type="checkbox" <?php if ($row['Zwemmen_2010'] == "1") echo "checked"; ?> /> Zwemmen 2010</label></li>
<li><label for="16"><input id="16" name="LLL_2010" type="checkbox" <?php if ($row['LLL_2010'] == "1") echo "checked"; ?> /> LLL 2010</label></li>
<li><label for="17"><input id="17" name="Terug_naar_School_2010" type="checkbox" <?php if ($row['Terug_naar_School_2010'] == "1") echo "checked"; ?> /> Terug naar School 2010</label></li>
<li><label for="18"><input id="18" name="Terug_naar_Werk_2010" type="checkbox" <?php if ($row['Terug_naar_Werk_2010'] == "1") echo "checked"; ?> /> Terug naar Werk 2010</label></li>
<li><label for="19"><input id="19" name="Verder_met_littekens_2010" type="checkbox" <?php if ($row['Verder_met_littekens_2010'] == "1") echo "checked"; ?> /> Verder met littekens 2010</label></li>
<li><label for="20"><input id="20" name="P2daagse_2010" type="checkbox" <?php if ($row['P2daagse_2010'] == "1") echo "checked"; ?> /> P2daagse 2010</label></li>
</ul></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[46]; ?></strong></td>
<td class="td_no_border"><textarea name="nbs_comments" cols="30" rows="6" id="nbs_comments"><?php echo ($row['nbs_comments']); ?></textarea></td>
<td></td>
<td class="td_no_border2"><img src="../images/intra icon.png" width="96" height="96" /></td>
</tr>
</table>
<table width="281" border="0" align="center" cellpadding="0" cellspacing="2">
<tr>
<td align="center" class="td_no_border2" nowrap="nowrap">
<input type="submit" class="buttons" value="Wijzig Contact" onsubmit="return editcontact();"/>
</td>
</tr>
</table>
</form>
</div>
</div>
</div>


Hope someone can help me.

[Coyote6]

If more than one result is found then display all of the matching results and then let the user check which one they need then continue. You will need to add a query to search by id.

// rows to return
$limit=10;
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("host","username","password"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("test3") or die("Unable to select database"); //select which database we're using

// See if id is set.
if (($_GET['id']) && (is_int($_GET['id']))) {
$sql_query = "SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contacts.nbs_contact_id={$_GET['id']}";
}
else {
// Get the search variable from URL
$var = @$_GET['search'];
$trimmed = trim($var); //trim whitespace from the stored variable
if ($trimmed) {
// Build SQL Query
$sql_query = "SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" ";
}

// EDIT HERE and specify your table and field names for the SQL query
$result = mysql_query($sql_query) or die(mysql_error());
$check2 = mysql_num_rows($result);
if ($check2 == 0) {
echo "<SCRIPT>\n" .
" alert('Contact bestaat niet, controlleer of uw goed heeft ingevoerd, eventueel voert u een nieuw contact in.');\n" .
" location.href('index.php');\n" .
"</SCRIPT>";
}
else {
// now you can display the results returned
$row = mysql_fetch_array($result);
}
}

if ($check2 == 1) {
// Leave your table as is.
// From "<table width="780" height="600" border="0" align="center" cellpadding="0" cellspacing="2">"
// To "</table>".
}
else if ($check2 > 1)
// List all the possible solutions.
foreach ($row as $r) {
// Add a method to search by id and then create a link to each one's id to return only 1 result from your search.
echo "<p><a href="searchpage.php?id={$r['nbs_contact_id']}">$r['nbs_companyname']</a>;
}
}

[Coyote6]

Another option and probably a closer look to what you were going for is to create a variable to keep track of which record you are on although this is not great db query design to do this because you will be fetching more records than you need.


if (($_GET['record']) && (is_int($_GET['record']))) {
$record = $_GET['record'];
}

// Your code all the way to the "else' statement of the "if ($check2 == 0) {" clause.
else {
$records = array();
while ($row = mysql_fetch_array ($result)) {
$records[] = $row;
}
}

// Then when you call which the row add which record you want.
// $row[$record]['nbs_companyname']

//Then add two buttons for the next and previous buttons.
$prev = $record - 1;
if (($check2 > 0) && ($prev >= 0)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?search=$var&record=$prev\">&lt;&lt; Previous</a>";
}
$next = $record + 1;
if (($check2 > $next) && ($check2 > 1)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?search=$var&record=$next\">&gt;&gt; Next</a>";
}


Also I noticed that I forgot to put the while ($row = mysql_fetch_array($results)) in the multi-result area on my last post.

[Coyote6]

The way I believe it should be done is using a LIMIT in the query to only allow for one record retrieval at a time and another query to count the records available.


SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" LIMIT $record, 1"



SELECT COUNT(*) FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" "

[Old Pedant]

Yes, Coyote, that's the whole point of paging and the only reasonable way to do it in MySQL queries.

But the missing piece here is that you must *REMEMBER* the query from one page to the next.

He is building the query by looking at form field posting values. The next time he gets to the page (that is, when the user clicks on the NEXT button), those form field post valies will *NOT* be there!

So the answer is to *save* the query in a session value. And each time on the page you look to see if the SUBMIT button's post value is found. If so, you rebuild the query as he is doing here. If not, you just go get the SQL query you saved in the session value and use it.

I'm not a PHP person, so I don't want to try to mangle the PHP code, but the concept is dead simple.

The important part here is to have an *ALWAYS THERE* form field post to the page, as an indication that a new search is to be performed, instead of using the one saved in the session value. I generally use a hidden form field:

<form action="search.php" method="post">
<input type="hidden" name="DOSEARCH" value="YES"/>
...

So in the PHP code you could do:

if ( $_POST["DOSEARCH"] == "YES" )
{
... build the query as he is doing now ...
... then SAVE that query in a session value! ...
} else
$sql_query = ...retrieve it from session value...
}
// here you add the paging logic.
$page = ... however you get it ...

$sql_query .= " LIMIT " . ($page_size * ($page - 1 ) ) . ", " . $page_size;
...

[UD2006]

If more than one result is found then display all of the matching results and then let the user check which one they need then continue. You will need to add a query to search by id.

// rows to return
$limit=10;
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("host","username","password"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("test3") or die("Unable to select database"); //select which database we're using

// See if id is set.
if (($_GET['id']) && (is_int($_GET['id']))) {
$sql_query = "SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contacts.nbs_contact_id={$_GET['id']}";
}
else {
// Get the search variable from URL
$var = @$_GET['search'];
$trimmed = trim($var); //trim whitespace from the stored variable
if ($trimmed) {
// Build SQL Query
$sql_query = "SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" ";
}

// EDIT HERE and specify your table and field names for the SQL query
$result = mysql_query($sql_query) or die(mysql_error());
$check2 = mysql_num_rows($result);
if ($check2 == 0) {
echo "<SCRIPT>\n" .
" alert('Contact bestaat niet, controlleer of uw goed heeft ingevoerd, eventueel voert u een nieuw contact in.');\n" .
" location.href('index.php');\n" .
"</SCRIPT>";
}
else {
// now you can display the results returned
$row = mysql_fetch_array($result);
}
}

if ($check2 == 1) {
// Leave your table as is.
// From "<table width="780" height="600" border="0" align="center" cellpadding="0" cellspacing="2">"
// To "</table>".
}
else if ($check2 > 1)
// List all the possible solutions.
foreach ($row as $r) {
// Add a method to search by id and then create a link to each one's id to return only 1 result from your search.
echo "<p><a href="searchpage.php?id={$r['nbs_contact_id']}">$r['nbs_companyname']</a>;
}
}


Hi sorry for my late repsons, your code gave a an parse error on the last echo, so I changed it into:

echo '<p><a href="index.php?id=zoeken&search=$r[nbs_contact_sur]">$r[nbs_contact_name]</a></p>';

But the page displays a whole list of $r[nbs_contact_name], it does not display the name of a contact from the database to click on. When I click on one of those $r[nbs_contact_name], I get the message:

echo "<SCRIPT>\n" .
" alert('Contact bestaat niet, controlleer of uw goed heeft ingevoerd, eventueel voert u een nieuw contact in.');\n"

which says that the contact does not exists and returns to the index page.

What do I do wrong?

[UD2006]

Ok, I've played a little around with the code, and came till the part that I can get 1 record to display and made a select link to display the full contact info of that record.

But when I search, on surname (I know there are multiple records with the same surname) but it only displays 1 records and not all the records with the same surname.

How to solve that?

Here is the code I played with:

<?php
// rows to return
$limit=10;
//connect to your database ** EDIT REQUIRED HERE **
mysql_connect("host","username","password"); //(host, username, password)
//specify database ** EDIT REQUIRED HERE **
mysql_select_db("test3") or die("Unable to select database"); //select which database we're using

// Get the search variable from URL
$var = @$_GET['search'];
$trimmed = trim($var); //trim whitespace from the stored variable
if ($trimmed) {
// Build SQL Query
$sql_query = "SELECT * FROM nbs_contacts JOIN nbs_events ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id AND nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" ";
}

// EDIT HERE and specify your table and field names for the SQL query
$result = mysql_query($sql_query) or die(mysql_error());
$check2 = mysql_num_rows($result);
if ($check2 == 0) {
echo "<SCRIPT>\n" .
" alert('Contact bestaat niet, controlleer of uw goed heeft ingevoerd, eventueel voert u een nieuw contact in.');\n" .
" location.href('index.php');\n" .
"</SCRIPT>";
}
else {
// now you can display the results returned
$row = mysql_fetch_array($result);
}


// List all the possible solutions.
{
// Add a method to search by id and then create a link to each one's id to return only 1 result from your search.
echo '<hr>';
echo '<Table>';
echo '<tr>';
echo '<td>'.$row['nbs_contact_id'].'</td>';
echo '<td>'.$row['nbs_contact_aanhef'].'</td>';
echo '<td>'.$row['nbs_contact_prechar'].'</td>';
echo '<td>'.$row['nbs_contact_name'].'</td>';
echo '<td>'.$row['nbs_contact_tussenv'].'</td>';
echo '<td>'.$row['nbs_contact_sur'].'</td>';
echo '<td>'.$row['nbs_address'].'</td>';
echo '<td>'.$row['nbs_number'].'</td>';
echo '<td>'.$row['nbs_zip'].'</td>';
echo '<td>'.$row['nbs_city'].'</td>';
echo '<td>'.$row['nbs_phone'].'</td>';
echo '<td><a href="index.php?id=edit&search='.$row['nbs_contact_sur'].' ">Selecteer</a></td>';
echo '</tr>';
echo '</Table>';
}

?>

[Coyote6]

But the missing piece here is that you must *REMEMBER* the query from one page to the next.


So the answer is to *save* the query in a session value. And each time on the page you look to see if the SUBMIT button's post value is found. If so, you rebuild the query as he is doing here. If not, you just go get the SQL query you saved in the session value and use it.


Yes you must remember the search, but you do not have to put it into the session if you keep them in the url. It's good to put it in the session, but if you leave it outside in the url the user can quickly modify the url address if they mistype a name or something but you must be careful that you do not allow non-url safe characters into the search field... Validate so that only numbers and letters are allowed is the simplest way.

So without testing this code I cannot be sure but give this a try...

<?php

// Set the number of results for your page.
$results_per_page = 1;

// Create an escape data function.
function escape_data ($data) {
if (ini_get ('magic_quotes_gpc')) {
$data = stripslashes ($data);
}
return mysql_real_escape_string (trim ($data));
}

// See if the page variable was set and a positive whole number.
if ((isset ($_GET['page'])) && (preg_match('|^[0-9]+$|i', $_GET['page']))) {
$page = $_GET['page'];
}
// Make sure that the page is not set to 0.
else if ($_GET['page'] == 0) {
$page = 1;
}
// If not set the page to 1;
else {
$page = 1;
}

// Set the starting point.
$start = ($page * $results_per_page) - ($results_per_page - 1);

// Check to make sure that a search has been submitted.
if (isset ($_GET['search'])) {
// Connect to database.
mysql_connect("host","username","password");
// Select db.
mysql_select_db("test3") or die("Unable to select database")
// Escape the data to make sure no sql injection can occur.
$search = mysql_real_escape_string ($_GET['search']);
// Query the db to find out if there are any matches and how many.
$q = "SELECT COUNT(*) as count" .
"FROM nbs_contacts " .
"JOIN nbs_events " .
"ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id " .
"WHERE nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\"";
$r = mysql_query($q) or die(mysql_error());
$row = mysql_fetch_assoc ($r);
$count = $row['count'];
// Decide what to do based on the count.
if ($count == 0) {
$message = 'No results were found.';
}
else {
// Query the database to get the needed details.
$q = "SELECT * " .
"FROM nbs_contacts " .
"JOIN nbs_events " .
"ON nbs_events.nbs_contact_id = nbs_contacts.nbs_contact_id " .
"WHERE nbs_contact_sur LIKE \"%$trimmed%\" OR nbs_zip LIKE \"%$trimmed%\" "; .
"LIMIT $start, $results_per_page";
$r = mysql_query($q) or die(mysql_error());
// Make sure results are returned.
if (mysql_num_rows > 0) {
$row = mysql_fetch_assoc ($r);
// Check to see if there is a need for a previous button.
if ($page > 1) {
$prev = $page - 1;
}
// Check to see if there is a need for a next button.
if ($page < $count) {
$next = $page + 1;
}
}
// Else page number is out of range.
else {
$message = 'No results were found.';
}

}
}
// Set a message that no data was set for the search.
else {
$message = 'Please submit some search critea.';
}

if (isset ($message)) {
echo "<p>$message</p>\n";
}
else {
?>
<script type="text/javascript">
function editcontact() {
if(document.form3.nbs_contact_id.value == "") {
alert("Geen contact geselecteerd, vul een zoekterm in zoek veld in!");
return false;
}
else {
return true;
}
}

function checkNLPostCode(nbs_zip){ // nbs_zip is the value of the postcode field
if (!/^[1-9]\d{3}\s[A-Z]{2}$/.test(nbs_zip)) {
alert (nbs_zip + " is niet de juiste formaat Postcode, zorg dat dit 1234 AA is (met spatie en hoofdletters)!!");
return false;
}
else {
return true;
}
}
</script>
<div id="left">
<div id="left_01">
<div id="toppage">
<h3><?php echo $tcm_label[8]; ?></h3>
</div>
<div style="margin:0px 1px 0px 1px">
<form id="form_ins_contact" name="form3" method="post" action="wijzigen.php">
<table width="780" height="600" border="0" align="center" cellpadding="0" cellspacing="2">
<?php
if (isset ($prev)) || (isset ($next)) {
echo " <tr>\n" .
" <td colspan=\"3\" align=\"left\">";
if (isset ($prev)) {
echo "<a href=\"" . $_SERVER['PHP_SELF'] . "?search=$search&page=$prev\">&lt;&lt; Prev</a>";
else {
echo "&nbsp;";
}
echo "</td>\n" .
" <td colspan=\"3\" align=\"right\">";
if (isset ($next)) {
echo "<a href=\"" . $_SERVER['PHP_SELF'] . "?search=$search&page=$next\">Next &gt;&gt;</a>";
else {
echo "&nbsp;";
}
echo "</td>\n" .
echo " <tr>\n" .
}
?>
<tr>
<td width="122" align="right"><strong><?php echo $tcm_label[54]; ?></strong></td>
<td class="td_no_border">
<select name="nbs_group_id" id="nbs_group_id">
<option value="1">NBS</option>
</select>
</td>
<td align="right"></td>
<td class="td_no_border2"></td>
<td width="192" align="right" class="td_no_border2"><input name="nbs_contact_id" type="hidden" id="nbs_contact_id" size="10" value="<?php echo ($row['nbs_contact_id']); ?>" /></td>
<td class="td_no_border2"></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[19]; ?></strong></td>
<td width="75" class="td_no_border">
<select name="nbs_contact_aanhef">
<option value="De heer" <?php if ($row['nbs_contact_aanhef'] == "De heer") echo "selected" ?> >Dhr</option>
<option value="Mevrouw" <?php if ($row['nbs_contact_aanhef'] == "Mevrouw") echo "selected" ?> >Mevr</option>
<option value="Familie" <?php if ($row['nbs_contact_aanhef'] == "Familie") echo "selected" ?> >Fam</option>
<option value="De heer, mevrouw" <?php if ($row['nbs_contact_aanhef'] == "De heer, mevrouw") echo "selected" ?> >De heer, mevrouw</option>
<option value="De heer en mevrouw" <?php if ($row['nbs_contact_aanhef'] == "De heer en mevrouw") echo "selected" ?> >De heer en mevrouw</option>
</select>
</td>
<td align="right"><strong><?php echo $tcm_label[61]; ?></strong></td>
<td class="td_no_border"><input name="nbs_companyname" type="text" id="nbs_companyname" size="35" value="<?php echo($row['nbs_companyname']); ?>" /></td>
<td class="td_no_border2"></td>
<td class="td_no_border2"></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[21]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_prechar" type="text" id="nbs_contact_prechar" size="20" value="<?php echo ($row['nbs_contact_prechar']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[22]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_name" type="text" id="nbs_contact_name" size="35" value="<?php echo ($row['nbs_contact_name']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[23]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_tussenv" type="text" id="nbs_contacttussenv" size="15" value="<?php echo ($row['nbs_contact_tussenv']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[24]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_sur" type="text" id="nbs_contact_sur" size="35" value="<?php echo ($row['nbs_contact_sur']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[25]; ?></strong></td>
<td class="td_no_border"><input name="nbs_address" type="text" id="nbs_address" size="45" value="<?php echo ($row['nbs_address']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[26]; ?></strong></td>
<td class="td_no_border"><input name="nbs_number" type="text" id="nbs_number" size="15" value="<?php echo ($row['nbs_number']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[27]; ?></strong></td>
<td class="td_no_border" width="194"><input name="nbs_numberadd" type="text" id="nbs_numberadd" size="15" value="<?php echo ($row['nbs_numberadd']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[28]; ?></strong></td>
<td class="td_no_border"><input name="nbs_postbusnr" type="text" id="nbs_postbusnr" size="20" value="<?php echo ($row['nbs_postbusnr']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[29]; ?></strong></td>
<td class="td_no_border"><input name="nbs_zip" type="text" id="nbs_zip" maxlength="7" size="7" value="<?php echo ($row['nbs_zip']); ?>" onblur="checkNLPostCode(this.value)" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[30]; ?></strong></td>
<td class="td_no_border"><input name="nbs_city" type="text" id="nbs_city" size="45" value="<?php echo ($row['nbs_city']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[31]; ?></strong></td>
<td class="td_no_border"><input name="nbs_country" type="text" id="nbs_country" size="35" value="<?php echo ($row['nbs_country']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[32]; ?></strong></td>
<td class="td_no_border"><input name="nbs_phone" type="text" id="nbs_phone" maxlength="10" size="10" value="<?php echo ($row['nbs_phone']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[33]; ?></strong></td>
<td class="td_no_border"><input name="nbs_mobphone" type="text" id="nbs_mobphone" maxlength="10" size="10" value="<?php echo ($row['nbs_mobphone']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[34]; ?></strong></td>
<td class="td_no_border"><input name="nbs_emailadres" type="text" id="nbs_emailadres" size="45" value="<?php echo ($row['nbs_emailadres']); ?>" /></td>
<td align="right"><strong><?php echo $tcm_label[35]; ?></strong></td>
<td class="td_no_border"><input name="nbs_contact_day" type="text" id="nbs_contact_day" maxlength="2" size="1" value="<?php echo ($row['nbs_contact_day']); ?>" />
<input name="nbs_contact_month" type="text" id="nbs_contact_month" maxlength="2" size="1" value="<?php echo ($row['nbs_contact_month']); ?>" />
<input name="nbs_contact_year" type="text" id="nbs_contact_year" maxlength="4" size="3" value="<?php echo ($row['nbs_contact_year']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[36]; ?></strong></td>
<td class="td_no_border"><input name="nbs_lid_vmb" type="checkbox" value="1" <?php if ($row['nbs_lid_vmb'] == "1") {echo "checked";} ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[37]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_selfburns" type="checkbox" <?php if ($row['nbs_selfburns'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[38]; ?></strong></td>
<td class="td_no_border"><input name="nbs_year" type="text" id="nbs_year" maxlength="4" size="4" value="<?php echo ($row['nbs_year']); ?>" /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[39]; ?></strong></td>
<td class="td_no_border"><select name="nbs_relativeto" id="nbs_relativeto">
<option selected="selected" id="0" value="">Selecteer relatie</option>
<option id="1" value="Partner" <?php if ($row['nbs_relativeto'] == "Partner") echo "selected" ?> >Partner</option>
<option id="2" value="Ouder(s)" <?php if ($row['nbs_relativeto'] == "Ouder(s)") echo "selected" ?> >Ouder(s)</option>
<option id="3" value="Broer/zus" <?php if ($row['nbs_relativeto'] == "Broer/zus") echo "selected" ?> >Broer/zus</option>
<option id="4" value="Familie" <?php if ($row['nbs_relativeto'] == "Familie") echo "selected" ?> >Familie</option>
<option id="5" value="Vriend(in)" <?php if ($row['nbs_relativeto'] == "Vriend(in)") echo "selected" ?> >Vriend(in)</option>
</select></td>
<td align="right"><strong><?php echo $tcm_label[62]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_medew_NBD" type="checkbox" <?php if ($row['nbs_medew_NBD'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[40]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_profrelatie" type="checkbox" <?php if ($row['nbs_profrelatie'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[41]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_psychonet" type="checkbox" <?php if ($row['nbs_psychonet'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[42]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_revalinet" type="checkbox" <?php if ($row['nbs_revalinet'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[43]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_reintegranet" type="checkbox" <?php if ($row['nbs_reintegranet'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[44]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_juridischehulp" type="checkbox" <?php if ($row['nbs_juridischehulp'] == "1") echo "checked"; ?> /></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[45]; ?></strong></td>
<td class="td_no_border"><input value="1" name="nbs_nomail" type="checkbox" <?php if ($row['nbs_nomail'] == "1") echo "checked"; ?> /></td>
<td align="right"><strong><?php echo $tcm_label[58]; ?></strong></td>
<td class="td_no_border">
<ul class="checklist">
<li><label for="1"><input id="1" name="ScarWars_2009" type="checkbox" <?php if ($row['ScarWars_2009'] == "1") echo "checked"; ?> /> ScarWars 2009</label></li>
<li><label for="2"><input id="2" name="Brandwondendag_2009" type="checkbox" <?php if ($row['Brandwondendag_2009'] == "1") echo "checked"; ?> /> Brandwondendag 2009</label></li>
<li><label for="3"><input id="3" name="vijfentwintig_plus_2009" type="checkbox" <?php if ($row['vijfentwintig_plus_2009'] == "1") echo "checked"; ?> /> 25 Plus 2009</label></li>
<li><label for="4"><input id="4" name="ScarTrek_2009" type="checkbox" <?php if ($row['ScarTrek_2009'] == "1") echo "checked"; ?> /> ScarTrek 2009</label></li>
<li><label for="5"><input id="5" name="Zwemmen_2009" type="checkbox" <?php if ($row['Zwemmen_2009'] == "1") echo "checked"; ?> /> Zwemmen 2009</label></li>
<li><label for="6"><input id="6" name="LLL_2009" type="checkbox" <?php if ($row['LLL_2009'] == "1") echo "checked"; ?> /> LLL 2009</label></li>
<li><label for="7"><input id="7" name="Terug_naar_School_2009" type="checkbox" <?php if ($row['Terug_naar_School_2009'] == "1") echo "checked"; ?> /> Terug naar School 2009</label></li>
<li><label for="8"><input id="8" name="Terug_naar_Werk_2009" type="checkbox" <?php if ($row['Terug_naar_Werk_2009'] == "1") echo "checked"; ?> /> Terug naar Werk 2009</label></li>
<li><label for="9"><input id="9" name="Verder_met_littekens_2009" type="checkbox" <?php if ($row['Verder_met_littekens_2009'] == "1") echo "checked"; ?> /> Verder met littekens 2009</label></li>
<li><label for="10"><input id="10" name="P2daagse_2009" type="checkbox" <?php if ($row['P2daagse_2009'] == "1") echo "checked"; ?> /> P2daagse 2009</label></li>
<li><label for="11"><input id="11" name="ScarWars_2010" type="checkbox" <?php if ($row['ScarWars_2010'] == "1") echo "checked"; ?> /> ScarWars 2010</label></li>
<li><label for="12"><input id="12" name="Brandwondendag_2010" type="checkbox" <?php if ($row['Brandwondendag_2010'] == "1") echo "checked"; ?> /> Brandwondendag 2010</label></li>
<li><label for="13"><input id="13" name="vijfentwintig_plus_2010" type="checkbox" <?php if ($row['vijfentwintig_plus_2010'] == "1") echo "checked"; ?> /> 25 Plus 2010</label></li>
<li><label for="14"><input id="14" name="ScarTrek_2010" type="checkbox" <?php if ($row['ScarTrek_2010'] == "1") echo "checked"; ?> /> ScarTrek 2010</label></li>
<li><label for="15"><input id="15" name="Zwemmen_2010" type="checkbox" <?php if ($row['Zwemmen_2010'] == "1") echo "checked"; ?> /> Zwemmen 2010</label></li>
<li><label for="16"><input id="16" name="LLL_2010" type="checkbox" <?php if ($row['LLL_2010'] == "1") echo "checked"; ?> /> LLL 2010</label></li>
<li><label for="17"><input id="17" name="Terug_naar_School_2010" type="checkbox" <?php if ($row['Terug_naar_School_2010'] == "1") echo "checked"; ?> /> Terug naar School 2010</label></li>
<li><label for="18"><input id="18" name="Terug_naar_Werk_2010" type="checkbox" <?php if ($row['Terug_naar_Werk_2010'] == "1") echo "checked"; ?> /> Terug naar Werk 2010</label></li>
<li><label for="19"><input id="19" name="Verder_met_littekens_2010" type="checkbox" <?php if ($row['Verder_met_littekens_2010'] == "1") echo "checked"; ?> /> Verder met littekens 2010</label></li>
<li><label for="20"><input id="20" name="P2daagse_2010" type="checkbox" <?php if ($row['P2daagse_2010'] == "1") echo "checked"; ?> /> P2daagse 2010</label></li>
</ul></td>
</tr>
<tr>
<td align="right"><strong><?php echo $tcm_label[46]; ?></strong></td>
<td class="td_no_border"><textarea name="nbs_comments" cols="30" rows="6" id="nbs_comments"><?php echo ($row['nbs_comments']); ?></textarea></td>
<td></td>
<td class="td_no_border2"><img src="../images/intra icon.png" width="96" height="96" /></td>
</tr>
</table>
<table width="281" border="0" align="center" cellpadding="0" cellspacing="2">
<tr>
<td align="center" class="td_no_border2" nowrap="nowrap">
<input type="submit" class="buttons" value="Wijzig Contact" onsubmit="return editcontact();"/>
</td>
</tr>
</table>
</form>
</div>
</div>
</div>
<?php
}
?>

It will only show 1 result per page so your update form works, and then you will have a next and previous button appear if more results are available. Sorry in advance for any missed parenthesis or commas or semi colons :rolleyes: