Putting multiple tables into a search

[CountryGirl]

I thought I'd start a new topic on this . . .

 

Alright guys, I got a search up! That tutorial is awesome - pretty easy !! This has been such a brain-wracking project .

 

This is what I am messing with right now - http://www.wagonerassessor.com/testsearch.php. If you put in "730000001" and then check both boxes, you can pull up a record .

 

Now, this is what I need to be able to figure out next:

 

I have 16 tables that have data in them that I need people to be able to search. This search is being built for an Assessor's office, so the tables are thinga like, ownership (which is the table I have in the search right now), tax area, land, parcel numbers, commercial, agricultural, address, legal, sale information, etc. These tables all have their base account number and then the correct info is in each table.

 

How do I incorporate each table into the search so that someone can access all the information they need? I need people to be able to pull up Name, Address, Account #, Parcel #, Legal, School District (tax area), Description (that'll include Sq. ft, year house was built, outbuildings, etc).

 

Thanks for the help! You guys have been great .

 

Qadoshyah

[CountryGirl]

Does anyone have advice on this? I'd appreciate any help !

 

Qadoshyah

[keeB]

That might be the coolest name ever. Qadoshyah.

 

Your form is returning 2 results and isn't very user friendly. Functionality wise it's not very pleasing to use. Being able to search by some other keywords would be nice.

 

 

When you say you want to incorporate all of the data in to the search, are you taking about the results or the input parameters?

[CountryGirl]

That might be the coolest name ever. Qadoshyah.

 

Your form is returning 2 results and isn't very user friendly. Functionality wise it's not very pleasing to use. Being able to search by some other keywords would be nice.

 

 

When you say you want to incorporate all of the data in to the search, are you taking about the results or the input parameters?

 

Thanks !

 

Yeah, I know the form isn't very impressive right now. It'll definitely change . I need all the data to be able to show in the results. When someone searches for, let's say a specific account number, I want all the info for that account number to be able to show up in the results and be pulled from all the tables.

 

Qadoshyah

[keeB]

How familiar are you with SQL joins?

[CountryGirl]

How familiar are you with SQL joins?

 

Not very familiar . I've heard of them and know what they are supposed to do, but exactly how (or where) do I put them into the code?

 

Qadoshyah

[keeB]

Can you post a DDL of your database and the query you are currently using to generate results of your search?

[CountryGirl]

Can you post a DDL of your database and the query you are currently using to generate results of your search?

 

Sure, here's all the code I'm using right now ~

 

<?php

$dbHost = 'localhost';

$dbUser = 'wcassessor';

$dbPass = '********';

$dbDatabase = 'wcassessor';

$con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());

 

mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());

?>

 

<?php

// Set up our error check and result check array

$error = array();

$results = array();

 

// First check if a form was submitted.

// Since this is a search we will use $_GET

if (isset($_GET['search'])) {

  $searchTerms = trim($_GET['search']);

 

  if (strlen($searchTerms) < 3) {

      $error[] = "Search terms must be longer than 2 characters.";

  }else {

      $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection.

  }

 

  // If there are no errors, lets get the search going.

  if (count($error) < 1) {

      $searchSQL = "SELECT Account, OwnersName FROM asmnt_ownership WHERE ";

     

      // grab the search types.

      $types = array();

      $types[] = isset($_GET['Acct'])?"`Account` LIKE '%{$searchTermDB}%'":'';

      $types[] = isset($_GET['Name'])?"`OwnersName` LIKE '%{$searchTermDB}%'":'';

     

      $types = array_filter($types, "removeEmpty");

     

      if (count($types) < 1)

        $types[] = "`Account` LIKE '%{$searchTermDB}%'";

     

          $andOr = isset($_GET['matchall'])?'AND':'OR';

      $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `OwnersName`"; // order by title.

 

      $searchResult = mysql_query($searchSQL) or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}");

     

      if (mysql_num_rows($searchResult) < 1) {

        $error[] = "The search term provided {$searchTerms} yielded no results.";

      }else {

        $results = array(); // the result array

        $i = 1;

        while ($row = mysql_fetch_assoc($searchResult)) {

            $results[] = "{$i}: {$row['Account']}<br />{$row['OwnersName']}<br />";

            $i++;

        }

      }

  }

}

 

