Skip to main content
multitalented_Captain16E5
Inspiring
June 22, 2015
Question

MySQL PHP - three tables - several rows in the thirde. How?

  • June 22, 2015
  • 13 replies
  • 1657 views

Hi I hope someone can help me with this.

I have got three tables.

doenet_posts  contains title and text

doenet_term_relationships contains the category

doenet_postmeta contains  telephone, email, website etc. in different rows with an own id which is'meta_ID'

I didnot design the database and cannot change it.

If I want to get only e.g. 'telephone' from doenet_postmeta then I have no problem

but if I want also the others to add I don't know how to seperate the telephone, email,website etc from each other.

Also because of p.ID = m.post_id I get as much results as there are corresponding  telephone, email,website etc rows

Below is my MySQL code so far.

Any help is greatly appreciated.

$query = "SELECT p.ID, p.post_title, p.post_status, p.post_type, p.post_content, r.object_id, r.term_taxonomy_id, m.meta_id, m.post_id, m.meta_key, m.meta_value FROM doenet_posts p LEFT JOIN (doenet_term_relationships r, doenet_postmeta m) ON (p.ID = r.object_id AND p.ID = m.post_id) WHERE (m.meta_key='telephone' AND r.term_taxonomy_id = 89 AND p.post_status LIKE 'publish' AND p.post_type LIKE 'doeodse-gids') AND (p.post_title IS NOT NULL AND p.post_title != '') ORDER BY p.post_title ASC";$results = mysql_query($query);

$row_results = mysql_fetch_assoc($results);

This topic is closed to new replies. Start a new post to keep the conversation going.

13 replies

ElizabethGailLittle
ElizabethGailLittle
Inspiring
June 25, 2015

Do you really need a JOIN?  If there is a common variable in the three tables, you can use a SELECT variable list.... FROM file .... WHERE file variable=[whatever variable is common].  Or are you trying to compile a list of all variables from all 3 tables?

multitalented_Captain16E5Author
Inspiring
June 26, 2015

Thanks for helping ElizabethGailLittle,

Where can I find a more extended example of SELECT variable list?

bregent
Participating Frequently
June 30, 2015

Hi DVita, sorry I have not been able to get back - been swamped at work. I see you have posted a new thread about loops. Is this because you have not been able to get the JOIN to work? A JOIN is likely a better solution than a nested loop.

doezo_posts is your main table. For each row in doezo_posts, how many rows can be in the other two tables. Can it be zero? One? More than one?  This will help determine if you need an innner or outer join.

ElizabethGailLittle
ElizabethGailLittle
Inspiring
June 24, 2015

‌1.  What is the variable in which file for which you are trying to get the data in postmeta?

2.  Is the id in postmeta contained in either posts or term_relationships?  If so, what are the variable names?

I may be able to help if you can answer these questions.

Gail

multitalented_Captain16E5Author
Inspiring
June 25, 2015

Thanks Gail,

I now think my problem is more how to get a loop with the meta_value from doezo_postmeta within the main loop.

I will be back here tomorrow.

bregent
Participating Frequently
June 23, 2015

Sounds like you have a cartesan product. That syntax might be valid, but I've never seen a join of 3 tables using only one JOIN statement and combining the other two tables in parens. I would rewrite that using 2 JOIN statements - it will be much easier to read too.

>doenet_postmeta contains  telephone, email, website etc. in different rows with an own id which is'meta_ID'

Not sure what you mean. What is the relevance of 'meta_ID'? It's not being used in your statement.

Also, are you sure you really need an outer join? What is the relationship of the tables?

> p.post_status LIKE 'publish' AND p.post_type LIKE 'doeodse-gids'

What's the point of using the LIKE predicate without any wildcards?

multitalented_Captain16E5Author
Inspiring
June 23, 2015

Thanks for your help Bregent,

I changed LIKE to =

I still have meta_id thee because it is the pimairy key and I have the idea I might need it to get the result I want.

I allready started to use two joins but I am still looking for good examples so far as I ad the second JOIN it gives an empty result.

It is the first time I use two JOIN's so probably I make a basic mistake. I am looking for some good examples.

This code is:

$query = "SELECT p.ID, p.post_title, p.post_status, p.post_type, p.post_content, r.object_id, r.term_taxonomy_id,

tel.meta_id AS id_tel, tel.post_id AS post_tel, tel.meta_key AS key_tel, tel.meta_value AS value_tel,

email.meta_id, email.post_id, email.meta_key, email.meta_value AS value_email

FROM doezo_posts p INNER JOIN doezo_term_relationships r  ON p.ID = r.object_id

JOIN doezo_postmeta tel ON p.ID = post_tel WHERE key_tel = 'telefoon'

JOIN doezo_postmeta email ON p.ID = post_email WHERE key_email = 'email'

ORDER BY p.post_title ASC";$results = mysql_query($query);

$row_results = mysql_fetch_assoc($results);

bregent
Participating Frequently
June 23, 2015

OK, now you've got 3 joins and a WHERE clause within the JOIN statement. If you can, please post and entity relationship diagram so we can see how the tables relate, and if the relationships are one-to-many, many-to-many, etc. Most importantly, tell us if any of the tables MUST have a corresponding row in the other tables, so we know whether or not you need to use an OUTER join.

A picture is worth a thousand words.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices