mySQL Join and Count

[jdadwilson]

I am working on a web site for a Texas County Genealogical Society. On the site I have a database that contains cemetery information table (cemetery_data) and person information data (person_data).

 

What I need is a query that will find all of the cemetery names (cem_name) that starts with a specific letter ‘A’, within a certain county (cem_county). That is the easy part

 

$querySQL = “SELECT * FROM cemetery_data WHERE cem_name LIKE ‘A%’ AND cem_county = ‘Smith’ ORDER BY cem_name;”

 

Now, within the same query I would like to get the count of the number of records from the person_data table that have a cemetery_data.cem_id = person_data.per_cemid

 

The results would then give:

 

AA Cemetery 234

AB Cemetery 432

AC Cemetery 764

 

Where the count would then be the number of interments in the cemetery.

 

Any help is greatly appreciated.

 

[jdadwilson]

I was able to get most of the results I want from the following query

 

SELECT  cemetery_data.*, COUNT(person_data.per_cemid) FROM cemetery_data, person_data WHERE cemetery_data.cem_name LIKE 'A%' AND cemetery_data.cem_county = 'Smith' AND person_data.per_cemid = cemetery_data.cem_id GROUP BY cemetery_data.cem_name;

 

The problem now is that any cemetery with zero interments are not listed. This will be the case until the database is totally populated with internments (20k down and 18k to go).

[kickstart]

Hi

 

You need a left outer join:-

 

SELECT  cemetery_data.*, COUNT(person_data.per_cemid) 
FROM cemetery_data
LEFT OUTER JOIN person_data 
ON cemetery_data.cem_id = person_data.per_cemid
WHERE cemetery_data.cem_name LIKE 'A%' 
AND cemetery_data.cem_county = 'Smith' 
GROUP BY cemetery_data.cem_name;

 

All the best

 

Keith

[jdadwilson]

Kickstart;

 

Thanks for your reply. The code worked great. Problem is that the page takes forever to load. The code is doing some heavy duty computing in that I loop for 26 letters of the alphabet to build a string that can be used to jump to a specific letter and then build the page with all of the cemeteries and internment counts.

 

You can see the page at:

 

www.wilson-moore/TXfannin/cemeteries.php

 

Thanks for your assistance.

[jdadwilson]

Sorry, I forgot the .com in the URL

 

http://www.wilson-moore.com/TXfannin/cemeteries.php

[kickstart]

Hi

 

Hope you are not doing 26 seperate SQL calls.

 

Think this rough idea would give you what you want fairly efficiently. Not tested so please excuse any typos.

 

<?php

$dbms = 'mysql';
$dbhost = 'localhost';
$dbname = 'somedb';
$dbuser = 'root';
$dbpasswd = '';	

// Make the database connection.
$conn = mysql_connect($dbhost,$dbuser,$dbpasswd);

mysql_select_db($dbname,$conn);

$CountyName = mysql_real_escape_string($_REQUEST['CountyName']);

$sql = "SELECT  cemetery_data.*, COUNT(person_data.per_cemid) AS internCount
		FROM cemetery_data
		LEFT OUTER JOIN person_data 
		ON cemetery_data.cem_id = person_data.per_cemid
		WHERE cemetery_data.cem_county = '$CountyName' 
		GROUP BY cemetery_data.cem_name
		ORDER BY cemetery_data.cem_name";

$FirstLetterArray = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

if ( ($result = mysql_query($sql,$conn)) )
{
	$PrevCemStart = "";
	while ($row = mysql_fetch_array($result))
	{
		if ($PrevCemStart != substr($row['cem_name'],0,1))
		{
			if ($PrevCemStart != '')
			{
				$OutStr .= '</table>';
			}
			$PrevCemStart = substr($row['cem_name'],0,1);
			$FirstLetterKey = array_search($PrevCemStart,$FirstLetterArray);
			if ($FirstLetterKey === false)
			{
				$FirstLetterArray[] = "<a href='#".$PrevCemStart."0'>$PrevCemStart</a>";
			}
			else
			{
				$FirstLetterArray[$FirstLetterKey] = "<a href='#".$PrevCemStart."0'>$PrevCemStart</a>";
			}
			$OutStr .= "<h2><a name='".$PrevCemStart."0' id='".$PrevCemStart."0'>- $PrevCemStart - </h2>";
			$OutStr .= '<table width="60%"><tr><td align="left" width="60%"><b>    View Cemetery Info</b></td><td align="right" width="40%"><b>View all Interments</b></td></tr>';
		}
		$OutStr .= '<tr valign="top"><td>    <a href="cemetery.php?cem_id='.$row['cem_id'].'">'.$row['cem_name'].'</a></td><td align="right"><a href="interments.php?cem_id='.$row['cem_id'].'">'.$row['internCount'].'</a></td></tr>';
	}
}
if ($PrevCemStart != "")
{
	$OutStr .= '</table>';
}

echo explode(' | ',$FirstLetterArray);
echo $OutStr;

?>

 

All the best

 

Keith

[jdadwilson]

Kickstart;

 

Thanks for the code. I tried it and it is good news bad news.

 

The good news is that it almost works. The | A | B | string just displays as "Array". Any ideas?

 

The bad news is that it takes about 30 seconds for the page to load and then in a flash is displayed.

 

And yes, I was doing 26 queries. I am old school having done my first programming in Fortran 2. The previous way seemed to load faster.

 

Thanks for your assistance. It is good to learn new techniques.

[jdadwilson]

Fixed the array problem. Need to use "implode" not "explode"

[akitchin]

the LEFT OUTER JOIN will cause each set of data from the person_data table to be appended to each individual cemetary. this is terribly inefficient, since you don't need that data, you simply need the count. a subquery would most likely be markedly faster.

 

admittedly i am not totally up to snuff on my subquery syntax, but give something like this a shot:

 

SELECT c.cem_name AS cemetary_name, (SELECT COUNT(*) FROM person_data AS p WHERE p.cem_id=c.cem_id) AS total_victims
  FROM cemetary_data AS c
  WHERE SUBSTR(c.cem_name,0,1) = 'A'
    AND c.cem_county = 'Smith'
  ORDER BY c.cem_name ASC

[kickstart]

Hi

 

The code will put out everything at once at the end.

 

There is nothing complicated in the SQL that would cause it to take a hideously long time. Suggests there might be an issue with the indexing of the tables. Is there an index on per_cemid on the person_data table?

 

Also it is generally a good idea to avoid using SELECT *. Useful shorthand but best avoided in some regularly run code.

 

Generally subselects are viewed as being less efficient than joins. Might be worth trying both solutions and doing explains on them.

 

All the best

 

Keith