function removeEmpty($var) {

  return (!empty($var));

}

?>

 

<html>

  <title>Test Search</title>

  <style type="text/css">

      #error {

        color: red;

      }

  </style>

  <body>

      <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?>

      <form method="GET" action="<?php echo $_SERVER['wcassessor'];?>" name="searchForm">

        Search For: <input type="text" name="search"><br />

        Search In:<br />

        Account Number: <input type="checkbox" name="Account" value="on" <?php echo isset($_GET['Account'])?"checked":''; ?> /> |

        Name: <input type="checkbox" name="title" value="on" <?php echo isset($_GET['OwnersName'])?"checked":''; ?> /> |

Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br />

        <input type="submit" name="submit" value="Search!" />

      </form>

      <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?>

  </body>

</html>

 

Thanks so much!

 

Qadoshyah

[CountryGirl]

Alright, I just read through MySQL's Manual on Joins and I am slightly confused.

 

How do I know which Join would be best to use? An inner join, an outer join, a left join, etc?

 

Do I need to do something like this, "t1.name = t2.name",  for each table?

 

They all have a KeyID as their Primary Key and an ApprID that is the index for each table.

 

Any ideas as to what would be the best way to write JOINS for the 16 tables I need to join?

 

Thanks!

Qadoshyah

 

[zeodragonzord]

I don't know how your tables are laid out so I can't give you an answer on which join you should use.  However, you must understand the difference between the joins.

 

Each time you do a join, you specify which field to match, such as an ID field.  Say you have two tables, TableA and TableB.  Doing an INNER JOIN means that you want to find something using a common ID that matches both tables.  Which means if you're joining TableA.FieldA with TableB.FieldA, the only rows returned are a ones that have a common value in both fields.

 

Doing a LEFT JOIN means that you will return all rows from TableA, but if there are any rows in TableB that matches TableA using the common field, it will return that too.

 

Other than explaining how joins work, we cannot help you further unless you describe the structure of your tables.

 

Take a look at W3Schools. http://www.w3schools.com/sql/sql_join_left.asp

[CountryGirl]

I think I will try playing around with the LEFT JOIN and see if that'll work with my tables (I *think* it may).

 

The structure of my tables . . . I'm not totally sure what info you need on that?

 

The database type is InnoDB. Collation is latin1_swedish_ci.  Table names are:

 

appr_agland

appr_commbuilding

appr_commercial

appr_miscimpr

appr_modifiers

appr_outbuilding

appr_resident

appr_value

asmnt_accountexempt

asmnt_legal

asmnt_ownership

asmnt_parcel

asmnt_situs

asmnt_taxarea

cmn_name

sale_parcel

 

Qadoshyah

[CountryGirl]

I just edited this post, because I was able to make the search work. But, I am not pulling any data from the second table.

 

This is the code I have right now:

 

"SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName
             FROM asmnt_ownership
             LEFT JOIN asmnt_legal
             ON asmnt_ownership.KeyID=asmnt_legal.KeyID
             ORDER BY asmnt_ownership.Account, asmnt_legal.Legal";

 

I'm thinking a LEFT JOIN isn't going to work, unless I just have my stuff written wrong (?).

 

When I do a search with an account number, I get ALL the account numbers (which is a crazily high amount). The colum for Legal is there (since I added it into the code), but nothing is in it. So, it's not pulling anything from the database.

 

Thanks!

Qadoshyah

[CountryGirl]

Okay, well, I think a LEFT JOIN will work . I am able to pull the Legal information now once I added to the SELECT statement:

 

"SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal
             FROM asmnt_ownership
             LEFT JOIN asmnt_legal
             ON asmnt_ownership.KeyID=asmnt_legal.KeyID
             ORDER BY asmnt_ownership.Account, asmnt_legal.Legal";

 

Now, should I be able to do this with all tables? Continue to just LEFT JOIN all tables?

 

