Jump to content

Adding a Search mysql to Current Code

mfleming

By mfleming
in PHP Coding Help

 Share

Recommended Posts

mfleming Member

mfleming

Posted
Posted

Hi.

 

I currently have php java to get my database results and limit the amount of data shown by a given value.  The js includes buttons to go forward or backward in the database.  Everything is all fine and dandy except I'm trying to add a very simple search for the user as well.

 

 

I want the user to be able to select either "firstname" or "lastname" to search through the database and display the resulting rows based of the first three characters entered into the search criteria.

 

 

I'm not sure where to start on this... Do I have to add something the js (I'm not very familiar with js), or should I add mysql_query's in the php linked file?

 

 

 

Current Page Code:

Link To Current Page Test 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Ness Physiotherapy and Sports Injury Clinic Web site - Site Map</title>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<meta name="description" content="Ness Physiotherapy and Sports Injury Clinic Our mission is to provide quality, evidenced based service in a safe, friendly, professional environment." />
<meta name="keywords" content="pain,rehabilitation,rehab,exercise,manipulation,musculoskeletal,pain,management,massage therapy,physiotherapy,sports injury,injury,injured,chronic pain,quality care,quality life,muscle balance,spinal fitness assessment, BMR PT, CAFCI, RMT" />
<!--Ness Physiotherapy and Sports Injury Clinic is owned by Sean Springer, Tamara Silvari and Charles Dirks. -->
<meta name="language" content="EN" />
<meta name="copyright" content="Ness Physiotherapy and Sports Injury Clinic" />
<meta name="robots" content="ALL" />
<meta name="document-classification" content="Health" />
<meta name="document-classification" content="Health" />
<meta name="document-rights" content="Copyrighted Work" />
<meta name="document-type" content="Public" />
<meta name="document-rating" content="General" />
<meta name="document-distribution" content="Global" />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<script type="text/javascript" src="../scripts/preloadimages.js"></script>
<script type="text/javascript" src="../scripts/p7exp.js"></script>
<script type="text/javascript" src="../scripts/formajax.js"></script>
<!--[if lte IE 7]>
<style>
#menuwrapper, #p7menubar ul a {height: 1%;}
a:active {width: auto;}
</style>
<![endif]-->
<!--[if IE 5]>
<style type="text/css"> 
/* place css box model fixes for IE 5* in this conditional comment */
.twoColFixRtHdr #sidebar1 { width: 220px; }
</style>
<![endif][if IE]>
<style type="text/css"> 
/* place css fixes for all versions of IE in this conditional comment */
.twoColFixRtHdr #sidebar1 { padding-top: 30px; }
.twoColFixRtHdr #mainContent { zoom: 1; }
/* the above proprietary zoom property gives IE the hasLayout it needs to avoid several bugs */
</style>
<![endif]-->
<link href="../css/p7exp.css" rel="stylesheet" type="text/css" />
<link href="../css/basiclayout.css" rel="stylesheet" type="text/css" />
<link href="../css/general.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" type="text/css" href="../css/form_member_admin.css"/>
</head>
<body class="twoColFixRtHdr" onload="ajaxFunction('fw')">
<div id="container2"> 
  <!-- Header Secion edit header.php if needed-->
  <?php require("../header.php"); ?>
  <div id="BasCon">
    <form id="myForm" action="ajaxFunction(this.form); return false">
      <div>
        <input type="hidden" name="st" value="0" >
        </input>
      </div>
      <table id="tbl_search">
      	<tr>
        	<td colspan="5"><b>MEMBER LIST</b></td>
        </tr>
        <tr>
          <td><input type="button" id="back" value="Prev" onclick="ajaxFunction('bk'); return false" /></td>
          <td align="right"><input type="button" value="Next" id="fwd" onclick="ajaxFunction('fw');  return false" /></td>
        </tr>
        <tr>
          <td colspan="2"><div id="txtHint"><b>Records will be displayed here</b></div></td>
        </tr>
      </table>
     <div class="formdiv">
    <label for="searchby">Search By:</label>
    <?php
	//get databast access file
  require "../dbConfigtest.php";

	$query = "SELECT firstname,lastname FROM $usertable"; 
	$result = mysql_query($query) or die(mysql_error());
	?>
    <select size="1" name="searchby" class="searchby">
    <?php
	$i = 0;
	while ($i < mysql_num_fields($result)){
  		$fieldname = mysql_field_name($result, $i);
  		echo '<option value="'.$fieldname.'">'.$fieldname.'</option>';
  		$i++;
	} 
	?>
    </select>
    
    <label for="newsearch">Search Value:</label>
    <input name='newsearch' type='text' value='' maxlength="32"  />
    <p> </p>
    <input class="button" type="submit" value="Search" name="search" />
    </div><!--End Div formdiv--> 
    <p> </p>
    <p> </p>
    </form>
  </div>
    <!-- This clearing element should immediately follow the #mainContent div in order to force the #container div to contain all child floats -->
  <br class="clearfloat" />
  <div id="footer_abv_2"></div>
  <!-- end #footer abv --> 
  <!-- Footer Secion edit footer.php if needed-->
  <?php require("../footer.php"); ?>
