CODEWITHSUNDEEP
phpmysqlsql
MrPizzaFace
MrPizzaFace

Reputation: 8086

SQL query for many to many DB in PHP

enter image description here

SQL query to show all tool_tip_links related to pages_learn_more ID?

How do I structure this query correctly?

 $SQL = 
    "SELECT ptt.tool_tip_link
    FROM pages_tool_tip ptt
    JOIN pages_to_pages ptp
    ON ptp.tool_tip_id = ptt.tool_tip_id
    WHERE ptp.learn_more_id = 2";

    $result = mysql_query($SQL); // or die(mysql_error()); 


     while ($db_field = mysql_fetch_array($result))
    {   
   $reference_keys   =     $db_field['tool_tip_link'];  
        }
      echo $reference_keys;

How do I combine these queries to make them clean and efficient? Thanks for the help..

--------------------------------------UPDATE---------------------------------- Using this code: 

    "SELECT ptt.tool_tip_link
    FROM pages_tool_tip ptt
    JOIN pages_to_pages ptp
            ON ptp.tool_tip_id = ptt.tool_tip_id
    JOIN pages_learn_more plm
            ON plm.id = ptp.learn_more_id
    WHERE plm.id = 2";

Should Show me 3 tool_tip_links. However it is only returning the last field in the DB which is 4 -> LINK 4

enter image description here

So how do i get all of the links, not just the last one? ty again..

--------------------------------------UPDATE----------------------------------

When I echo out mysql_num_rows it displays correctly stating 3 for id #2.

So what code do I need to display all three rows links? LOOP ARRAY? I don't get it because It should already be looped in my array. Can i just echo $reference_keys or should I be doing something different?

I also tried a foreach and that returned an error. What am i doing wrong? please advise.

more db views --> hope it helps find a resolve..

this is the pages_learn_more table

this is the pages tooltip table

Upvotes: 4

Views: 601

Answers (2)

Luis Specian
Luis Specian

Reputation: 104

You should rewrite your query to:

"SELECT ptt.tool_tip_link
    FROM pages_learn_more plm
    JOIN pages_to_pages ptp
        ON plm.id = ptp.learn_more_id
    JOIN pages_tool_tip ptt
        ON ptp.tool_tip_id = ptt.tool_tip_id
    WHERE plm.id = 2";

This makes the pages_learn_more the main table on your join, and finds what you want.

Upvotes: 1

Adi
Adi

Reputation: 5169

According to your criteria:

SQL query to show all tool_tip_links related to pages_learn_more ID

Here you go 

SELECT ptt.tool_tip_link
FROM pages_tool_tip ptt
JOIN pages_to_pages ptp
     ON ptp.tool_tip_id = ptt.tool_tip_id
WHERE ptp.learn_more_id = :id

:id is the variable here.

Upvotes: 2

Related Questions