[SOLVED] Is using JOIN to access multiple tables a Hard Must?

[chadrt]

VERY NOOBIE HERE!!!

 

I am very new to PHP and MySQL I just started working with it for the first time last week.  I successfully have created a database structure and modified some scripts to load the information into the database on a daily basis and now I have been playing around a bit designing a database access page.  It has been a rough road but so far so good I have it working as expected.  My question is this I am accessing data that spans across multiple tables and I am using this as my method of accessing those tables.

 

mysql_connect($dbhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$queryam="SELECT * FROM AM WHERE callsign='$callsignsearch'";
$resultam=mysql_query($queryam);

$queryen="SELECT * FROM EN WHERE callsign='$callsignsearch'";
$resulten=mysql_query($queryen);

$queryhd="SELECT * FROM HD WHERE callsign='$callsignsearch'";
$resulthd=mysql_query($queryhd);

$num=mysql_numrows($resultam);

mysql_close();

 

Now while browsing the forums here I see something talked about called JOIN and I have downloaded some tutorials, read some turtorials on this subject and I must say it has me confused in a big way.  Every time I try and create an sql statement to retrieve the information using a JOIN method I get errors of one type or another.  I have tried examples from multiple sites and different JOIN methods but still come up with the same.

 

Now those three tables are the only tables in the database.  But the there are almost a million records I think it was 987,000 last I checked and growing every day.  I dont know enough about this stuff to really be able to know wether I can do this better or not.  If anyone has any suggestions please let me know.  I am eager to hear the feedback!!

 

Here is the project itself.  http:// ki4mve.com/cs and you can use my callsign of KI4MVE as search criteria.  I put a space in the link because I dont want it to be picked up by the bots.

[Wildbug]

Can you explain a little bit about your tables?  What data do they contain?  What is their structure?  I have an idea that you might be looking for a UNION instead of a JOIN.  I have another idea that you might have been able to put all the information in one table, but that might be too big an assumption until I know a little more about your setup.

[chadrt]

The data is FCC callsign information for Amateur Radio Operators throughout the US.  There is a lot of fields in each table but for public information purposes I only use the following fields This is an actuale structure.

 

Table HD:

unique_id

callsign

license_status

grant_date

expire_date

cancellation_date

 

Table EN:

unique_id

callsign

entity_name

street_address

city

state

zipcode

po_box

 

Table AM:

unique_id

callsign

operator_class

group_code

region_code

previous_callsign

previous_operator_class

trustee_name

 

I have learned that the callsign field is not unique to a given record as the FCC allows the callsign to be reissued due to inheritance from a family member or a club taking a callsign over or time to reissue has been passed so the callsign goes back into the pool.  However the unique_id is allways unique no table has two identical unique_id's!  I hope I have given you enough of what you were looking for.  If you have any other questions please dont hesitate to contact me!!!

 

I can show you the code to create the tables just if requested.

[btherl]

If the unique ids match between tables, then you can try something like this:

 

SELECT * FROM HD
JOIN EN USING (unique_id)
JOIN AM USING (unique_id)
WHERE HD.callsign = 'blah'

 

That will look up callsigns using the HD table, and then use the unique ids to find matching rows from the other two tables.  Is that what you're looking for?  You might also want to try this condition:

 

WHERE HD.callsign = 'blah'
OR AM.callsign = 'blah'
OR EN.callsign = 'blah'

[chadrt]

Will that only work in PHP or something because I tried that from the mysql command line and it took so long that I just CTRL+C'd it to Abort it almost locked up or something.  I have tried that before and had the same response.  I thought I was doing something wrong but now I am wondering if there is a problem with my mysql installation.

 

You nailed the code though evend the capitol table names so I copied and pasted the code you had there. 

[btherl]

Hmm.. do you have indexes on both callsign and unique_id on all table?  Not having those indexes would cause a VERY slow query, if those tables are large.

 

Try these:

 

CREATE INDEX EN_unique_id ON EN (unique_id);
CREATE INDEX EN_callsign ON EN (callsign);

 

Both of those for each table.  If it doesn't help, then run your query with "EXPLAIN" at the start, like "EXPLAIN SELECT ...", and post the output here.

[chadrt]

I just learned a very important thing here.  I have been accessing that database from with the same network and having great response time.  But I am visiting the inlaws right now and one of my queries is not working at all and that is the one with the multiple tables being accessed.  I will try indexing them and see what happens with the broken script.

[chadrt]

Sorry about posting twice, I didnt see an edit function.

 

I ran those index's and that was amazing.  It made all my scripts work but not just work they are near lightning fast.  My only question on those is I have a script that reloads the database on monday morning with fresh data.  Do I need to recreate those or will they from this point on automatically keep themselves up to date?  I am actually impressed with the speed at which the lookup scripts perform now.  Thanks again...

[Illusion]

Once you created index in a table , it is forever unless you delete it. No problem even the data is updated its keep indexing it.