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
  • 124 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Member #120589

Dani AI

Generated

The immediate cause is trying to compute a numeric range from an empty form value. If $_POST['death'] is empty you end up using -5 and +5; that BETWEEN then filters out everything. Also note a probable parse error in your snippet (if(isset($_POST['death']{) — that missing parenthesis will stop the script. Validate the death input first and only add a BETWEEN clause when you actually have a numeric year.

was on the right track with building small SQL pieces. A safe, practical pattern is to collect WHERE parts and parameters, then prepare and execute once. This avoids the unconditional BETWEEN and also closes the SQL injection hole that raw concatenation creates. Example (uses PDO and input validation):

$conds = [];
$params = [];

if (!empty($_POST['surname'])) {
  $conds[] = 'SURNAME LIKE :surname';
  $params[':surname'] = '%' . $_POST['surname'] . '%';
}

if (!empty($_POST['death']) && filter_var($_POST['death'], FILTER_VALIDATE_INT) !== false) {
  $y = (int)$_POST['death'];
  $conds[] = 'YEAR_OF_DEATH BETWEEN :y1 AND :y2';
  $params[':y1'] = $y - 5;
  $params[':y2'] = $y + 5;
}

$sql = 'SELECT * FROM HEADSTONES' . ($conds ? ' WHERE ' . implode(' AND ', $conds) : '');
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

Extra notes: confirm YEAR_OF_DEATH is stored as an integer (STRING types or NULLs can make BETWEEN behave oddly). Prefer checking request method ($_SERVER['REQUEST_METHOD'] === 'POST') instead of relying on a submit button name. Avoid deprecated mysql_* functions — use PDO or mysqli with prepared statements. For debugging, log the final SQL and bound parameters (never show them to end users). For reference see the PDO prepared statements docs and filter_var on php.net: PDO prepared statements and filter_var.

Recommended Answers

All 4 Replies

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 Member #120589

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 Member #120589

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.