database and mysql help

[soullessj]

hi

 

i want to store information in a database and call for it later but the problem i have is that i end up with to long a row.

 

ie. character table for tv shows.

 

id                        1

charac1                character 1

actor1                  actor 1

desc1                  description 1

charc2                  character 2

actor2                  actor 2

desc2                  description 2

 

as you can see it gets a bit much for when there are many of them.

what i want to know if it is possible to make a table like this

 

id                              1

chid                            show1

charc                          character

actor                          actor

desc                          description

 

id                              2

chid                            show1

charc                          character

actor                          actor

desc                          desc

 

id                              3

chid                            show2

charc                        character

actor                        actor

desc                          description

 

then i want to query the database and take all the info from the rows that have the same id ie. chid

and store each one of the row's in there own array.

 

so that i can display them how and where i want

 

[fugix]

you absolutely can make a table like that, and it is recommended. You would simply create a table with the fields; id, chid, charc, actor, desc. make the id field the primary key with auto-increment enabled. Use an INSERT clause to add information for each character. When you want to extract the data from you database, you will want to use something like this.

$character = $_POST['character'];
$query = mysql_query("SELECT * FROM table_name WHERE charc = '$character'");
$row = mysql_fetch_assoc($query);
$db_character = $row['charc'];

etc...the $character variable will be whatever character the user types. then you will search the database for that character and return whatever fields you'd like form that row, hope this helps

[soullessj]

is it possible to put each row into there own array so that i can call them separately  and in place them in different places instead of getting it all in one array

[soullessj]

maybe if i put another id for character and add

 

AND characid=1 or whatever the id is and repeat it to store the information in different array's

 

would that work

[fugix]

yes, if you draw out a single row you will get the results that you want.

[soullessj]

okay thanks for the help i will try it out

[fugix]

okay thanks for the help i will try it out

okay, let us know if you need further assistance

[soullessj]

i just noticed but the code you showed above wouldn't it only show that row with character name matching the charac column so it would only retrieve one row into the array

[fugix]

i just noticed but the code you showed above wouldn't it only show that row with character name matching the charac column so it would only retrieve one row into the array

yes

[soullessj]

hi

 

i haven't tried using the character script above yet as im still trying to get the databases sorted out.

 

i've retried putting the database together.

Sorry i tried to save a copy of the tables but it didn't seem to want me to view them so i cant post it now will have to do it tomorrow

 

i've got two tables one called anime and the other ani_character.

 

i've got an id colum and then i have a a_id column in the anime table and an a_chid column in the ani_character table.

 

what i've done is use the a_id and a_chid colums to connect the tables together. ie. i've used bgc in a_id and a_chid to join them together for one show where anime has 1 insert about the show and ani_character has 4 inserts (4 characters) and i've used bgc as the a_chid for all 4 so that its linked to the same show.

 

this is the query i've used so far.

 

$query="SELECT anime.*, ani_character.* FROM anime, ani_character WHERE ani_character.a_chid=anime.a_id";

 

$result= mysql_query($query)or die (mysql_error());

 

$row = mysql_fetch_array($result);

 

then i use the echo command to display the needed information

 

echo $row['ac_name'] (Character name)

 

but i've come across a problem. the query only puts the first character row into the array so i can get the first character information but not the others. Is there anyway to fix it and how would i display them with the echo command.

 

thanks

[soullessj]

can anyone help me with this

[fugix]

to combine select statements, use a union, for correct syntax of the union clause refer to this

[soullessj]

here are images of the two tables i've made so far.

I have tried a bit with the union command but i get the following error

 

The used SELECT statements have a different number of columns

 

here is the query

 

<?php
$query="SELECT anime.*, ani_character.* FROM anime, ani_character WHERE ani_character.a_chid=anime.a_id
UNION
SELECT ani_character.id FROM ani_character WHERE ani_character.id=2";

$result= mysql_query($query)or die (mysql_error());

$row = mysql_fetch_array($result);

?>

 

can you please show me how to tell it to put the character info into seperate array's so i can display them with the show info or how to put all the character info into one array so that i can put them into the page with the show info.

 

thanks

 

[attachment deleted by admin]

[soullessj]

i put a_chid= 2 hoping that it would show the second row with the same id (bgc)

 

[soullessj]

sorry i tried that and then tried putting id=2 but get the same error saying that there are to many columns

[fugix]

a union takes the results of two queries and combines them vertically, what I think you are looking to do here is JOIN, for correct syntax and examples look here

[soullessj]

thanks i will check it out but is there anyway you can show me the actual query or what it would look like. Once i know what its suppost to be like i can work from there.

 

Thanks

[fugix]

thanks i will check it out but is there anyway you can show me the actual query or what it would look like. Once i know what its suppost to be like i can work from there.

 

Thanks

there are many examples on the link that i gave you, simply replace there column_names and table_name with yours

[soullessj]

i'll try it out thanks

[soullessj]

i couldn't get it working.

i managed to create another query after the first and to load the data into another array, but i know from reading that, that can cause problems if to many querys are running.

[soullessj]

can anyone help me out with this. especially if you can show me what the actual query is. I know i should figure it out myself but if someone can show it to me i'll be able to figure out how it works and see where i went wrong.

 

thanks

[gizmola]

The number of columns in each result set that you are unioning must be the same

 

[soullessj]

so then how do i get it to retrieve the data from the first table and the other 4 rows of data that have the same id linking them (bgc). If i put a query to retrieve info with the same id (a_id and a_chid) i only get the first row from the second table (ani_character). I have managed to get the info by repeating the query multiple times and storing in a different array, but i want to get all the info in one query and be able to display it.

[gizmola]

I don't really know what your table structures look like so it's hard to advise you.

[soullessj]

if you check the previous page there is a zip file of the two tables with info in them.

anime is table 1

and ani_character is table 2

 

thanks