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
follows:
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 :),
Carol
Current Script:
[code]
Directory Search Results table{ border: 1px solid black; border-collapse: collapse; }th{
border: 1px solid black;
padding: 6px;
font-weight: bold;
background: #ccc;
text-align: center;
}
td {
border: 1px solid black;
padding:6px;
vertical-align: top;
text-align: center;
}
[/code][php]<?php
//connect to DB
$serverName = “XXXXXX”;
$usr=“xxxxxx”;
$pwd=“xxxxxx”;
$db=“Intranet”;
$connectionInfo = array(“UID” => $usr, “PWD” => $pwd, “Database” => $db);
$dbhandle = sqlsrv_connect($serverName, $connectionInfo);
$display_block=“
Search Results::
”;// DB SEARCH LOGIC ::: START
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
[‘first_name’]."%’";
}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;
//.”–”.$row[‘Last_Name’]."–".$row[‘Location’];
echo "
| First Name | Last
Name |
Location | Phone Number | ".$row[‘Location’]." |