LinuxQuestions.org
LinuxAnswers - the LQ Linux tutorial section.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
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.

Notices

Reply
 
LinkBack Search this Thread
Old 08-31-2003, 10:23 PM   #1
Travis86
Member
 
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
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?
 
Old 09-01-2003, 04:46 AM   #2
Andy@DP
Member
 
Registered: Aug 2003
Location: Aberdeen, UK.
Distribution: Debian, Ubuntu
Posts: 208
Blog Entries: 2

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 04:51 AM.
 
Old 09-01-2003, 04:56 AM   #3
Andy@DP
Member
 
Registered: Aug 2003
Location: Aberdeen, UK.
Distribution: Debian, Ubuntu
Posts: 208
Blog Entries: 2

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, 10:54 AM   #4
Travis86
Member
 
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, 05:52 AM   #5
Andy@DP
Member
 
Registered: Aug 2003
Location: Aberdeen, UK.
Distribution: Debian, Ubuntu
Posts: 208
Blog Entries: 2

Rep: Reputation: 30
Sorry about the delay, your assumptions are correct. You would need multiple rows, one for each page.
 
Old 09-03-2003, 10:06 AM   #6
Travis86
Member
 
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
OK
 
Old 09-03-2003, 08:38 PM   #7
jfshadow
Member
 
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 TABLE

main_id (primary key)
another_id (foreign key)
header
footer

0|0|header1|footer1
1|0|header2|footer2
2|1|header1|header1
3|1|header1|header1
etc...

JOIN TABLE
body
another_id (primary key)

0|body1
1|body2
2|body3
3|body4
etc...

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.

EXAMPLE SQL:

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 12:03 PM.
 
  


Reply


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
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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


All times are GMT -5. The time now is 04:47 PM.

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