Help answer threads with 0 replies.
Go Back > Forums > Non-*NIX Forums > Programming
User Name
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.


  Search this Thread
Old 08-31-2003, 11:23 PM   #1
Registered: Dec 2002
Location: The land of GMT -6
Distribution: OS X, PS2 Linux, Ubuntu, IRIX 6.5
Posts: 399

Rep: Reputation: 31
Multiple values with SQL (Noob stuff)

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
another_id -> another table
header - TEXT
footer - TEXT

// another table
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?
Old 09-01-2003, 05:46 AM   #2
Registered: Aug 2003
Posts: 208

Rep: Reputation: 30
The header and footer will stay the same on each page?

If so there is no need to hold it in a database, but the tables look OK for one-to-many. The main table could hold things like

main_id another_id header footer
0 0 head.php footer.php
1 1 head.php footer.php
2 0 head.php footer.php

The another table holding

another_id body
0 text
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.

Last edited by Andy@DP; 09-01-2003 at 05:51 AM.
Old 09-01-2003, 05:56 AM   #3
Registered: Aug 2003
Posts: 208

Rep: Reputation: 30
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.

Hope this helps (a little)
Old 09-01-2003, 11:54 AM   #4
Registered: Dec 2002
Location: The land of GMT -6
Distribution: OS X, PS2 Linux, Ubuntu, IRIX 6.5
Posts: 399

Original Poster
Rep: Reputation: 31
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.
Old 09-03-2003, 06:52 AM   #5
Registered: Aug 2003
Posts: 208

Rep: Reputation: 30
Sorry about the delay, your assumptions are correct. You would need multiple rows, one for each page.
Old 09-03-2003, 11:06 AM   #6
Registered: Dec 2002
Location: The land of GMT -6
Distribution: OS X, PS2 Linux, Ubuntu, IRIX 6.5
Posts: 399

Original Poster
Rep: Reputation: 31
Old 09-03-2003, 09:38 PM   #7
Registered: Feb 2002
Location: Amarillo, Texas
Distribution: Slackware 9.1 (desktop) / WinXP w/VMware linux dev/Tawie Server Linux (TSL) 2.0 (servers)/ LFS (dev)
Posts: 47

Rep: Reputation: 15
The above table schema would allow for the following:


main_id (primary key)
another_id (foreign key)


another_id (primary key)


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.

TABLE: product
product_id (auto-increment)
product_type_id (smallint)
header_id (int)
body_id (int)
footer_id (int)
cost (dec(6,2)) -- Product specific
price (dec(6,2)) -- Product specific
msrp (dec(6,2)) -- Product specific
etc ... -- Other product specific data

TABLE: product_type
product_type_id (auto-increment)
product_type (char)

TABLE: header
header_id (auto-increment)
header (char)

TABLE: footer
footer_id (auto_increment)
footer (char)

TABLE: body
body_id (auto_increment)
body (char/blob/text)

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

Hope this helps!

Last edited by jfshadow; 09-06-2003 at 01:03 PM.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing form values between multiple forms!! AskMe Programming 5 09-07-2005 08:44 PM
Multiple SCREENS, different WM's. And some other stuff... xeristian/null Linux - Software 1 04-25-2005 04:25 PM
xmodmap for multiple values malo_umoran Slackware 3 03-27-2005 10:39 AM
SQL: making queries from multiple tables ganninu Programming 1 01-08-2004 12:17 PM
storing multiple values within one field in mysql antken Programming 8 12-15-2002 11:08 PM > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 09:05 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration