ProgrammingThis forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
I'm trying to write a PHP program that accesses an SQL database. It is my first endeavour into SQL, and I'm confused. My book, PHP and MySQL (Peachpit press), talks about "one to many" relationships, but doesn't make it clear how it works.
I want to create a page that will have a header, a footer, and multiple elemets inside (kind of like a blog). The table would be:
// main table
main_id - INT UNSIGNED NOT NULL AUTO_INCREMENT
another_id -> another table
header - TEXT
footer - TEXT
// another table
another_id - INT UNSIGNED NOT NULL AUTO_INCREMENT
body - TEXT
According to my book, this is how you do it, but there can only be one "another_id" in the main table, right? How do I get it so I can have as many as I want?
1 more text
Its a very clumsy way of doing this sort of thing. As is my display of the problem!
I don't understand your statement that says there can be only one value of another_id in the main table. It is not set to unique and its not the primary key so there can be any value of another_id in the main table as the above example shows.
When doing one-to-many and many-to-many relationships in any database your looking at creating multiple tables.
Normally one table would hold info about the page, one table for the body and one table for the links between the two.
A page table could hold page_id, header_text and footer_text (assume more than one combination of header and footer text).
A body table could hold body_id and body_text (again assume multiple entries, like pages of a book for example).
Finally a link table that could hold a unique identifier, page_id and corresponding body_id. This linking table can hold all the combinations you need of header/footers and body text.
I mean that there can only be one vaule for another_id at a time.
I've thought a little more about this and have come to the conclusion that to have mulitple items following a given pattern, I'd have to add rows to a table. Since another_id could only represent one one row in the table, I'd have to have a table for each page. Are these assumptions correct?
That's a pretty good idea about putting the header and footer. I'll incorporate that.
We can see from this that there will be alot of duplicate headers and footers in the main table, while the join table with the foreign key has unique information.
A better table layout would be to have the body that is always changing in the main table or to even have a different table in it with its own primary id, if for some products, for instance, use the same information.
cost (dec(6,2)) -- Product specific
price (dec(6,2)) -- Product specific
msrp (dec(6,2)) -- Product specific
etc ... -- Other product specific data
The whole idea of a relational database is to not have to duplicate data if you don't have to. In the example above, the header, footer, body and optionally the product_type tables all grow as needed with no duplicate data what-so-ever. Each of their primary keys is referenced as a foreign key in the product table. And the primary table (product) is kept to a minimal size. The queries would be extremely fast as you could then do unique compound indexes on the product table for (product_id, product_type_id) and you can create an index on the unique indexes on each id column of the join tables.
SELECT h.header, pt.product_type, h.header, b.body, f.footer, p.price, p.msrp
FROM product p, product_type pt, header h, body b, footer f
WHERE p.product_id = $var1
AND p.product_type_id = $var2
AND p.product_type_id = pt.product_type_id
AND p.header_id = h.header_id
AND p.body_id = b.body_id
AND p.footer_id = f.footer_id