PDA

View Full Version : Create a Sort and Search within MySQL results


toad78
01-09-2010, 02:22 PM
I have a Table with queried results and would first like to know how to create a Sort with the following fields:

empl_id
firstname
lastname
company
position

How do I go about creating the ability for the user to sort them by the fields? For example, if they click 'firstname', it will sort all records by first name and so forth.

#2: How do I create a custom search box that can search by a term and only search within the records of that same table and not the whole website?

Thank you for your help!

tux
01-09-2010, 03:15 PM
In your query you can sort it using WHERE.

eg.

SELECT * FROM yourtable WHERE firstname = '$firstname'

In this example you would declare a variable from the form the user clicked when they selected the firstname.

$firstname = $_POST['firstname'];

This will return the contents of the table where the firstname equals what the user selected on the previous page.

With regards to the search option you can do this...

SELECT * FROM yourtable WHERE firstname LIKE '$search'

In this example you would declare a variable from the form the user clicked when they submitted the search.

$search = $_POST['search'];

This will return the contents of the table where the firstname equals what the user entered in the search box on the previous page.

You would need to build in extra code to allow for things like capitals and plurals etc. but this should help you get started.

toad78
01-09-2010, 07:31 PM
You mean you wouldn't use $_GET? You have to use $_POST?

tux
01-09-2010, 07:43 PM
When sending info from a form I use POST and when getting info from a URL I use GET.

I assumed you had a form field or select drop down where the user selected firstname etc. If you are using a link then use GET.

The search box will be a form field so use POST.

toad78
01-09-2010, 08:44 PM
You'll have to forgive me, but I'm getting an error and can't figure out what's going on:

Notice: Undefined variable: search in C:\...\list.php on line 111

Line 111: $query_getFN = "SELECT * FROM employee_info WHERE firstname LIKE '$search'";

in the following:
mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
$query_getFN = "SELECT * FROM employee_info WHERE firstname LIKE '$search'";
$getFN = mysql_query($query_getFN, $JEMmanagmnt) or die(mysql_error());
$row_getFN = mysql_fetch_assoc($getFN);
$totalRows_getFN = mysql_num_rows($getFN);

$search = $_POST['search'];HTML:
<form id="form1" name="sort" method="get" action="">
<p style="text-align: right;"><label for="textfield"><strong>Search: </strong></label>
<input name="search" type="text" id="search" size="20" maxlength="100" />
<input name="search" type="submit" id="search" value="Go" />
</p>.....
</form>

toad78
01-09-2010, 08:48 PM
When sending info from a form I use POST and when getting info from a URL I use GET.

I assumed you had a form field or select drop down where the user selected firstname etc. If you are using a link then use GET.

Actually, I was thinking of this:

if($_GET['sort'] == "firstname")
{
$query_getEmployee .= "ORDER BY firstname ASC";
}
else if($_GET['sort'] == "lastname")
{
$query_getEmployee .= "ORDER BY lastname ASC";
}

mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
$query_getEmployee = "SELECT empl_id, firstname, lastname, company, `position`, location, phone, email FROM employee_info";
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);

<th style="color: #2e2203; padding-left: 10px;" scope="col"><a href="list.php?sort=firstname">Name</a></th>
But am getting the errors:
Notice: Undefined index: sort in C:\...\list.php on line 118

Notice: Undefined index: sort in C:\...\list.php on line 122

Can you help me with this one?

tux
01-09-2010, 09:13 PM
I think you are getting the error because you have defined the variable $search after the query.

Try...

$search = $_POST['search'];

mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
$query_getFN = "SELECT * FROM employee_info WHERE firstname LIKE '$search'";
$getFN = mysql_query($query_getFN, $JEMmanagmnt) or die(mysql_error());
$row_getFN = mysql_fetch_assoc($getFN);
$totalRows_getFN = mysql_num_rows($getFN);

toad78
01-09-2010, 09:30 PM
Same error message even after I moved it above the query.

tux
01-09-2010, 09:36 PM
echo out $search

echo $search;

to see if it contains the value from the previous page ( POST )

tux
01-10-2010, 09:13 AM
Here is a simple script I would use to search a DB field. I have put in stuff I think is relevant to you. You will have to edit it a bit with your details. Note that I use an external script to connect to my DB and then use a require_once to call it on the page. You could remove it and add your connection if you wish but having it externally is neater in my opinion.

<?php
require_once("path to your connection script here"); // database connection. I always write my connection script and save it
// seperately and then use require_once on each page it is required on.

$search = $_POST['search'];//name entered in form on previous page

$query = "SELECT * FROM yourtable WHERE firstname LIKE '$search' ";// edit yourtable name here. I'm assuming your are searching the firstname field.
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);//select search results

do{//this should echo out all the results from the table with the firstname that is searched for
echo $row['empl_id'];
echo $row['firstname'];
echo $row['lastname'];
echo $row['company'];
echo $row['position'];
?>
<br>
<?php
}while($row = @mysql_fetch_array($result));
?>

Let me know if this helps.

