0

I got a search.php file and a search_results.php file like in my previous thread...the user can select from a dropDown menu what they want to search EmployeeID/EmployeeName/Manager/Department/Team then a text box where they can type in the ID, full name (with spaces) ,Department..ect I got the search for ID, Department, and Team working; however, I want to know how to search for an employee in my DB if the user enters a full name like "John Smith" with the spaces. Is there a query that can search the DB table for the full name and return what it found with first and last name. My DB has a Table named Employee with (EmpID, EmpFName, EmpLName, EmpDept, EmpTeam)

$method=$_POST['searchType'];
if(isset($_POST['searchType']))
{
switch ($method):

    case ($method=='EmployeeFName'):
    if(empty($_POST['query']))
	{	
	exit("Please Fill in a name of an employee you want to search. Click <a href=search.php>here</a> to go back.</p>");
	}
	else
	{
	echo"<i>Your search for {$_POST['query']} had the following results:</i>";
	checkLogin();
	sqlConnect();
	mysql_select_db("titans", $con);
	$XX = "<font color=red>No Record Found</font>";
	$method=$_POST['searchType'];
	$search=$_POST['query'];
	$query = mysql_query("SELECT * FROM Employee WHERE $method = '$search'");
	$result = mysql_query($query);
	$num=mysql_numrows($result); 
	$i=0;
	$space=' ';	
	echo"<table class='full'>";
	echo"<th></th><th>ID</th><th>Employee Name</th><th>Title</th><th>Team</th><th>Department</th></tr>";
	while ($row = mysql_fetch_array($query))
	{
		$variable1=$row["EmployeeFName"];
		$variable2=$row["EmployeeLName"];
		$variable3=$row["EmployeeID"];
		$variable4=$row["EmployeeDepart"];
		$variable5=$row["EmployeeTeam"];
		$variable6=$row["ManagerID"];
		$variable7=$row["EmployeeTitle"]; 
echo"<tr><td class='view'><form action='employee.html' method='post'><div><input type='submit' value='View' class='button' /></div></td><td name='userID' class='id'>{$variable3}</td></form><td name='userName' class='record'>{$variable1}{$space}{$variable2}  </td><td name='userTitle' class='record'>{$variable7}</td><td name='userTeam' class='record'>{$variable5}</td><td name='userDept' class='record'>{$variable4}</td></tr>";
}
	}      
        break;


    case ($method=='EmployeeID'):
    if(empty($_POST['query']))
	{
	exit("Please Fill in the employeeID you want to search. Click <a href=search.php>here</a> to go back.</p>");
	}
	else
	{
	echo"<i>Your search for {$_POST['query']} had the following results:</i>";
	checkLogin();
	sqlConnect();
	mysql_select_db("titans", $con);
	$XX = "<font color=red>No Record Found</font>";
	$method=$_POST['searchType'];
	$search=$_POST['query'];
	$query = mysql_query("SELECT * FROM Employee WHERE $method = '$search'");
	$result = mysql_query($query);
	$num=mysql_numrows($result); 
	$i=0;
	$space=' ';	
	echo"<table class='full'>";
	echo"<th></th><th>ID</th><th>Employee Name</th><th>Title</th><th>Team</th><th>Department</th></tr>";
	while ($row = mysql_fetch_array($query))
	{
		$variable1=$row["EmployeeFName"];
		$variable2=$row["EmployeeLName"];
		$variable3=$row["EmployeeID"];
		$variable4=$row["EmployeeDepart"];
		$variable5=$row["EmployeeTeam"];
		$variable6=$row["ManagerID"];
		$variable7=$row["EmployeeTitle"]; 

echo"<tr><td class='view'><form action='employee.html' method='post'><div><input type='submit' value='View' class='button' /></div></td><td name='userID' class='id'>{$variable3}</td></form><td name='userName' class='record'>{$variable1}{$space}{$variable2}  </td><td name='userTitle' class='record'>{$variable7}</td><td name='userTeam' class='record'>{$variable5}</td><td name='userDept' class='record'>{$variable4}</td></tr>";
}	
	}      
        break;


    case ($method=='EmployeeDepart'):
    if(empty($_POST['query']))
	{
	exit("Please Fill in the employee department you want to search. Click <a href=search.php>here</a> to go back.</p>");
	}
	else
	{
	echo"<i>Your search for {$_POST['query']} had the following results:</i>";
	checkLogin();
	sqlConnect();
	mysql_select_db("titans", $con);
	$XX = "<font color=red>No Record Found</font>";
	$method=$_POST['searchType'];
	$search=$_POST['query'];
	$query = mysql_query("SELECT * FROM Employee WHERE $method = '$search'");
	$result = mysql_query($query);
	$num=mysql_numrows($result); 
	$i=0;
	$space=' ';	
	echo"<table class='full'>";
	echo"<th></th><th>ID</th><th>Employee Name</th><th>Title</th><th>Team</th><th>Department</th></tr>";
	while ($row = mysql_fetch_array($query))
	{
		$variable1=$row["EmployeeFName"];
		$variable2=$row["EmployeeLName"];
		$variable3=$row["EmployeeID"];
		$variable4=$row["EmployeeDepart"];
		$variable5=$row["EmployeeTeam"];
		$variable6=$row["ManagerID"];
		$variable7=$row["EmployeeTitle"]; 

echo"<tr><td class='view'><form action='employee.html' method='post'><div><input type='submit' value='View' class='button' /></div></td><td name='userID' class='id'>{$variable3}</td></form><td name='userName' class='record'>{$variable1}{$space}{$variable2}  </td><td name='userTitle' class='record'>{$variable7}</td><td name='userTeam' class='record'>{$variable5}</td><td name='userDept' class='record'>{$variable4}</td></tr>";
}
	}     
        break;


    case ($method=='EmployeeTeam'):
    if(empty($_POST['query']))
	{		
	exit("Please Fill in the employee team you want to search. Click <a href=search.php>here</a> to go back.</p>");
	}
	else
	{
	echo"<i>Your search for {$_POST['query']} had the following results:</i>";
	checkLogin();
	sqlConnect();
	mysql_select_db("titans", $con);
	$XX = "<font color=red>No Record Found</font>";
	$method=$_POST['searchType'];
	$search=$_POST['query'];
	$query = mysql_query("SELECT * FROM Employee WHERE $method = '$search'");
	$result = mysql_query($query);
	$num=mysql_numrows($result); 
	$i=0;
	$space=' ';	
	echo"<table class='full'>";
	echo"<th></th><th>ID</th><th>Employee Name</th><th>Title</th><th>Team</th><th>Department</th></tr>";
	while ($row = mysql_fetch_array($query))
	{
		$variable1=$row["EmployeeFName"];
		$variable2=$row["EmployeeLName"];
		$variable3=$row["EmployeeID"];
		$variable4=$row["EmployeeDepart"];
		$variable5=$row["EmployeeTeam"];
		$variable6=$row["ManagerID"];
		$variable7=$row["EmployeeTitle"]; 

echo"<tr><td class='view'><form action='employee.html' method='post'><div><input type='submit' value='View' class='button' /></div></td><td name='userID' class='id'>{$variable3}</td></form><td name='userName' class='record'>{$variable1}{$space}{$variable2}  </td><td name='userTitle' class='record'>{$variable7}</td><td name='userTeam' class='record'>{$variable5}</td><td name='userDept' class='record'>{$variable4}</td></tr>";
}
	}      
       break;


    case ($method=='ManagerID'):
    if(empty($_POST['query']))
	{	
	exit("Please Fill in the Manager ID or name you want to search. Click <a href=search.php>here</a> to go back.</p>");
	}
	else
	{
	echo"<i>Your search for {$_POST['query']} had the following results:</i>";
	checkLogin();
	sqlConnect();
	mysql_select_db("titans", $con);
	$XX = "<font color=red>No Record Found</font>";
	$method=$_POST['searchType'];
	$search=$_POST['query'];
	$query = mysql_query("SELECT * FROM Employee WHERE $method = '$search'");
	$result = mysql_query($query);
	$num=mysql_numrows($result); 
	$i=0;
	$space=' ';	
{
	echo"<table class='full'>";
	echo"<th></th><th>ID</th><th>Employee Name</th><th>Title</th><th>Team</th><th>Department</th></tr>";
	while ($row = mysql_fetch_array($query))
	{
		$variable1=$row["EmployeeFName"];
		$variable2=$row["EmployeeLName"];
		$variable3=$row["EmployeeID"];
		$variable4=$row["EmployeeDepart"];
		$variable5=$row["EmployeeTeam"];
		$variable6=$row["ManagerID"];
		$variable7=$row["EmployeeTitle"]; 
if($variable3==$_POST['query'])
{
echo"<tr><td class='view'><form action='employee.html' method='post'><div><input type='submit' value='View' class='button' /></div></td><td name='userID' class='id'>{$variable3}</td></form><td name='userName' class='record'>{$variable1}{$space}{$variable2}  </td><td name='userTitle' class='record'>{$variable7}</td><td name='userTeam' class='record'>{$variable5}</td><td name='userDept' class='record'>{$variable4}</td></tr>";
}
	} 
}	
	}

endswitch;
}
2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by R3Slick
1