And to be able to just return one search result, can I still use the same code I was using before? Like:

 

WHERE Account = '{$search}' OR OwnersName = '{$search}'"

 

Thanks! I think I may be getting the hang of things .

 

Qadoshyah

 

[zeodragonzord]

Yea, you can add as many joins to other tables as you need to.

...
LEFT JOIN TableB ON TableA.columnA = TableB.columnA
LEFT JOIN TableC ON TableA.columnA = TableC.columnA
...

 

And yes, you can add a WHERE clause at the end.  If you do that, try to include the table name.  Without it, MySQL won't know which field you're referring to if the column name you're searching for is the same name across multiple tables; it's ambiguous.  It's good convention and prevents errors.

 

WHERE TableA.Account = '{$search}' OR TableB.OwnersName = '{$search}'"

[CountryGirl]

I'm having difficulty with the WHERE clause for some reason. I've tried putting it at a few different spots in the code and it won't work. Does this look right or not?

 

"SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal
             FROM asmnt_ownership
             LEFT JOIN asmnt_legal
             WHERE asmnt_ownership.Account '{$search}' OR asmnt_ownership.OwnersName = '{$search}'
             ON asmnt_ownership.KeyID=asmnt_legal.KeyID
             ORDER BY asmnt_ownership.Account, asmnt_legal.Legal";

 

Qadoshyah

[zeodragonzord]

The WHERE clause needs to go after all the JOINs.

 

"
SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal
FROM asmnt_ownership
LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID
WHERE asmnt_ownership.Account '{$search}' OR asmnt_ownership.OwnersName = '{$search}'
ORDER BY asmnt_ownership.Account, asmnt_legal.Legal";

 

So the order are as follows:

[*]SELECTs

[*]FROM

[*]JOINs

[*]WHERE

[*]ORDER BY

[*]LIMIT

 

One more thing to note.  When you're doing ORDER BY, you should specify if you want it ordered in ascending order 'ASC' or descending order 'DESC'.  Since you have two fields you are ordering, you're essentially ordering your results by Account first, then if there are any duplicates under Account, it will order by Legal.

 

ORDER BY asmnt_ownership.Account ASC, asmnt_legal.Legal ASC";

[CountryGirl]

Thanks! That's what I was thinking, but wasn't sure.

 

Okay, I've tried this code:

 

"SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal
             FROM asmnt_ownership
             LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID
             WHERE asmnt_ownership.Account '{$search}' OR asmnt_ownership.OwnersName = '{$search}'
             ORDER BY asmnt_ownership.Account, asmnt_legal.Legal";

 

And this code:

 

"SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal
             FROM asmnt_ownership
             LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID
             WHERE asmnt_ownership.Account '{$search}' OR asmnt_ownership.OwnersName = '{$search}'
             ORDER BY asmnt_ownership.Account ASC, asmnt_legal.Legal ASC";

 

And neither of them are working. Why is this?

 

Qadoshyah

[zeodragonzord]

You missed the equal '=' sign on your first WHERE clause.

 

WHERE asmnt_ownership.Account = '{$search}' OR asmnt_ownership.OwnersName = '{$search}'

[CountryGirl]

You missed the equal '=' sign on your first WHERE clause.

 

WHERE asmnt_ownership.Account = '{$search}' OR asmnt_ownership.OwnersName = '{$search}'

 

Haha, I figured it had to be something simple !! I looked over the code several times and didn't even see it, LOL. That was it and it worked. Yes, this is great. Hopefully I can Join the other tables in correctly.

 

Qadoshyah

[CountryGirl]

Okay, I was going good until I tried adding in a 4th table and then I can't get anything.

 

Here's my SELECT query:

 

SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal, cmn_name.Address1, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_parcel.ParcelID
             FROM asmnt_ownership
             LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID
             LEFT JOIN cmn_name ON asmnt_ownership.NameID=cmn_name.NameID
             LEFT JOIN asmnt_parcel ON asmnt_ownership.NameID=asmnt_asmnt_parcel.NameID
             WHERE asmnt_ownership.Account = '{$search}' OR asmnt_ownership.OwnersName = '{$search}' OR cmn_name.Address2 = '{$search}' OR asmnt_parcel.ParcelID = '{$search}'
             ORDER BY asmnt_ownership.Account ASC, asmnt_legal.Legal ASC";

 

