Jump to content

Archived

This topic is now archived and is closed to further replies.

CountryGirl

Putting multiple tables into a search

Recommended Posts

I changed the code a little, since I realized I was being a bit redundant ;). It is working better than it was, but it is still not returning a few names.

 

This is what I changed it to:

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

 

The table that contains the names is cmn_name and the field that holds the name is OwnersName.

 

The structure of the 3 tables I'm using above is:

 

asmnt_legal

---------------

KeyID

Account

Legal

 

cmn_name

----------------

KeyID 

NameID

RowID

OwnersName

Address1

Address2

City

State

ZipCode

 

asmnt_parcel

------------------

KeyID

RowID

Account

ParcelID

Township

Range

Section

Quarter

NameID

OwnersName

SubdivisionName

LotNumber

ParentAccount

TotalValue

TaxAreaCode

LotSize

LotSizeType

 

Thanks!

Qadoshyah

Share this post


Link to post
Share on other sites

You seem to be joining tables by KeyID of each table.  Are you certain that those should match, that KeyID should be the same always?  When you insert a row in each table, do you track the KeyID so that all should match or are you relying on auto-incrementing primary key IDs?

Share this post


Link to post
Share on other sites

You seem to be joining tables by KeyID of each table.  Are you certain that those should match, that KeyID should be the same always?  When you insert a row in each table, do you track the KeyID so that all should match or are you relying on auto-incrementing primary key IDs?

 

Well, that's where I'm running into problems I think. I think some of the KeyIDs match, but I don't believe they all do. What I'm confused by is that not every table has the same primary key. Do I have to link them by a primary key or just anything that will make them pull them correct info? The company I'm working for just switched webhosts, so I am going to have to re-do everything anyways (...sigh...). And put everything into a new MySQL database on the new host. So, I may be able to get it better organized this time around.

 

Qadoshyah

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.