Forum Moderators: coopster
First off, thank you for any assistance in advance. Here's what I have: I have a working script that retrieves customer information based on the customer ID. The script works perfectly and returns the information requested.
Now for the twist, the client has decided they would prefer retrieving the information by customer name instead of Customer ID. I've tried just changing the select statement field, but that just triggered the error message of the if statement. I think the reason is the the variable is looking for a number and obviously, the Customer Name is text. I can't figure where to define the variable, and how to insert it into the script.
Obviously, I'm new to .php. Here's the script:
<script type="text/javascript">
var url = "GetPatient.php?id="; // The server-side script
function handleHttpResponse() {
if (http.readyState == 4) {
if(http.status==200) {
var results=http.responseText;
document.getElementById('divCustomerInfo').innerHTML = results;
}
}
}
function requestCustomerInfo() {
// var sId = document.getElementById("txtCustomerId").value;
var sId = document.getElementById("txtCustomerId").value;
http.open("GET", url + escape(sId), true);
http.onreadystatechange = handleHttpResponse;
http.send(null);
}
function getHTTPObject() {
var xmlhttp;
if(window.XMLHttpRequest){
xmlhttp = new XMLHttpRequest();
}
else if (window.ActiveXObject){
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
if (!xmlhttp){
xmlhttp=new ActiveXObject("Msxml2.XMLHTTP");
}
}
return xmlhttp;
}
var http = getHTTPObject(); // We create the HTTP Object
</script>
Here's GetPatient.php
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Get Patient Data</title>
<?php
//customer ID
$sID = $_GET["id"];
//variable to hold customer info
$sInfo = "";
//database information
$sDBServer = 'dbamd.db.#*$!#*$!.example.com';
$sDBName = 'example';
$sDBUsername = 'example';
$sDBPassword = 'example';
//create the SQL query string
$sQuery = "Select * from jos_jforms_66e92 where hbf3c=".$sID;
//make the database connection
$oLink = mysql_connect($sDBServer,$sDBUsername,$sDBPassword);
@mysql_select_db($sDBName) or $sInfo = "Unable to open database";
if($sInfo == '') {
if($oResult = mysql_query($sQuery) and mysql_num_rows($oResult) > 0) {
$aValues = mysql_fetch_array($oResult,MYSQL_ASSOC);
$sInfo = $aValues['hbf3c']."<br />".$aValues['h5418']."<br />".
$aValues['h22fd']."<br />"."</a>";
} else {
$sInfo = "Patient $sID doesn't exist.";
}
}
mysql_close($oLink);
?>
</head>
<body>
<div id="divInfoToReturn"> <?php echo $sInfo ?> </div>
</body>
Here's the html that displays the results
<p>Enter Patient ID to retrieve information:</p>
<p>Patient ID: <input type="text" id="txtCustomerId" value=""></p>
<p><input type="button" value="Get Patient Info" onclick="requestCustomerInfo()"></p>
<div id="divCustomerInfo"></div>
Thanks.
Joe
</html>
[edited by: dreamcatcher at 6:49 am (utc) on Dec. 30, 2009]
[edit reason] Removed specifics. [/edit]
the client has decided they would prefer retrieving the information by customer name instead of Customer ID.
It's going to depend on how you have it stored. if you have first, middle, and last name fields, you have to work out "how" it's going to be searched. That is, if the user enters "John Smith" you can assume the space is the "delimiter" between first and last name. The best way is to have first and last name fields in your form.
Cautious of names like O'Malley. You will have to use mysql_real_escape_string on the input (which you should anyway) or "double up" the single quotes so it doesn't error on input.
I would use the like operator with only the wild card at the end. It's most likely a user will get the first part of a name right, but the last may have typos. If you use the wild card on both sides,
... lname like '%$lname%'
... you'll get matches on Smith, Blacksmith, Blahsmithson, etc. if Smith is the last name. The inverse is also true, so you may want to move the wildcard or have it on both ends.
All that in mind,
$is_first_last=0;
if (preg_match('/\s+/',$_POST['term'])) {
list($fname,$lname) = explode(' ',$_POST['term']);
$is_first_last=1;
}
else { $lname = $_POST['term']; }
if (isset($fname) {
$fname = mysql_real_escape_string($fname);
}
if (isset($lname) {
$lname = mysql_real_escape_string($lname);
}
$sQuery = "Select * from tablename where";
if ($is_first_last==1) {
$sQuery .= " first_name like '".$fname . "%' and";
}
$sQuery .= " last_name like '" . $lname . "%'";
From which you will have one of two queries:
Select * from tablename where last_name like 'Smith%';
or
Select * from tablename where first_name like 'John%' and last_name like 'Smith%';
I may have the list/explode incorrect, may contain errors, typed on the fly, but this should get you started.
Another problem you will face - this type of query will return multiple records. So you will need to use a while loop, and output links to the specific records. You already have the "id" part worked out. Now you'll have to do this:
// (use the select code above)
if($sInfo == '') {
$results=NULL;
$oResult = mysql_query($sQuery);
while ($aValues = mysql_fetch_array($oResult)) {
$id = $aValues['id'];
$fn = $aValues['first_name'];
$ln = $aValues['last_name'];
$results .= '<li><a href="' . $this_script . "?id=$id" .
'">' . "$fn $ln</a></li>\n";
}
if ($results) {
echo "<ul>$results</ul>";
// don't use <br> or <br />, use semantic elements
}
else {
echo "<p>No results found with that search</p>";
}
}
else { echo "<p>No results found.</p>"; }
You can use PHP_SELF instead of a variable, but there are vulnerabilities with it, I prefer to hard code it just as a preference.
Now all you have to do is surround your current script in an if:
if (isset($_GET['id']) and ($_GET['id'] > 0)) {
// do your by-id lookup
}
else if (isset($_POST['term']) and preg_match('/^[a-z0-9\s\-\']+$/i',$_POST['term'])) {
// (The preg just makes sure it's got word characters)
// do the above
}
else {
// output the search form
}
As said, not working code, concept only . . . but this will get you rolling. Last comment: if JS is disabled, it fails . . . be sure to finish up by adding stuff that lets it work without JS, topic for another thread (though there's lots of resources here to show you how.)
Joe
if (isset($_POST['name'])) {
$sQuery = "select * from tablename where name like '" . mysql_real_escape_string($_POST['name']) . "%'";
// execute and output results
}
else {
// output form
}