toad78
01-12-2010, 03:35 PM
Sorry, but I'm not getting the results I need. Just the error:
Notice: Undefined index: search in C:\...\list.php on line 104

Line 104:
$search = $_POST['search'];

I guess it would help if someone actually had a working document that I can work with.

tux
01-12-2010, 09:33 PM
The code I gave you in my last post worked for me. It must be something you are doing wrong.

Post your code for the whole page and also the code for the form that sends the search word from the previous page.

toad78
01-12-2010, 10:31 PM
Hahaha! No doubt that it DOES work for you! You're speaking to an amatuer here!

Here you go:
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_JEMmanagmnt, $JEMmanagmnt);
$query_getEmployee = "SELECT * FROM employee_info";
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);
?>
<?php
$search = $_POST['search'];
$query_getEmployee = "SELECT * FROM employee_info WHERE firstname LIKE '$search' ";
$getEmployee = mysql_query($query_getEmployee, $JEMmanagmnt) or die(mysql_error());
$row_getEmployee = mysql_fetch_assoc($getEmployee);
$totalRows_getEmployee = mysql_num_rows($getEmployee);
?>
<div id="mainbody">
<?php if ($totalRows_getEmployee == 0) { // Show if recordset empty ?>
<p class="warning">Their currently are no Employees to list</p>
<?php } // Show if recordset empty ?>
<?php if ($totalRows_getEmployee > 0) { // Show if recordset not empty ?>
<form id="form1" name="form1" method="post" action="">
Search:
<input name="search" type="text" id="search" size="30" maxlength="50" />
</form>
<table width="98%" border="0" cellspacing="0" cellpadding="0">
<tr style="background: #fef3e6;">
<th style="color: #2e2203; padding-left: 10px;" scope="col"><a href="list.php?sort=<?php echo $row_getEmployee['firstname']; ?>">First Name</a> </th>
<th style="color: #2e2203; padding-left: 10px;" scope="col"><a href="list.php?sort=<?php echo $row_getEmployee['lastname']; ?>">Last Name</a> </th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Company</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Position</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Email</th>
<th style="color: #2e2203; padding-left: 10px;" scope="col">Phone</th>
<th scope="col">&nbsp;</th>
<th scope="col">&nbsp;</th>
</tr>
<?php $counter = 0; ?>
<?php do { ?>
<tr <?php if ($counter++ %2) {echo 'class="hilite"';} ?>>
<td style="padding-left: 10px;" scope="col"><span style="text-transform: uppercase;"><a href="employee.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>"><?php echo $row_getEmployee['firstname']; ?></a></span></td>
<td style="padding-left: 10px;" scope="col"><span style="text-transform: uppercase;"><a href="employee.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>"><?php echo $row_getEmployee['lastname']; ?></a></span></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['company']; ?><br />
<?php echo $row_getEmployee['position']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['email']; ?></td>
<td style="padding-left: 10px;" scope="col"><?php echo $row_getEmployee['phone']; ?></td>
<td scope="col"><a href="update.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>">EDIT</a></td>
<td scope="col"><a href="delete.php?empl_id=<?php echo $row_getEmployee['empl_id']; ?>">DELETE</a></td>
</tr>
<?php } while ($row_getEmployee = mysql_fetch_assoc($getEmployee)); ?>
</table>
<?php } // Show if recordset not empty ?>

Good luck!

tux
01-13-2010, 05:51 AM
Your form doesn't have an action!

Is this php and html on the same page?

Not had time to look at it fully as off to work but if it is try making the action on the form go to itself like this......

<form id="form1" name="form1" method="post" action="<?php echo $PHP_SELF;?>">

If not the action should be the destination page.

PS: I'm an amateur too. lol

toad78
01-13-2010, 01:44 PM
Applied that, but I still get the same error message even after I choose a radio button choice.

After the form is filled out, it should forward to a Thank you page and then send the formmail.

tux
01-13-2010, 04:55 PM
Copy this code into a new page and edit it to include your DB connection and the table you are searching.

Run the page and see if it works.

<?php
require_once("path to your connection script here"); // database connection. I always write my connection script and save it
// seperately and then use require_once on each page it is required on.

$search = $_POST['search'];//name entered in form on previous page

if($search){
$query = "SELECT * FROM yourtable WHERE firstname LIKE '$search' ";// edit yourtable name here. I'm assuming your are searching the firstname field.
$result = @mysql_query($query);
$row = @mysql_fetch_array($result);//select search results

do{//this should echo out all the results from the table with the firstname that is searched for
echo $row['empl_id'];
echo $row['firstname'];
echo $row['lastname'];
echo $row['company'];
echo $row['position'];
?>
<br>
<?php
}while($row = @mysql_fetch_array($result));
}
?>




<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form id="form1" name="form1" method="post" action="<?php echo $PHP_SELF;?>">
<p>Search:
<input name="search" type="text" id="search" size="30" maxlength="50" />
</p>
<p>
<input type="submit" name="Submit" value="Submit">
</p>
</form>
</body>
</html>