multi field php search form for mysql

I have created a multi field search form the only problem it is not that funcutional. I need it to be able to search either when the user enters data in a single field and or when they enter it in multi field here is my code.

form.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-
8859-1" />

<title>Passport To Languages Search Filemaker</title>

<link href="default.css" rel="stylesheet" type="text/css" media="screen" />

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

window.onload=function(){

if(document.getElementById&&document.
getElementsByTagName&&document.createElement){

var sfield=document.getElementsByTagName('form')[0].elements[0];

if(!sfield){return};

sfield.onfocus=function(){this.value=''};

sfield.onblur=function(){

if(!this.value){this.value='Enter your search term here'};

}

}

}

</script>

</head>

<body>

<h1>Passport To Languages Search FileMaker</h1>

<div class="maincontainer">

<form method="get" action="processform.php">

Patient First Name:&nbsp;<input type="text" name="searchterm" title="Patient First Name" value=""
class="searchbox" /><br>

Patient Last Name:&nbsp;
<input type="text" name="searchterm1" title="Patient Last Name" value=""
class="searchbox" /><br>


Recipient ID:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="text" name="searchterm2" title="Recipient ID" value=""
class="searchbox" /><br>

Patient Phone:&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="text" name="searchterm4" title="Patient Phone" value=""
class="searchbox" /><br>

date:&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="text" name="searchterm4" title="Date of Service" value=""
class="searchbox" /><br>


<input type="Submit" value="Submit" name="Submit">



</form>

</div>

</body>

</html>


processform.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">


<?php

// include MySQL-processing classes

require_once 'mysql.php';

