On Mon, Sep 22, 2008 at 4:07 PM, Dan Shirah <mrsquash2@xxxxxxxxx> wrote:
> Hello all,
>
> I'm looking for suggestions on how to create a search function within my
> application. First I'll give you an overview.
>
> At the top of my page I have a form that contains name and date fields.
> When a user puts in some data and selects "Search" a simple column of
> results will be displayed on the left side of the screen.
>
> Example:
>
> User searchs for a date of "09/22/08" and a name of "Customers." In the
> left
> column a list of all customers for that day will be generated. There is A
> LOT of detail associated with these customer results therefore all data
> such
> as name, address, zip code, order number etc is not pulled from the
> database
> upon initial search. Instead, if a user clicks a customer name a seperate
> query will run and retrieve all of the pertinent data and provide a
> bulleted
> list under the customer name.
>
> What my vendor wants me to do is provide a way for someone to search
> through
> all the customers including the details that I do not initially display to
> allow them to find specific data. Such as finiding a record with a
> specific
> zip code. They then want to be able to click next to go to each
> consecutive
> record with that zip code.
>
> Any ideas on how to do this since all that data is not initially pulled in?
>
Its pretty straight forward, you create a query that extracts the name and
id of the records with a relevant where clause created on the fly. When
outputing the data, each record gets created as a link that then loads
another page/div with the total dataset for that record. The question for
you is how you want the interface for the search to work. You can provide a
dropdown with some choices (date, zip, etc) and a text input / date input
picker to allow the user to enter the data. This then gets submitted and
runs the query
<?php
//ignoring all the little details like data validation which you need to put
in
$query_type = $_POST['query_type']; // zip, date, (text value of the
dropdown)
$param = $_POST['param'];
$sql = "select cus_id, cus_name from customers where 1 ";
if(!empty($query_type)){
//use this to point to the correct field in the table
switch strtolower($query_type){
case "date":
$field = " join_date ";
$param = (strtotime($param) !== false ) ? "'" . date("Y-m-d",
strtotime($param)) . "' " : date("Y-m-d") ; //ternary check on date if
strtotime doesn't fail
break;
case "zip":
$field = " zip ";
$param = (int)$param; //force to int for US 5 digit zip, could also
use regex if you know what country you are operating in
break;
//add more case statements based on the element you want to allow
}
if (!empty($param)){
$sql .= " and $field = $param ";
}
//skipping the mysql connection stuff
$result = mysql_query($sql);
if ( mysql_errno != 0 )
{
//query went wrong so handle it
die("Bad query!");
}
if (mysql_num_rows($result) > 0 )
{
while ($rows = mysql_fetch_array($result)){
echo "<a
href='getDetail.php?id={$rows['cus_id']}'>{$rows['cus_name']}</a><br/>";
}
}else{
echo "No results";
}
?>
or something like that
--
Bastien
Cat, the other other white meat
