Hi everyone,
I was wondering if anyone would be able to help me out?
I need to display all active records from a database and allow the user to search by FirstName, LastName and display inactive records as well.
I am currently playing with a database which is not the final project. I found a script which is helpful, but I’m stuck in creating a form with <input fields and <option select fields that passes the variable to a query. I need to remove the for loop which creates the letters of the alphabet and somehow allow for variables to be passed from a form.
I am not a programmer and do not take any credit for the code posted here. Any help is much appreciated. Thanks,
<?
$con = mysql_connect("localhost","root","rootpassword");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test1", $con);
/* Get the letter user clicked on and assign it a variable called $sort */
$sort = $_REQUEST['letter'];
/* Let's check if variable $sort is empty. If it is we will create a query to display all customers alphabetically ordered by last name. */
if($sort == ""){
$qry= "SELECT * FROM names ORDER BY LastName ASC " ;
}else{
/* if varible $sort is not empty we will create a query that sorts out the customers by their last name, and order the selected records ascendingly. */
$qry = "SELECT * FROM names WHERE LastName LIKE '$sort%' ORDER BY LastName ASC" ;
}
/* Notice the use of '%' wilde card in the above query "LIKE '$sort%'". */
//next step is to execute the query.
$execute = mysql_query($qry) or die(mysql_error());
/* Before we display results let's create our alphabetical navigation. The easiest way to create the navigation is to use character codes and run them through the "for" loop. */
echo "
" ;
for ($i = 65; $i < 91; $i++) {
printf('<a href="%s?letter=%s">%s</a> | ',
$PHP_SELF, chr($i), chr($i));
}
echo "
" ;
/* now we are ready to display the results. Since out tbl_customers table has only three fileds we will display the results in a paragraphs. In the real world you may need to display the results in a table.
To display the results we will use "do while" loop to fetch the results. If no customers are found we will display an error message. */
echo " <table border='1'>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Active</th>
</tr> ";
if(mysql_num_rows($execute)>0){
do{
echo "
<tr>
<td>" .$result['id']. "</td>
<td>" .$result['FirstName']. "</td>
<td>" .$result['LastName']. "</td>
<td>" .$result['active']. "</td>
</tr>
" ;
}while($result = mysql_fetch_assoc($execute));
}else{
echo "
<tr>
<td colspan='4'>No customer found</td>
</tr>
</table>
" ;
}
?>