</div>
<!-- end #container2 -->
</body>
</html>

 

PHP Code from "pagelisting.php" which is called from the java.

<?
//get databast access file
include "../dbConfigtest.php";

//////////////////////////////// Main Code sarts /////////////////////////////////////////////
$endrecord=$_GET['endrecord'];// To take care global variable if OFF
if(strlen($endrecord) > 0 and !is_numeric($endrecord)){
echo "Data Error";
exit;
} 

$limit=25;// Number of records per page
$nume=mysql_num_rows(mysql_query("select * from $usertable"));
//echo "endrecord=$endrecord limit=$limit ";
if($endrecord < $limit) {$endrecord = 0;}

switch($_GET['direction'])
{
case "fw":
$eu = $endrecord ;
break;

case "bk":
$eu = $endrecord - 2*$limit;
break;

default:
echo "Data Error";
exit;
break;
}
if($eu < 0){$eu=0;}
$endrecord =$eu+$limit;

$t=mysql_query("select * from $usertable limit $eu,$limit");

$str= "{ \"data\" : [";

while($nt=mysql_fetch_array($t)){
$str=$str."{\"id\" : \"$nt[id]\", \"firstname\" : \"$nt[firstname]\", \"lastname\" : \"$nt[lastname]\", \"email\" : \"$nt[email]\", \"enewsletter\" : \"$nt[enewsletter]\"},";
//$str=$str."{\"myclass\" : \"$nt[class]\"},";

}
$str=substr($str,0,(strLen($str)-1));

if(($endrecord) < $nume ){$end="yes";}
else{$end="no";}

if(($endrecord) > $limit ){$startrecord="yes";}
else{$startrecord="no";}


$str=$str."],\"value\" : [{\"endrecord\" : $endrecord,\"limit\" : $limit,\"end\" : \"$end\",\"startrecord\" : \"$startrecord\"}]}";
echo $str;
//echo json_encode($str);
/////////////////////////////////////////////////////////////////////////////////////////////
?>

 

Current js Code:

function ajaxFunction(val)
{

//document.writeln(val)
var httpxml;
try
  {
  // Firefox, Opera 8.0+, Safari
  httpxml=new XMLHttpRequest();
  }
catch (e)
  {
  // Internet Explorer
  try
    {
    httpxml=new ActiveXObject("Msxml2.XMLHTTP");
    }
  catch (e)
    {
    try
      {
      httpxml=new ActiveXObject("Microsoft.XMLHTTP");
      }
    catch (e)
      {
      alert("Your browser does not support AJAX!");
      return false;
      }
    }
  }
function stateChanged() 
    {
    if(httpxml.readyState==4)
      {
var myObject = eval('(' + httpxml.responseText + ')');


var str="<table><tr><th>LINK</th><th>ID</th><th>FirstName</th><th>LastName</th><th>Email</th></tr>";
for(i=0;i<myObject.data.length;i++)
{ 
//var sPath = window.location.pathname;
//var sPath = sPath.substring(sPath.lastIndexOf('/') + 1);
var sPath = "testing/member_update_test.php";
var site_link = "www.nessphysiotherapy.com/";
var urlLink = site_link + sPath + "?id=" + myObject.data[i].id;
var site_title = "EDIT";
str = str + "<tr><td>" + "<a href=\"http:\/\/" + urlLink + "\" title=\"site_title\">" +site_title + "<\/a>" + "<td>" + myObject.data[i].id + "</td><td>" + myObject.data[i].firstname +  "</td><td>" + myObject.data[i].lastname +  "</td><td>" + myObject.data[i].email + "</td></tr>"
}
var endrecord=myObject.value[0].endrecord 


document.forms.myForm.st.value=endrecord;
if(myObject.value[0].end =="yes"){ document.getElementById("fwd").style.display='inline';
}else{document.getElementById("fwd").style.display='none';}


if(myObject.value[0].startrecord =="yes"){ document.getElementById("back").style.display='inline';
}else{document.getElementById("back").style.display='none';}


str = str + "</table>" 
document.getElementById("txtHint").innerHTML=str;
    }
    }

var url="../scripts/pagelisting.php";
var myendrecord=document.forms.myForm.st.value;
url=url+"?endrecord="+myendrecord;
url=url+"&direction="+val;

url=url+"&sid="+Math.random();
//alert(url)
httpxml.onreadystatechange=stateChanged;
httpxml.open("GET",url,true);
httpxml.send(null);
  document.getElementById("txtHint").innerHTML="Please Wait....";

}

 

