0

I'm just wondering how to solve this problem. I've tried it myself with the code below but it doesn't seem to work. I made it so the database results come up from the database HEADSTONESA and it has a section called KEYS which is the cemetery key. I was wondering if you can make it so when the result comes up it automatically searches the CEMETERIES database and replaces the KEYS with the name of the cemetery.

<?php
    mysql_connect ("localhost", "root","root") or die (mysql_error());
    mysql_select_db ("FHSNL");
	//sets all values in form to php variables
    $surname = $_POST['surname'];
    $given = $_POST['given'];
	$maiden = $_POST['maiden'];
	$deathdate = $_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'];
	$religion = $_POST['religion'];
	//divides into smaller sql pieces for filled in form input.
	if($surname)$piece[] = "`SURNAME` LIKE '%$surname%'";
	if($given)$piece[] = "`GIVEN` LIKE '%$given%'";
	if($maiden)$piece[] = "`MAIDEN` LIKE '%$maiden%'";
	if($age)$piece[] = "`AGE` LIKE '%$age%'";
	if($deathplace)$piece[] = "`PLACE_OF_DEATH` LIKE '%$deathplace%'";
	if($region)$piece[] = "`TOWN` LIKE '%$region%'";
	if($religion)$piece[] = "`RELIGION` LIKE '%$religion%'";
	if($deathdate)$piece[] = "`YEAR_OF_DEATH` BETWEEN '$deathbefore' AND '$deathafter'";
	//if submit button is pressed it runs the search query.
    if(isset($_POST['submit'])){
		if(isset($piece)){
  $sqlend = implode(" AND ",$piece);
  $sql = mysql_query("SELECT * FROM `HEADSTONESA` WHERE $sqlend");
  $rows = mysql_num_rows($sql);
  //constructs the table.
	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>";
	while($row = mysql_fetch_array($sql))
	{
		echo "<tr>";
    echo "<td>" .$row['ID'] . "</td>";
    echo "<td>".$row['GIVEN']."</td>";
    echo "<td>".$row['SURNAME']."</td>";
    echo "<td>".$row['D_OF_DEATH']."</td>";
    echo "<td>" .$row['AGE']. "</td>";
	echo "<td>" .$row['D_OF_BIRTH']."</td>";
	echo "<td>" .$row['PLACE_OF_BIRTH']."</td>";
	echo "<td>" .$row['PLACE_OF_DEATH']."</td>";
	echo "<td>" .$row['ERECTED_BY']."</td>";
	echo "<td>" .$row['ADDITIONAL_INFO']."</td>";
	$requested_id = $row['KEYS'];
	$sql2 = mysql_query("SELECT * FROM `CEMETERIES` WHERE `KEYS` LIKE '$requested_id'");
	$row2 = mysql_fetch_array($sql2);
	echo "<td>".$row2['NAME']."</td>";
	echo "</tr>";
		
	}
	echo "</table>";
	//if nothing found prints out the following.
    if(!$rows)
    echo "No results where found";
	
    }
}
//if no search terms entered prints the following.
else{
  echo "You must enter valid search terms";
}
    
    
    ?>

Thanks for the help

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

What is the problem ? What do you want to do with the above codes ? Your post is not clear. Be more precise and tell us what do you want to do.

0

What I want to happen is when someone searches the database it goes through the headstonesa table. The results show and under the results there's a section called keys which just has letters and numbers. For example SJSTJ08RC. Instead of these showing I want to take that key and automatically search the cemeteries table for that key and print the name of the cemetery instead. The cemeteries table has a colum called keys and a colum called name. The keys is the same is both tables

0

Then you join the two tables, below is the syntex. All in capital letters ae keywords;

SELECT tableA.cemeteriesid, tableB.cemeteriesname FROM tableA INNER JOIN tableB ON tableA.cemeteriesid = tableB.cemeteriesid WHERE tableA.cemeteriesid = 'SJSTJ08RC'

Note
tableA = One of the tables (say headstonesa)
tableB = the other table (containig cemeteries name

0

please be more attentive to your codes.
you didn't use any of the concatination in your if statement on
these following codes.

if($surname)$piece[] = "`SURNAME` LIKE '%$surname%'";
if($given)$piece[] = "`GIVEN` LIKE '%$given%'";
if($maiden)$piece[] = "`MAIDEN` LIKE '%$maiden%'";
if($age)$piece[] = "`AGE` LIKE '%$age%'";
if($deathplace)$piece[] = "`PLACE_OF_DEATH` LIKE '%$deathplace%'";
if($region)$piece[] = "`TOWN` LIKE '%$region%'";
if($religion)$piece[] = "`RELIGION` LIKE '%$religion%'";
if($deathdate)$piece[] = "`YEAR_OF_DEATH` BETWEEN '$deathbefore' AND '$deathafter'";

Instead of equal(=)sign, use this one (.=) so that it will concatinate to your query.
the next thing is that you should use the OR statement also in filtering.

your code already runs to my testing.php with some editing to be tested
well this code should help to your program.

"SELECT TableA.id, TableB.DeadPersonNAme FROM TableA INNER JOIN TableB ON TableA.id = tableB.id WHERE TableA.id = '$id'";

Edited by decade: i forgot something

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.