See anything wrong with this?

 

Qadoshyah

[zeodragonzord]

Change from

 

LEFT JOIN asmnt_parcel ON asmnt_ownership.NameID=asmnt_asmnt_parcel.NameID

 

to

 

LEFT JOIN asmnt_parcel ON asmnt_ownership.NameID=asmnt_parcel.NameID

 

 

Please please paste the error messages you get if something doesn't work.  It may help us help you.

[CountryGirl]

Change from

 

LEFT JOIN asmnt_parcel ON asmnt_ownership.NameID=asmnt_asmnt_parcel.NameID

 

to

 

LEFT JOIN asmnt_parcel ON asmnt_ownership.NameID=asmnt_parcel.NameID

 

 

Please please paste the error messages you get if something doesn't work.  It may help us help you.

 

Thank you! Thank you! That was it. I guess it helps to have a second set of eyes looking at it .

 

I haven't been getting error messages, it just won't return results. But, hopefully I won't have any more snags. We'll see. Hopefully I can get the rest of the tables joined tomorrow, so ya never know .

 

Qadoshyah

[zeodragonzord]

If you're doing this in PHP, put this on the top of your page.

 

error_reporting(E_ALL);
ini_set("display_errors", 1); 

 

It'll be sure to display errors on the page if one occurs.  Just remember to remove it if you're done testing and is ready for real usage.

[CountryGirl]

If you're doing this in PHP, put this on the top of your page.

 

error_reporting(E_ALL);
ini_set("display_errors", 1); 

 

It'll be sure to display errors on the page if one occurs.  Just remember to remove it if you're done testing and is ready for real usage.

 

Cool, thanks!!

 

I have another question:

 

I have 4 tables joined in at the moment. They return searches accurately most of the time. But, there are certain names that they won't return anything on, when I know there is something in the database for that entry. Why is it doing this? Do I have them joined wrong somehow? Should they all be joined to the same "Index" or "Unique" spot? This is what I have right now. And like I said above, it's working for a lot of entries, but not working for some. I can pull up things totally fine with account numbers, but doing the names sometimes is where I'm running into problems. Just not returning any results. And no, I'm not getting any error message.

 

"SELECT asmnt_ownership.Account, asmnt_ownership.OwnersName, asmnt_legal.Legal, cmn_name.Address1, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_parcel.ParcelID
             FROM asmnt_ownership
             LEFT JOIN asmnt_legal ON asmnt_ownership.KeyID=asmnt_legal.KeyID
             LEFT JOIN cmn_name ON asmnt_ownership.NameID=cmn_name.NameID
             LEFT JOIN asmnt_parcel ON asmnt_ownership.KeyID=asmnt_parcel.KeyID
             WHERE asmnt_ownership.Account = '{$search}' OR asmnt_ownership.OwnersName = '{$search}' OR cmn_name.Address2 = '{$search}' OR cmn_name.OwnersName = '{$search}' OR asmnt_parcel.ParcelID = '{$search}'
             ORDER BY asmnt_ownership.Account ASC, asmnt_legal.Legal ASC";

 

You can see what I'm talking about if you want with the page I'm testing on: www.wagonerassessor.com/searchjoin.php

 

If you type in something like "BERGEN, LARRY D" you'll return a result. But, if you type in something like, "ABSHIER, STEVE" you won't return anything. But, I know that there are records in the database. From the same table BERGEN, LARRY is from.

 

Searching by Account number & Parcel number, I always return the accurate info. I'm just running into problems when I try searching by name, address or legal info.

 

Thanks!

Qadoshyah

[zeodragonzord]

What table and field holds the names?  Also, explain the structure of your tables.  Such as the folllowing:

 

TableA

----------

field1

field2

field3

 

TableB

--------

field1

field2

field3