Pass query results to new query

hannerswilsonco

I am fairly new to php and I am building a pretty extensive E-commerce site. I have built my database and can query to get results. But what I can't do is query one table and pass those results to another table. For example:

Table 1 has a list of ID's (and other info) Table 2 has list of ID's from Table 1 cross referenced to Table 3. So Table 2 could have one ID from Table 1 that has say 10 or more different ITEMID's that are located in Table 3.

I need to get the results from query of table 2 and pass them to a query of table 3.

Thanks,
hannerswilsonco

eoghain

What you probably want to do is a JOIN query.

Table 1
---------
id

Table 2
----------
id
table1_id

Table 3
---------
id
table2_id

To get all of the ID's in table 3 where the ID in table 1 is 3 you'd write a query similar to this:

SELECT t3.id
  FROM table3 t3
 INNER JOIN table2 t2 ON (t3.table2_id = t2.id)
 INNER JOIN table1 t1 ON (t2.table1_id = t1.id)
 WHERE t1.id = 3

hannerswilsonco

code:--------------------------------------------------------------------------------

Table DIRECTORY

Table DIRECTORYITEM

DIRID (Comes from ID from DIRECTORY)
ITEMID

Table ITEM

ID (Comes from ITEMID from DIRECTORYITEM)

code written:

$query = "SELECT * FROM ITEM
INNER JOIN DIRECTORYITEM ITEMID ON (ITEM.ITEMID = DIRECTORYITEM.DIRID)
INNER JOIN DIRECTORY ID ON (DIRECTORYITEM.DIRID = DIRECTORY.ID)
WHERE DIRECTORY.ID='$prodtype'";
$result = mysql_query($query)
or die ("Couldn't execute query.");

What am I missing?

eoghain

$query = "
SELECT * 
  FROM ITEM 
 INNER JOIN DIRECTORYITEM ON (ITEM.ID = DIRECTORYITEM.ITEMID) 
 INNER JOIN DIRECTORY ON (ITEM.DIRID = DIRECTORY.ID) 
 WHERE DIRECTORY.ID='$prodtype'
";

That is your query written correctly. In my original example when I wrote "INNER JOIN table2 t2" the t2 in that statement was an alias for the table (just used so I didn't have to type out Table2.id), it looks like you were trying to identify the field to do the joining on in your querys. So I removed those entries, plus I made your ON clauses read correctly. When you do a JOIN the ON clause tells the database how you are JOINing these tables together so you need to populate it with the correct fields from databases that you've already identified in your query. So on your line "INNER JOIN DIRECTORYITEM" you have to make the ON clause tell the database how DIRECTORYITEM and ITEM relate to each other.

Finally your WHERE clause was incorrect, you don't separately quote the tablename and fieldname.