0

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.

3
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by diafol
0

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!

0

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.

0

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";
    }
    ?>
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.