You can check for a search by the employee's name. Then you can check for a space. If there is a space you can split the two names with explode and then perform a query that matches first and last name. If there isn't any spaces only perform a query for the first or last name (Depending on what you want).

<?php
if(isset($_POST['searchType'])) //Check first, get values later
{
	$method=$_POST['searchType'];
	$search=$_POST['query'];
	
	switch ($method)
	{
    	case 'EmployeeFName': //Switch is not like if...you don't need an expression, just a value will do
    		if(empty($_POST['query'])) exit("Please Fill in a name of an employee you want to search. Click <a href=search.php>here</a> to go back.</p>");
			else
			{
				echo"<i>Your search for {$search} had the following results:</i>";
				//Here's the magical splitting of the names
				$arr = explode(" ", $search);
				$search_first = $arr[0];
				$search_last = $arr[1];
				
				$query = mysql_query("SELECT * FROM Employee WHERE EmpFName = '".$search_first."' AND EmpLName = '".$search_last."'");
				$result = mysql_query($query);
				if(mysql_num_rows($result)==0) echo exit("<font color='red'>No Record Found</font>");
				echo"<table class='full'>";
				echo"<th></th><th>ID</th><th>Employee Name</th><th>Title</th><th>Team</th><th>Department</th></tr>";
				while ($row = mysql_fetch_array($result)) //Ohh! You can only fetch from a result RETURNED by mysql_query, not the actual query
				{ 
					//No need to setup individual variables...just use the $row array!
					echo"<tr><td class='view'><form action='employee.html' method='post'><div><input type='submit' value='View' class='button' /></div></td><td name='userID' class='id'>".$row["EmployeeID"]."</td></form><td name='userName' class='record'>".$row["EmployeeFName"];." ".$row["EmployeeLName"]."  </td><td name='userTitle' class='record'>".$row["EmployeeTitle"]."</td><td name='userTeam' class='record'>".$row["EmployeeTeam"]."</td><td name='userDept' class='record'>".$row["EmployeeDepart"]."</td></tr>";
				}
			}      
        	break;
		//And on, and on, etc.
	}
}

