soullessj Posted June 13, 2011 Share Posted June 13, 2011 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 Quote Link to comment Share on other sites More sharing options...
fugix Posted June 13, 2011 Share Posted June 13, 2011 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 Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 13, 2011 Author Share Posted June 13, 2011 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 Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 13, 2011 Author Share Posted June 13, 2011 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 Quote Link to comment Share on other sites More sharing options...
fugix Posted June 13, 2011 Share Posted June 13, 2011 yes, if you draw out a single row you will get the results that you want. Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 13, 2011 Author Share Posted June 13, 2011 okay thanks for the help i will try it out Quote Link to comment Share on other sites More sharing options...
fugix Posted June 13, 2011 Share Posted June 13, 2011 okay thanks for the help i will try it out okay, let us know if you need further assistance Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 13, 2011 Author Share Posted June 13, 2011 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 Quote Link to comment Share on other sites More sharing options...
fugix Posted June 13, 2011 Share Posted June 13, 2011 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 Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 14, 2011 Author Share Posted June 14, 2011 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 Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 14, 2011 Author Share Posted June 14, 2011 can anyone help me with this Quote Link to comment Share on other sites More sharing options...
fugix Posted June 14, 2011 Share Posted June 14, 2011 to combine select statements, use a union, for correct syntax of the union clause refer to this Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 15, 2011 Author Share Posted June 15, 2011 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] Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 15, 2011 Author Share Posted June 15, 2011 i put a_chid= 2 hoping that it would show the second row with the same id (bgc) Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 15, 2011 Author Share Posted June 15, 2011 sorry i tried that and then tried putting id=2 but get the same error saying that there are to many columns Quote Link to comment Share on other sites More sharing options...
fugix Posted June 15, 2011 Share Posted June 15, 2011 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 Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 15, 2011 Author Share Posted June 15, 2011 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 Quote Link to comment Share on other sites More sharing options...
fugix Posted June 15, 2011 Share Posted June 15, 2011 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 Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 15, 2011 Author Share Posted June 15, 2011 i'll try it out thanks Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 16, 2011 Author Share Posted June 16, 2011 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. Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 16, 2011 Author Share Posted June 16, 2011 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 Quote Link to comment Share on other sites More sharing options...
gizmola Posted June 16, 2011 Share Posted June 16, 2011 The number of columns in each result set that you are unioning must be the same Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 16, 2011 Author Share Posted June 16, 2011 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. Quote Link to comment Share on other sites More sharing options...
gizmola Posted June 16, 2011 Share Posted June 16, 2011 I don't really know what your table structures look like so it's hard to advise you. Quote Link to comment Share on other sites More sharing options...
soullessj Posted June 16, 2011 Author Share Posted June 16, 2011 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.