to much info in database

[soullessj]

hi im trying to create a database but i end up with to many sections in the tables. im using and id section to link mulitole tables and display the information from the tables corresponding the id.

 

ie. characters from a tv show.

 

id              1

 

charac      character 1

actor        actor1

desc          description

 

charac 2    character 2

actor 2      actor 2

desc 2      description 2

 

IE.

 

tv shows

 

id                  1

 

name            show name

desc            show description.

 

if i wanted to display info for show 1 the query would say that table 1 id(tv show) = table 2 id(characters)

and then i would be able to display the info from those tables. but if there are alot of characters then it becomes a bit of a pain having to repeat the sections in the table for each character charc 1, charac2, charac3 etc...

 

is there anyway to make this simpler either by being able to store and retrieve them differntly or store the information in an external file such as an xml and retrieve it from there.

 

any help would be appreciated thanks

[soullessj]

benn using a query like this to link corresponding id's to get data that matches the first table.

 

SELECT test. *, test2. * FROM test, test2 WHERE test.h_id=test2.h_id AND test.h_id=$id

[monkeytooth]

Look up JOIN, LEFT JOIN, INNER JOIN, RIGHT JOIN, OUTER JOIN.. and then restructure your query's to work in those fashions. Which one to use I guess would mostly be up to you. They are all in concept similar but handle differently than the other.

[soullessj]

i am using join to join the tables but if i tried to put all the characters under the same id it tends to become a bit long in rows.

[monkeytooth]

Yea my fault i didnt pay to well attention. If its a hassle like you say you could dump the output in xml format, json format and iterate through that if its easier for you. Maybe a concat in the query but thats a tough one to call on my part.

[soullessj]

im still new to php thats why i was wondering if it would be easier like that . for example if there were 20 characters i would have to create a name desc etc.. table for each one to store the information like charac1, charac 2, charac 3.

desc 1, desc 2, desc 3. as you can see it gets a bit long and tiresome to do that for each character.

[soullessj]

is there anyway i can store them in different rows with a common colum and join them together

[ignace]

Your DB is lacking a proper design that's why it's so hard to retrieve the information.

 

tv_show (tv_show_id, ..)

tv_show_character (tv_show_character_id, tv_show_id, played_by, ..)

 

actor (actor_id, ..)

 

To get the TV characters and the actor information.

 

SELECT ..
FROM tv_show_characters T1
LEFT JOIN actor T2 ON T2.id = T1.played_by
WHERE T1.tv_show_id = ?