Link to comment
Share on other sites
QuickOldCar Regular Member

QuickOldCar

Posted
Posted

I may be able to help.

 

What I do is a multi select for mysql depending on what is selected will bring the different results.

 

These will not be your values, you can modify this to your own needs, but should give you the idea of how can go about it.

 

<?php
$display = mysql_real_escape_string($_GET['display']);
$order = mysql_real_escape_string($_GET['order']);
$search_name = mysql_real_escape_string($_GET['search_name']);
$search_words = mysql_real_escape_string($_GET['search_words']);

//search get variables from search form
if ($search_name == "first_begins_characters") {
    $result = mysql_query("SELECT * FROM users WHERE firstname LIKE '".$search_words."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE firstname LIKE '".$search_words."%'");
} elseif ($search_name == "first_contains_characters") {
    $result = mysql_query("SELECT * FROM users WHERE firstname LIKE '%"."$search_words"."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE firstname LIKE '%"."$search_words"."%'");
} elseif ($search_name == "last_begins_characters") {
    $result = mysql_query("SELECT * FROM users WHERE lastname LIKE '".$search_words."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE lastname LIKE '".$search_words."%'");
} elseif ($search_name == "last_contains_characters") {
    $result = mysql_query("SELECT * FROM users WHERE lastname LIKE '%"."$search_words"."%' ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users WHERE lastname LIKE '%"."$search_words"."%'");
} elseif ($search_name == "all") {
$result = mysql_query("SELECT * FROM users ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users");
} else {

//if anything goes wrong above or nothing selected, this will be used as the default query instead
$result = mysql_query("SELECT * FROM users ORDER BY $display $order LIMIT $startrow,$posts_per_page" );
$total_count = mysql_query("SELECT * FROM users");
}

?>

<form name="input" action="" method="get">
<?php
if (!$_GET['display']) {
$display = "firstname";
}
?>
Display:<Select style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" name="display">
<option "Input" style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $display; ?>"><?php echo $display; ?></option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="firstname">firstname</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="lastname">lastname</option>
</select>
<?php
if (!$_GET['order']) {
$order = "ASC";
}
?>
Order:<Select style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" name="order">
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $order; ?>"><?php echo $order; ?></option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="ASC">Ascending</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="DESC">Descending</option>
</select>

<?php
if (!$_GET['search_name']) {
$search = "all";

}
?>
Search Type:<Select style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" name="search">
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $search; ?>"><?php echo $search; ?></option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="all">all</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="first_begins_characters">User Begins Character(s)</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="first_contains_characters">User Contains Character(s)</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="last_begins_characters">Last Begins Character(s)</option>
<option style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="last_contains_characters">Last Contains Character(s)</option>
</select>
<br />
Search Word(s) or Char(s):<input size="40"type="text" name="search_words" style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="<?php echo $_GET['search_words']; ?>"><?php echo $_GET['search_words']; ?>
<input type="submit" style="color: #FFFFFF; font-family: Verdana; font-weight: bold; font-size: 12px; background-color: #000000;" size="15" value="Search Name" />

</form>

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.