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

Report · Jun 22, 2015

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);

Report · Jun 22, 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?

Report · Jun 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);

Report · Jun 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.

Report · Jun 24, 2015

Thanks Bregent,

I succeeded to move on e little.

But the problem I started with remains.

I have to select and single out this info: 'telefoon', 'email', 'adres', 'website' from the table doezo_postmeta.

tel.post_id has its value from p.ID the main table doezo_posts.

tel.post_id has more then one entry in this table.

tel.meta_id has only one but I don't see how to take advantage of it.

I can get one of them like 'telefoon' but the others stay out of reach.

I also have problems choosing the right type of join.

The test results from a test database you can find on:

http://jmwagenda.nl/2015/join35.php

here de info inherets from the earlier posts only one is correct.

http://jmwagenda.nl/2015/join34.php

with // removed. Only one info item remains and is correct.

Some screenshots will follow in the next post.

I removed code where it seemed not relevant for my question.

The code:

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

$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, tel.post_id, tel.meta_key, tel.meta_value AS meta_tel

FROM doezo_posts p

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

INNER JOIN doezo_postmeta tel ON p.ID = tel.post_id

WHERE (r.term_taxonomy_id = 89 AND p.post_status = 'publish' AND p.post_type = 'doezse-gids') AND

(p.post_title IS NOT NULL AND p.post_title != '') AND

tel.meta_key IN ('telefoon', 'email', 'adres', 'website' )

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

$row_results = mysql_fetch_assoc($results);

echo "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n";

echo "<dataLoaded>\n";

if (!empty($row_results["post_title"])) {

do {

//$telefoon = "";

//$email = "";

//$adres = "";

//$website = "";

if (($row_results["meta_key"] == 'telefoon')) { $telefoon = "" . "Telefoon: ". html_entity_decode($row_results["meta_tel"]) . "" . " "; }

if (($row_results["meta_key"] == 'email')) { $email = "" . "Email: ". html_entity_decode($row_results["meta_tel"]) . "" . " "; }

if (($row_results["meta_key"] == 'adres')) { $adres = "" . "Adres: ". html_entity_decode($row_results["meta_tel"]) . "" . " "; }

if (($row_results["meta_key"] == 'website')) { $website = "" . "Website: ". html_entity_decode($row_results["meta_tel"]) . "" . " "; }

$gegevens = array($telefoon, $email, $adres, $website);

foreach ($gegevens as $value) {

echo "$value";

}

} while ($row_results = mysql_fetch_assoc($results));

}

Report · Jun 24, 2015

The screenshots:

doezo_posts which is the main table:

doezo_postmeta after searching on 'telefoon':

doezo_term_relationships used for selecting the category's. Seems to me less relevant for my question.

Report · Jun 24, 2015

I will rephrase my question tomorrow in a new item.

So don't bother to answer this.

I think I can put my question more clearly.

I hope you will have a look at it again.

Thanks Bregent

Report · Jun 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

Report · Jun 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.

Report · Jun 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?

Report · Jun 26, 2015

Thanks for helping ElizabethGailLittle,

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

Report · Jun 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.

Report · Jun 30, 2015

Thanks Bregent,

I didnot get hold on the join with three tables and time is running out. I got the idea I am close to a solution using the nested loop now.

In Doezo posts in can be 0 (although we don't want this, it could be) up to 7. But I want to filter it down to 4. Those are translated to English: telephone-number, email-adress, home-adress, website-adress.

Within the php script it all has to be formatted as an XML-format.

If you have any ideas which can point me in the right direction it would be helpfull.

Report · Jul 01, 2015

Thanks bregent and ElizabethGailLittle,

I succeeded in getting it done with the nested query as mentioned. Thanks for your help.

See: PHP/MySQL while loop within while loop from database