[SOLVED] Slightly More Advanced mySQL/PHP with multiple tables.

[desoto0311]

I went through the tutorial by 'Crayon Violet' and am stoked at how quickly I got up to speed on basic mySQL/php queries. What I'm playing around with now is expanding on the tutorial by trying to access and display data from multiple tables, since this seems to be a common theme (best practice?) when setting up DB's.

 

I normally would have tried to put all information into a row in one single table so that I can use my 'basic' set of commands to access information, but this can get cumbersome for the DB I think.

 

Okay, so here goes:

 

I have three tables I created:

table 1 (id, name, name2, table2sub)

table 2 (id, name)

table 3 (id, name, table1ref_id)

 

What I CAN do now thanks to the tutorial is display/modify/edit/delete anything I want from table 1.

What I'd LIKE to be able to do is to display (for example) all items from table 1 that have the same 'table2sub' id from table 2 and display the subsequent id & name from table3 IF table 3 has a reference to a row in table 1 from table1ref_id. Make sense?

 

So an example would be:

(table1)

1,firstname,secondname,2,3

2,alt,secondalt,2,5

3,altotra,secondotra,2,4

4,alternate,alt2,1,5

(table3)

1,name,1

2,name,1

3,name,2

4,name,1

 

So if I'm querying all table 2 records with a "2", table 1 records 1,2,3 should display, and the record from table one should be displayed with all table 3 data for records 1,2,4 (since it's 3rd field id is "1")

 

Phew. I'm guessing another tutorial search may be in order, but if someone can point me in the right direction in regards to sql queries I'd much appreciate it.

 

Thanks,

-D

[thepip3r]

you need to look into either JOINS or subqueries. 

 

SELECT * FROM tab1, tab2 WHERE tab2.tab1id=tab1.id

 

SELECT * FROM tab1 JOIN tab2 ON (tab2.tab1id=tab1.id)

 

those are very simplistic examples.

[desoto0311]

Cool-

 

What about something like:

 

SELECT * FROM table1 WHERE table2sub equals $table2id ORDER BY name

 

or am I way off? lol.

 

I guess a subquery would somehow then come into play after I get all of my table 1 data that matches the table2 id where I need to then pull all table 3 info that matches table 1....  Phew. Still a bit lost but it's somewhat making sense....

[thepip3r]

close... but this is not a join.  if 'table2sub' really exists in table one, then you're never really correlating data from table 2 in any way.

SELECT * FROM table1 WHERE table2sub='$table2id' ORDER BY tabname

[desoto0311]

SELECT DISTINCT id FROM table1, table2 WHERE table2.id=table2sub;

 

Better? Or is this slightly reversed?

[desoto0311]

Oh, I see what you did there, makes sense.

 

How would you pull off a 'sub-search' then for all items in table 3? Would you basically set the results of this SQL query as a variable (array?) that's then plugged into a second SQL query?

 

Something like: SELECT * FROM table3 WHERE $arrayResult = something something something.... okay I lost it, lol.

[desoto0311]

I checked another site about 'subqueries' and this looks promising as well:

 

SELECT id FROM table3 WHERE id IN (SELECT table1refid FROM table3 WHERE table1refid=table1.id);

 

Would that be accurate? Of course this gets me back to the fact that this query really would need to be done AFTER all table1 records that match the table2 id's have been selected.

 

It's still a 'double-query' type deal....

[thepip3r]

to select all data from all tables that you listed in your initial post, this is what I would do because i'm much more familiar with joins than i am subqueries.  Well i have to modify your table structure since table2 doesn't have any association to anything.  Also you data sets are confusing w/o descriptions so i'm going to make some relational data fields so you can see how associations work:

 

table -> columns

people (pid, first_name, last_name, mid_name)

locale (lid, pid, address, city, state)

contact (cid, pid, email, phone)

 

SELECT * 
FROM people
JOIN locale ON (locale.pid=people.pid)
WHERE people.first_name='Larry'

 

this would pull (pid, first_name, last_name, mid_name, lid, pid, address, city, state) from both tables where the first_name is Larry. 

 

If you want data from all three tables:

 

SELECT * 
FROM people
JOIN locale ON (locale.pid=people.pid)
JOIN contact ON (contact.pid=people.pid)
WHERE people.first_name='Larry'
AND locale.state='FL'

 

this would pull back EVERY column from all three tables for all Larrys who live in Florida.  does that make more sense with how to build relationships between the tables?  we've put a "pid" or "peopleid" field into the other two tables because that is how the data is associated.  the locales and contact information is SPECIFIC to those people so we must have a way to differentiate that in a clear and concise manner. 

[desoto0311]

Yes, I *mostly* get it, thanks. I did actually tie all three tables together by way of id's. Essentially table 2 is 'categories', and all data in table 1 MUST belong to one of the categories in table 2. Table 3 is a collection of 'other' information, with each data row belonging to a data row in table 1.

 

I tried to name the rows so that it would make sense, but I don't think I did a great job of it, lol. Additionally, it looks like I might not have setup the tables in the most concise way. I *think* I should be able to make this work based somewhat on your code. After I get the SQL results I'm after then I'll confer with the SQL tutorial I've been using on this site to setup the loops necessary to display the stuff.

 

Thanks a bunch!