function mysqlConnectAndSelect()
{
	checkLogin();
	sqlConnect();
	mysql_select_db("titans", $con);	
}
?>

There are also lots more problems beyond this one. I'd be glad to fix them all for you! Just send me a PM.

Edited by FlashCreations: n/a

0

You can check for a search by the employee's name. Then you can check for a space. If there is a space you can split the two names with explode and then perform a query that matches first and last name. If there isn't any spaces only perform a query for the first or last name (Depending on what you want).

<?php
if(isset($_POST['searchType'])) //Check first, get values later
{
	$method=$_POST['searchType'];
	$search=$_POST['query'];
	
	switch ($method)
	{
    	case 'EmployeeFName': //Switch is not like if...you don't need an expression, just a value will do
    		if(empty($_POST['query'])) exit("Please Fill in a name of an employee you want to search. Click <a href=search.php>here</a> to go back.</p>");
			else
			{
				echo"<i>Your search for {$search} had the following results:</i>";
				//Here's the magical splitting of the names
				$arr = explode(" ", $search);
				$search_first = $arr[0];
				$search_last = $arr[1];
				
				$query = mysql_query("SELECT * FROM Employee WHERE EmpFName = '".$search_first."' AND EmpLName = '".$search_last."'");
				$result = mysql_query($query);
				if(mysql_num_rows($result)==0) echo exit("<font color='red'>No Record Found</font>");
				echo"<table class='full'>";
				echo"<th></th><th>ID</th><th>Employee Name</th><th>Title</th><th>Team</th><th>Department</th></tr>";
				while ($row = mysql_fetch_array($result)) //Ohh! You can only fetch from a result RETURNED by mysql_query, not the actual query
				{ 
					//No need to setup individual variables...just use the $row array!
					echo"<tr><td class='view'><form action='employee.html' method='post'><div><input type='submit' value='View' class='button' /></div></td><td name='userID' class='id'>".$row["EmployeeID"]."</td></form><td name='userName' class='record'>".$row["EmployeeFName"];." ".$row["EmployeeLName"]."  </td><td name='userTitle' class='record'>".$row["EmployeeTitle"]."</td><td name='userTeam' class='record'>".$row["EmployeeTeam"]."</td><td name='userDept' class='record'>".$row["EmployeeDepart"]."</td></tr>";
				}
			}      
        	break;
		//And on, and on, etc.
	}
}

function mysqlConnectAndSelect()
{
	checkLogin();
	sqlConnect();
	mysql_select_db("titans", $con);	
}
?>

There are also lots more problems beyond this one. I'd be glad to fix them all for you! Just send me a PM.

thanks for the help; however, I found another way to do it, It should work fine for what I need it to do.

$full = $_POST['query'];
$n = sscanf($full, "%s %s", $first, $last);
if($last != NULL)
{
$Emps= sqlQuery("SELECT * FROM Employee WHERE EmployeeFName like '$first' AND EmployeeLName like '$last'");
code-------
}
else 
{
$Emps= sqlQuery("SELECT * FROM Employee WHERE EmployeeFName like '$first'");
code-----
}
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.