Looking for help - getting information from mssql db using php

If you don’t mind, I need a lot of help with a php script to query an existing mssql database and get results based upon a user’s input. I basically have zero programming experience. I have enlisted the help of a programmer here, but he is not familiar with php. Currently, he is sidetracked on another project, and I am desperate to get this finished, as I cannot launch the new site until I get this working.

Background: I created a new html intranet site where I work. On the home page, I have a Phone Directory search form containing 2 text fields (first name and last name) and a drop-down field for the location.
The input names for first and last names match the fields in the database: “last_name” and “first_name”
The drop-down field locations entered in my form exactly match the locations in the database.

When the user selects Search, the form action is \php\Directory.php and the Method is Default.
The end result should return at minimum the First Name, Last Name, and Location of all matches in the database. For example, entering Joe for the first name should return all users in the database with the first name of Joe. Entering Jo should return Jo, Joseph, Joe, etc… Ditto the last name. For location, selecting one from the drop-down menu should return all users in that location. A combination of fields should be able to be used to narrow down a particular person. Example, Joe in Location xyz should return only Joes that are in xyz location, not all Joes in the company. I also want the phone number and extension returned, but I thought I’d at least get this part working before continuing.

We have been able to connect to the database and return some data, but it doesn’t always return all of the
data, and sometimes doesn’t return anything. Example: Searching for Joe may return a couple of Joes but
not all of them. Searching for a particular Joe’s last name returns nothing. Searching for my name returns nothing - neither using first nor last name - and I verified I’m in the database. So basically some last name searches work and some don’t. As for searching using the Location, we haven’t even tackled that one yet.

The script we have so far is below. The fields in the database that contain data we want returned are as

LAST_NAME - self explanatory
FIRST_NAME - self explanatory
LOCATION - self explanatory
AREA_CODE - self explanatory
PREFIX - First 3 digits of phone number
MAIN_NO - last 4 digits of phone number. Also used when returning the extension by adding a 1 in front of it.

Thanks in advance :),

Current Script:


Directory Search Results table{ border: 1px solid black; border-collapse: collapse; }

border: 1px solid black;
padding: 6px;
font-weight: bold;
background: #ccc;
text-align: center;

td {

border: 1px solid black;
vertical-align: top;
text-align: center;



//connect to DB
$serverName = “XXXXXX”;

$connectionInfo = array(“UID” => $usr, “PWD” => $pwd, “Database” => $db);

$dbhandle = sqlsrv_connect($serverName, $connectionInfo);

Search Results::


if( $dbhandle ) {

//Form the query based on the search criteria

if($_GET[‘first_name’] != “” & $_GET[‘last_name’] != “”){
$get_phone_directory = “SELECT First_Name, Last_Name, Location FROM PHCI_SHARE_PHONE_LIST where Last_Name like '%”.$_GET[‘last_name’]."%’ and First_Name like '%".$_GET


}else if($_GET['first_name'] == "" & $_GET['last_name'] != ""){

		$get_phone_directory = "SELECT First_Name, Last_Name, Location FROM 

PHCI_SHARE_PHONE_LIST where last_name like ‘%".$_GET[‘last_name’]."%’";

}else if($_GET['first_name'] != "" & $_GET['last_name'] == ""){

		$get_phone_directory = "SELECT First_Name, Last_Name, Location FROM 

PHCI_SHARE_PHONE_LIST where first_name like ‘%".$_GET[‘first_name’]."%’";

	     $stmt = sqlsrv_query( $dbhandle, $get_phone_directory);

if( $stmt === false ) {

			die( print_r( sqlsrv_errors(), true));

		if( sqlsrv_fetch( $stmt ) === false) {
			die( print_r( sqlsrv_errors(), true));

$row_count = sqlsrv_has_rows($stmt);

		echo "check this out -- ".$row_count;

//$row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC);

//$next_result = sqlsrv_next_result($stmt);

//$FirstName = .$row[‘first_name’];
//echo “DATA IS - “.$FirstName;

echo "

echo "";
		while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
			echo "<tr><td>".$row['First_Name']."</td><td> ".$row



		echo " </tr>";
		echo " </table>";

	} else {

	     echo "Encountered a problem accessing Phone Directory.\n";
	     die( print_r( sqlsrv_errors(), true));

	/* Close the connection. */
	sqlsrv_close( $dbhandle);


First Name Last


Location Phone Number

Couple of things that i see. I’ve never used mssql, but assuming its basically the same as mysql, you really should add some sql injection security in there, specially when dealing with end users typing in stuff. It only takes one person to royally screw up the database.

Also, change the method in the form to post instead of get. inbetween the first two if statements, just add

[php]if(!empty($_POST[‘first_name’]) {
$fname = add_slashes(ucwords($_POST[‘first_name’]));
} else {
$f_ok = 0;
if(!empty($_POST[‘last_name’]) {
$lname = add_slashes(ucwords($_POST[‘last_name’]));
} else {
$l_ok = 0;

After that,
if(!$f_ok && !$l_ok) {
$get_phone_directory = “SELECT First_Name, Last_Name, Location FROM PHCI_SHARE_PHONE_LIST WHERE Last_Name like '%”.$lname."%’ AND First_Name like ‘%".$fname."%’";
if(!$l_ok && $f_ok == 0) {
$get_phone_directory = “SELECT First_Name, Last_Name, Location FROM PHCI_SHARE_PHONE_LIST WHERE Last_Name like '%”.$lname."%’";
if(!f_ok && $l_ok == 0) {
$get_phone_directory = “SELECT First_Name, Last_Name, Location FROM PHCI_SHARE_PHONE_LIST WHERE First_Name like '%”.$fname."%’";

When i was typing those if statements out, i noticed that your column names weren’t consistent, which would explain why you were sometimes getting data and sometimes not. In the last two statements, your name columns were in all lowercase, but in the first one, they were capitalized.

1 more things before i hit Post, databases tend to be case sensetive, no real way around it. So make sure that if pre-format any search parameters. If its Joe in the database, it may not find it if you type in joe (it should using wildcards, but not always as you can see). If all the names are like that, then use ucwords(). I already added it in my example, but if its not needed, just remove it.

Sponsor our Newsletter | Privacy Policy | Terms of Service