Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have 2 tables. The parent table is learn_more and child table is reference_keys Both tables are innodb

reference_keys has two columns:
key_id [index]
key_href


learn_more table
id [primary]
keys_id [foreign key]
page_title    
page_content

What I am trying to do is get multiple links in the learn more table from the reference_keys table. So example, learn_more table id:1, keys_id:1,3,4,8,13,25,..., page_title:Home Page: blah blah, page_content: blah blah......

The problem is that phpmyadmin will not allow me to put more than 1 id in the keys_id of learn_more.

//ERROR
//Warning: #1265 Data truncated for column 'keys_id' at row 1

I'm guessing the relation view is not setup correctly. - How do i fix this?

and on my page it shows the key_id in the echo instead of the value for the id: which is the key_href. so my page show "1" instead of the value for 1 which is a link..

Perhaps my sql query is not correct?

$SQL = "SELECT * FROM learn_more WHERE page_title = '$this_page'";
share|improve this question
1  
You have a one to many here, where one reference_key can be referenced by many learn_more but each learn_more may only reference one reference_key. Are you looking instead for a many-to-many? –  ametren Jul 11 '12 at 17:12
 
Yes i need a many to many - sorry im just learning this –  fabio Jul 11 '12 at 17:15

3 Answers

up vote 2 down vote accepted

To build a many-to-many here is what you could do:

reference_keys has two columns:
key_id [index]
key_href

learn_more_to_reference_key
reference_key_id [FK to reference_keys]
learn_more_id [FK to learn_more]

learn_more table
id [primary]
page_title    
page_content

Then you have essentially a 1:N on each side of the relationship. Notice that I removed the FK from the learn_more table, too.

So to grab the relationship you'd query like this:

SELECT * FROM Learn_More lm 
INNER JOIN learn_more_to_reference_key lmtrk ON lm.id = lmtrk.learn_more_id 
INNER JOIN reference_keys rk ON rk.id = lmtrk.reference_key_id

I believe the inner join is correct, i'm double-checking that.

share|improve this answer
 
i see so i need a third table to make the connections there? –  fabio Jul 11 '12 at 17:19
 
@FabioAnselmo Yes, that's correct. –  ametren Jul 11 '12 at 17:23
 
thx for the qry statement i will report back if i have issus- ty again –  fabio Jul 11 '12 at 17:24
 
@FabioAnselmo by the way you should be able to add your where clause to the query I provided: where lm.page_title = '".$this_page."' –  ametren Jul 11 '12 at 17:26
 
You code worked great but im noticing it only displays the 1 link. So if for example I have lm.id set for 2,3,4,5,6,7.. it only displays 2 on the page.. –  fabio Jul 11 '12 at 19:01
show 1 more comments
up vote 1 down vote

If you want to have one row in learn_more correspond to multiple rows in reference_keys, you need to move the foreign key field from the learn_more table to the reference_keys table.

So instead of having a foreign key field in learn_more that points out to multiple rows of reference_keys (which, as you seem to be running into, is not supported), you have the multiple rows of reference_keys all point back to the learn_more table.

This would implement a one-to-many relationship between learn_more and reference_keys. If you need a many-to-many relationship (where each reference_key rows can be connected to many learn_more rows and vice-versa) you need to use a third table to establish a link between the two databases. See http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php for more information.

share|improve this answer
up vote 1 down vote

You're getting the keys_id because that is what is in the learn_more table. To get the key_href, you'll need to JOIN the learn_more table to the reference_keys table. Also, @ametren is correct - you should have a many-to-many table that links your two current tables.

key_linking_table
id [primary and foreign key]
keys_id [primary and foreign key]

$SQL = 
"SELECT lm.id, lm.page_title, lm.page_content, rk.key_href
 FROM learn_more AS lm
 LEFT JOIN key_linking_table AS klt
 ON klt.id = rk.id
 LEFT JOIN reference_keys AS rk
 ON klt.key_id = rk.key_id
 WHERE [condition]"
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.