Member Avatar for knitex

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
  • 113 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by diafol
Member Avatar for Stefano Mtangoo

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 for 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.

Member Avatar for knitex

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 for diafol

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.