I'm working on this project where they can search a list of names and they can put in a death year that automatically searches for + and - 5 years.

mysql_connect ("localhost", "root","root") or die (mysql_error());
    mysql_select_db ("FHSNL");
    $surname = $_POST['surname'];
    $given = $_POST['given'];
	$maiden = $_POST['maiden'];
	$deathbefore = $_POST['death'] - 5;
	$deathafter = $_POST['death'] + 5;
	$age = $_POST['age'];
	$birth = $_POST['birth'];
	$birthplace = $_POST['birthplace'];
	$deathplace = $_POST['deathplace'];
	$erected = $_POST['erected'];
	$region = $_POST['region'];
    if(isset($_POST['submit'])){
		 $sql = mysql_query("SELECT * FROM `HEADSTONES` WHERE `SURNAME` LIKE '%$surname%' AND `GIVEN` LIKE '%$given%' AND `MAIDEN` LIKE '%$maiden%' AND `AGE` LIKE '%$age%' AND `D_OF_BIRTH` LIKE '%$birth%' AND `PLACE_OF_DEATH` LIKE '%$deathplace%' AND `ERECTED_BY` LIKE '%$erected%' AND `PLACE_OF_BIRTH` LIKE '%$birthplace%' AND `Expr3` LIKE '%$region%' AND `YEAR_OF_DEATH` BETWEEN '$deathbefore' AND '$deathafter'");

I know the problem is that deathbefore and deathafter automatically go to -5 or +5 if no one enters a search term no results show but how do i fix this so all the results show if no search term is entered in that input box? Its probably a simple solution but my brain is fried right now.
Thanks for the help.

I'm working on this project where they can search a list of names and they can put in a death year that automatically searches for + and - 5 years.

mysql_connect ("localhost", "root","root") or die (mysql_error());
    mysql_select_db ("FHSNL");
    $surname = $_POST['surname'];
    $given = $_POST['given'];
	$maiden = $_POST['maiden'];
	$deathbefore = $_POST['death'] - 5;
	$deathafter = $_POST['death'] + 5;
	$age = $_POST['age'];
	$birth = $_POST['birth'];
	$birthplace = $_POST['birthplace'];
	$deathplace = $_POST['deathplace'];
	$erected = $_POST['erected'];
	$region = $_POST['region'];
    if(isset($_POST['submit'])){
		 $sql = mysql_query("SELECT * FROM `HEADSTONES` WHERE `SURNAME` LIKE '%$surname%' AND `GIVEN` LIKE '%$given%' AND `MAIDEN` LIKE '%$maiden%' AND `AGE` LIKE '%$age%' AND `D_OF_BIRTH` LIKE '%$birth%' AND `PLACE_OF_DEATH` LIKE '%$deathplace%' AND `ERECTED_BY` LIKE '%$erected%' AND `PLACE_OF_BIRTH` LIKE '%$birthplace%' AND `Expr3` LIKE '%$region%' AND `YEAR_OF_DEATH` BETWEEN '$deathbefore' AND '$deathafter'");

I know the problem is that deathbefore and deathafter automatically go to -5 or +5 if no one enters a search term no results show but how do i fix this so all the results show if no search term is entered in that input box? Its probably a simple solution but my brain is fried right now.
Thanks for the help.

Put schema here so that it is easy to answer!

Member Avatar
diafol

validate and clean all inputs - vars only set if data valid and create small sql pieces:

if($surname)$piece[] = "`SURNAME` LIKE '%$surname%'";
if($given)$piece[] = "`GIVEN` LIKE '%$given%'";
(etc)

if(isset($piece)){
  $sqlend = implode(" AND ",$piece);
  $clause = "SELECT * FROM `HEADSTONES` WHERE $sqlend";
}else{
  echo "You must enter valid search terms";
}

Something like that? BEWARE - not tested.

Sorry if i didn't explain enough. The user enters info, not all fields are required, into any field they want and it searches the database. If they enter that year it automatically searches all years within a +- 5 range.

<body>
<?php
    mysql_connect ("localhost", "root","root") or die (mysql_error());
    mysql_select_db ("FHSNL");
    $surname = $_POST['surname'];
    $given = $_POST['given'];
	$maiden = $_POST['maiden'];
	if(isset($_POST['death']){
	$deathbefore = $_POST['death'] - 5;
	$deathafter = $_POST['death'] + 5;}
	$age = $_POST['age'];
	$birth = $_POST['birth'];
	$birthplace = $_POST['birthplace'];
	$deathplace = $_POST['deathplace'];
	$erected = $_POST['erected'];
	$region = $_POST['region'];
    if(isset($_POST['submit'])){
		 $sql = mysql_query("SELECT * FROM `HEADSTONES` WHERE `SURNAME` LIKE '%$surname%' AND `GIVEN` LIKE '%$given%' AND `MAIDEN` LIKE '%$maiden%' AND `AGE` LIKE '%$age%' AND `D_OF_BIRTH` LIKE '%$birth%' AND `PLACE_OF_DEATH` LIKE '%$deathplace%' AND `ERECTED_BY` LIKE '%$erected%' AND `PLACE_OF_BIRTH` LIKE '%$birthplace%' AND `Expr3` LIKE '%$region%'");
    
    $rows = mysql_num_rows($sql);
	echo "<table><tr><th>ID</th><th>Keys</th><th>Plot Number</th><th>Region</th><th>Town</th><th>Religon</th><th>Surname</th><th>Given</th><th>Maiden</th><th>Date of Death</th><th>Age</th><th>Date of Birth</th><th>Place of Birth</th><th>Place of Death</th><th>Erected by</th><th>Additional Information</th>";
    for($j = 0; $j < $rows; ++$j)
	{
		$row = mysql_fetch_row($sql);
		echo "<tr>";
		for($k = 0; $k < 15; ++$k) echo "<td>$row[$k]</td>";
		echo "</tr>";
	}
	echo "</table>";
    if(!$rows)
    echo "No results where found";
	echo "$surname  $given";
    }
    ?>
Member Avatar
diafol

No, got you first time. Didn't work for you?