try{

// connect to MySQL

$db=new MySQL(array ('host'=>'','user'=>'','password'=>'',
'database'=>'mydb'));

($searchterm=$db->escapeString($_GET['searchterm']));
($searchterm1=$db->escapeString($_GET['searchterm1']));
($searchterm2=$db->escapeString($_GET['searchterm2']));
($searchterm3=$db->escapeString($_GET['searchterm3']));
($searchterm4=$db->escapeString($_GET['searchterm4']));


$result=$db->query("SELECT * FROM data WHERE at_case = '$searchterm2' OR  at_date ='$searchterm4' OR at_claimant_phone_number LIKE '%$searchterm3%' OR at_claimant_first_name LIKE '%$searchterm%' OR at_claimant_last_name LIKE '%$searchterm1%'  ");
if(!$result->countRows()){

echo '<div class="maincontainer"><h2>No results were found. Go
back and try a new search.</h2></div>'."n";

}

else{

// display search results

echo '<div class="maincontainer"><h2>Your search criteria
returned '.$result->countRows().' results.</h2>'."";


echo "<table border='1'><center>";
echo "<tr><th>Appointment#</th> <th>Date</th> <th>Time........</th> <th>Language</th> <th>Patient Name</th> <th>Interpreters First Name</th> <th>INTERPRETERS Last..Name</th> </tr>";


while($row=$result->fetchRow()){

echo "<tr onclick=thisfunction();> <td>";

echo "<tr>";
echo "<td>" . $row['appointment_sn_text'] . "</td>";
echo "<td>" . $row['at_date'] . "</td>";
echo "<td>" . $row['at_time'] . "</td>";
echo "<td>" . $row['at_language'] . "</td>";
echo "<td>" . $row['at_claimant_first_name'] . "</td>";
echo "<td>" . $row['INTERPRETERSbyInterpreterSN::ir_first_name'] . "</td>";
echo "<td>" . $row ['INTERPRETERSbyInterpreterSN::ir_last_name'] . "</td>";
echo "<td>" . "<a href='display.php?id=".$row['appointment_sn_text']."'>Display</a>" . "</td>";
echo "</tr>";
}

}

echo "</table>";
 

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

?>

mysql.php

<?php

// define 'MySQL' class

class MySQL{

private $conId;

private $host;

private $user;

private $password;

private $database;

private $result;

const OPTIONS=4;

public function __construct($options=array()){

if(count($options)!=self::OPTIONS){


throw new Exception('Invalid number of connection parameters');

}

foreach($options as $parameter=>$value){

if(!$value){

throw new Exception('Invalid parameter '.$parameter);

}

$this->{$parameter}=$value;

}

$this->connectDB();

}

// connect to MySQL

private function connectDB(){

if(!$this->conId=mysql_connect($this->host,$this->user,$this->password)){

throw new Exception('Error connecting to the server');

}

if(!mysql_select_db($this->database,$this->conId)){

throw new Exception('Error selecting database');

}

}

// run query

public function query($query){

if(!$this->result=mysql_query($query,$this->conId)){

throw new Exception('Error performing query '.$query);

}

return new Result($this,$this->result);

}

public function escapeString($value){

return mysql_escape_string($value);

}

}

// define 'Result' class

class Result {

private $mysql;

private $result;

public function __construct(&$mysql,$result){

$this->mysql=&$mysql;

$this->result=$result;

}

// fetch row

public function fetchRow(){

return mysql_fetch_assoc($this->result);

}

// count rows

public function countRows(){

if(!$rows=mysql_num_rows($this->result)){

return false;

}

return $rows;

}

// count affected rows

public function countAffectedRows(){

if(!$rows=mysql_affected_rows($this->mysql->conId)){

throw new Exception('Error counting affected rows');

}

return $rows;

}

// get ID form last-inserted row

public function getInsertID(){

if(!$id=mysql_insert_id($this->mysql->conId)){

throw new Exception('Error getting ID');

}

return $id;

}

// seek row

public function seekRow($row=0){

if(!is_int($row)||$row<0){

throw new Exception('Invalid result set offset');

}

if(!mysql_data_seek($this->result,$row)){

throw new Exception('Error seeking data');

}

}

}

?>

 





lostinfmlandAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OnALearningCurveCommented:
Hi lostinfmland,

You could try using the results of your Get values to build your WHERE string.

see the attached code snippet for an example:

I hope this makes sense.

Cheers,

Mark
($searchterm=$db->escapeString($_GET['searchterm']));
($searchterm1=$db->escapeString($_GET['searchterm1']));
($searchterm2=$db->escapeString($_GET['searchterm2']));
($searchterm3=$db->escapeString($_GET['searchterm3']));
($searchterm4=$db->escapeString($_GET['searchterm4']));

//Initialise Where Clause string
$whereClause = "WHERE ";

//Add sections to where clause if values have been input by the user
if ($searchterm != "") {
	$whereClause = "$whereClause at_claimant_first_name LIKE '%$searchterm%' OR";
}
	
if ($searchterm1 != "") {
	$whereClause = "$whereClause at_claimant_last_name LIKE '%$searchterm1%' OR";
}

if ($searchterm2 != "") {
	$whereClause = "$whereClause at_case = '$searchterm2' OR";
}

if ($searchterm3 != "") {
	$whereClause = "$whereClause at_claimant_phone_number LIKE '%$searchterm3%' OR";
}	
	
if ($searchterm4 != "") {
	$whereClause = "$whereClause at_date ='$searchterm4' OR";
}	

// Check length of Where Clause  - if 6 i.e. "WHERE " then no option selected so set string to "" otherwise remove the trailing "OR" by taking off the last two characters
if (strlen($whereClause) = 6) {
	$whereClause = "";
} else {
	$whereClause = substr($whereClause,0,(strlen($whereClause))-2);

$result=$db->query("SELECT * FROM data $whereClause"); 
if(!$result->countRows()){

Open in new window

0
lostinfmlandAuthor Commented:
I keep on getting a Can't use function return value in write context in processform.php on line 48 here is my code.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">


<?php

// connect to MySQL
$con = mysql_connect("75.145.64.202","root","Pd0296/A");  

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

 mysql_select_db("mydb", $con);  

$searchterm=$_GET['searchterm'];
$searchterm1=$_GET['searchterm1'];
$searchterm=$GET['searchterm2'];
$searchterm3=$_GET['searchterm3'];
$searchterm4=$_GET['searchterm4'];

//Initialise Where Clause string
$whereClause = "WHERE ";

//Add sections to where clause if values have been input by the user
if ($searchterm != "") {
      $whereClause = "$whereClause at_claimant_first_name LIKE '%$searchterm%' OR";
}
      
if ($searchterm1 != "") {
      $whereClause = "$whereClause at_claimant_last_name LIKE '%$searchterm1%' OR";
}

if ($searchterm2 != "") {
      $whereClause = "$whereClause at_case = '$searchterm2' OR";
}

if ($searchterm3 != "") {
      $whereClause = "$whereClause at_claimant_phone_number LIKE '%$searchterm3%' OR";
}      
      
if ($searchterm4 != "") {
      $whereClause = "$whereClause at_date ='$searchterm4' OR";
}      

// Check length of Where Clause  - if 6 i.e. "WHERE " then no option selected so set string to "" otherwise remove the trailing "OR" by taking off the last two characters
if (strlen($whereClause) = 6) {
      $whereClause = "";
} else {
      $whereClause = substr($whereClause,0,(strlen($whereClause))-2);

$result=$db->query("SELECT * FROM data $whereClause");
if(!$result->countRows()){


echo '<div class="maincontainer"><h2>No results were found. Go
back and try a new search.</h2></div>'."n";

}

else{

// display search results

echo '<div class="maincontainer"><h2>Your search criteria
returned '.$result->countRows().' results.</h2>'."";


echo "<table border='1'><center>";
echo "<tr><th>Appointment#</th> <th>Date</th> <th>Time........</th> <th>Language</th> <th>Patient Name</th> <th>Interpreters First Name</th> <th>INTERPRETERS Last..Name</th> </tr>";


while($row=$result->fetchRow()){

echo "<tr onclick=thisfunction();> <td>";

echo "<tr>";
echo "<td>" . $row['appointment_sn_text'] . "</td>";
echo "<td>" . $row['at_date'] . "</td>";
echo "<td>" . $row['at_time'] . "</td>";
echo "<td>" . $row['at_language'] . "</td>";
echo "<td>" . $row['at_claimant_first_name'] . "</td>";
echo "<td>" . $row['INTERPRETERSbyInterpreterSN::ir_first_name'] . "</td>";
echo "<td>" . $row ['INTERPRETERSbyInterpreterSN::ir_last_name'] . "</td>";
echo "<td>" . "<a href='display.php?id=".$row['appointment_sn_text']."'>Display</a>" . "</td>";
echo "</tr>";
}



echo "</table>";
 

 

?>



0
OnALearningCurveCommented:
My Bad,

try changing line 48

From

if (strlen($whereClause) = 6) {

to

if (strlen($whereClause) == 6) {

and see if it works,

Cheers,

Mark.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

lostinfmlandAuthor Commented:
I am now gettin an unexepected $end
0
OnALearningCurveCommented:
I missed the "}" from the else section of the if clause

Please change the following lines:

if (strlen($whereClause) = 6) {
      $whereClause = "";
} else {
      $whereClause = substr($whereClause,0,(strlen($whereClause))-2);


TO:

if (strlen($whereClause) = 6) {
      $whereClause = "";
} else {
      $whereClause = substr($whereClause,0,(strlen($whereClause))-2);
}


and let me know
0
OnALearningCurveCommented:
In addition to this

the code you posted above ID: 35366951 seems to be missing a final closing "}" in your last else statement where the search results are displayed.

Hope this helps,

Mark.
0
lostinfmlandAuthor Commented:
I am not getting any results just a blank page no errors here is the code

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">


<?php

// include MySQL-processing classes

require_once 'mysql.php';

 

// connect to MySQL

$db=new MySQL(array ('host'=>','=>'','password'=>'',
'database'=>'mydb'));

$searchterm=$db->escapeString($_GET['searchterm']);
$searchterm1=$db->escapeString($_GET['searchterm1']);
$searchterm2=$db->escapeString($_GET['searchterm2']);
$searchterm3=$db->escapeString($_GET['searchterm3']);
$searchterm4=$db->escapeString($_GET['searchterm4']);



//Initialise Where Clause string
$whereClause = "WHERE ";

//Add sections to where clause if values have been input by the user
if ($searchterm != "") {
      $whereClause = "$whereClause at_claimant_first_name LIKE '%$searchterm%' OR";
}
      
if ($searchterm1 != "") {
      $whereClause = "$whereClause at_claimant_last_name LIKE '%$searchterm1%' OR";
}

if ($searchterm2 != "") {
      $whereClause = "$whereClause at_case = '$searchterm2' OR";
}

if ($searchterm3 != "") {
      $whereClause = "$whereClause at_claimant_phone_number LIKE '%$searchterm3%' OR";
}      
      
if ($searchterm4 != "") {
      $whereClause = "$whereClause at_date ='$searchterm4' OR";
}      

// Check length of Where Clause  - if 6 i.e. "WHERE " then no option selected so set string to "" otherwise remove the trailing "OR" by taking off the last two characters
if (strlen($whereClause) == 6) {
      $whereClause = "";
} else {
      $whereClause = substr($whereClause,0,(strlen($whereClause))-2);


$result=$db->query("SELECT * FROM data $whereClause");
if(!$result->countRows()){

$result=$db->query("SELECT * FROM data WHERE at_date = '$searchterm4' AND at_case = '$searchterm2'");
if(!$result->countRows()){

echo '<div class="maincontainer"><h2>No results were found. Go
back and try a new search.</h2></div>'."n";

}

else{

// display search results

echo '<div class="maincontainer"><h2>Your search criteria
returned '.$result->countRows().' results.</h2>'."";


echo "<table border='1'><center>";
echo "<tr><th>Appointment#</th> <th>Date</th> <th>Time........</th> <th>Language</th> <th>Patient Name</th> <th>Interpreters First Name</th> <th>INTERPRETERS Last..Name</th> </tr>";


while($row=$result->fetchRow()){

echo "<tr onclick=thisfunction();> <td>";

echo "<tr>";
echo "<td>" . $row['appointment_sn_text'] . "</td>";
echo "<td>" . $row['at_date'] . "</td>";
echo "<td>" . $row['at_time'] . "</td>";
echo "<td>" . $row['at_language'] . "</td>";
echo "<td>" . $row['at_claimant_first_name'] . "</td>";
echo "<td>" . $row['INTERPRETERSbyInterpreterSN::ir_first_name'] . "</td>";
echo "<td>" . $row ['INTERPRETERSbyInterpreterSN::ir_last_name'] . "</td>";
echo "<td>" . "<a href='display.php?id=".$row['appointment_sn_text']."'>Display</a>" . "</td>";
echo "</tr>";
}

}

}

echo "</table>";
 

}

{

 

exit();

}

?>


0
OnALearningCurveCommented:
OK,

Can you echo out the complete SQL query and then run it against your database to see firstly if the query looks right and then secondly if it does actually return any results.

My guess is that I've either got the query wrong or it is returning an empty result set.
0
lostinfmlandAuthor Commented:
Sorry you were dead on I made an error and I think it is now all working here is the final code I used:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">


<?php

// include MySQL-processing classes

require_once 'mysql.php';

try{

// connect to MySQL

$db=new MySQL(array ('host'=>,'user'=>'','=>'',
'database'=>'mydb'));

($searchterm=$db->escapeString($_GET['searchterm']));
($searchterm1=$db->escapeString($_GET['searchterm1']));
($searchterm2=$db->escapeString($_GET['searchterm2']));
($searchterm3=$db->escapeString($_GET['searchterm3']));
($searchterm4=$db->escapeString($_GET['searchterm4']));

//Initialise Where Clause string
$whereClause = "WHERE ";

//Add sections to where clause if values have been input by the user
if ($searchterm != "") {
      $whereClause = "$whereClause at_claimant_first_name LIKE '%$searchterm%' OR";
}
      
if ($searchterm1 != "") {
      $whereClause = "$whereClause at_claimant_last_name LIKE '%$searchterm1%' OR";
}

if ($searchterm2 != "") {
      $whereClause = "$whereClause at_case = '$searchterm2' OR";
}

if ($searchterm3 != "") {
      $whereClause = "$whereClause at_claimant_phone_number LIKE '%$searchterm3%' OR";
}      
      
if ($searchterm4 != "") {
      $whereClause = "$whereClause at_date ='$searchterm4' OR";
}      

// Check length of Where Clause  - if 6 i.e. "WHERE " then no option selected so set string to "" otherwise remove the trailing "OR" by taking off the last two characters
if (strlen($whereClause) ==6) {
      $whereClause = "";
} else {
      $whereClause = substr($whereClause,0,(strlen($whereClause))-2);
}



$result=$db->query("SELECT * FROM data $whereClause");
if(!$result->countRows()){


echo '<div class="maincontainer"><h2>No results were found. Go
back and try a new search.</h2></div>'."n";

}

else{

// display search results

echo '<div class="maincontainer"><h2>Your search criteria
returned '.$result->countRows().' results.</h2>'."";


echo "<table border='1'><center>";
echo "<tr><th>Appointment#</th> <th>Date</th> <th>Time........</th> <th>Language</th> <th>Patient Name</th> <th>Interpreters First Name</th> <th>INTERPRETERS Last..Name</th> </tr>";


while($row=$result->fetchRow()){

echo "<tr onclick=thisfunction();> <td>";

echo "<tr>";
echo "<td>" . $row['appointment_sn_text'] . "</td>";
echo "<td>" . $row['at_date'] . "</td>";
echo "<td>" . $row['at_time'] . "</td>";
echo "<td>" . $row['at_language'] . "</td>";
echo "<td>" . $row['at_claimant_first_name'] . "</td>";
echo "<td>" . $row['INTERPRETERSbyInterpreterSN::ir_first_name'] . "</td>";
echo "<td>" . $row ['INTERPRETERSbyInterpreterSN::ir_last_name'] . "</td>";
echo "<td>" . "<a href='display.php?id=".$row['appointment_sn_text']."'>Display</a>" . "</td>";
echo "</tr>";
}

}

echo "</table>";
 

}

catch(Exception $e){

echo $e->getMessage();

exit();

}

?>


0
OnALearningCurveCommented:
Nice one,

Glad you managed to get it working.

Mark.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lostinfmlandAuthor Commented:
Awsome thankyou
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.