database and mysql help

[gizmola]

There are a few different syntaxes that work.  This is the typical one for mysql:

 

SELECT ani_character.*, anime.* FROM ani_character ac JOIN anime an ON an.a_id = ac.a_chid AND ac.a_chid = 'bgc'

 

Since your relationship here seems to be that anime has "one to many" ani_character rows, I'm not sure why you are connecting these by some redundant column when they should be connected by keys.  The Primary key for anime, should be stored in ani_character as a foreign key.  Adding this redundant column is just confusing.

[soullessj]

okay what i am trying to do is that in table 1 - anime is the info for the show etc.

in table 2 is the characters from the show.

 

what i am trying to do is store and retrieve the info for the characters along with the info for the show so that i can display them on one page.

 

i have tried putting all the character info into one row but it ends up being to long and i have to repeat the same columns ie. charac1, charac2, desc1, desc2 etc...

 

i thought that if i put them in there own rows and use a column to link them together so that when i queried the database it would retrieve all 4 rows and not just the first one.

 

if this isn't a good or right way to do it could you show me how then.

 

i have managed to get the info from the other rows but i have to repeat the query multiple times on the same page and manually tell it to retrieve info from a specific row

 

thanks

[gizmola]

I just gave you the join query.  If you have 4 characters for 1 show you get 4 rows back -- one for each character, and the columns for the show will be repeated in each row.

[soullessj]

thanks i will try it out. i was just explaining why its like that.

 

so if i use the echo command it will display all the info from all 4 rows or do i have to tell it which row and how

 

thanks

[gizmola]

thanks i will try it out. i was just explaining why its like that.

 

so if i use the echo command it will display all the info from all 4 rows or do i have to tell it which row and how

 

thanks

 

I don't think you understand what I was telling you.  The connection between the two tables does not need to be, nor should it be the a_id and a_chid columns containing the string 'bgc'.  Your primary key for anime (id) is the right way to join the tables.  I would have a column in ani_character named anime_id, and in that column would be a 1 for each character that was in "bubblegum crisis tokyo". 

 

Quite frankly your structure isn't really right here, because the same character is going to appear in many different episodes, so the actual relationship between anime and character is Many to Many.  So really you should have 3 tables to implement what you're trying to accomplish.

 

With that said, you need to read the mysql_query page.  When you query you get a result set.  You then need to fetch the results, so you'lll loop and fetch each row.  I'd recommend using mysql_fetch_assoc

[soullessj]

yeah sorry i have only been playing around with php for a little while and still figuring out how everything works so thanks for the help and i'll play around with that and see how it goes.

 

[soullessj]

ok i have been trying to get information to show from the first table which has the show information. then i have been trying to get the page to show the character information below that on the same page. the show has and id key and the character list has a anime_id key where i use id=anime_id.

 

so if the show id is 1 all the characters in ani_characters anime_id =1.

 

i have managed to one its own get this to show me all the characters in the database with the same anime_id, but when i put it together i only get one character shown (the first one in the list).

 

include('includes/conn.inc.php');
$que = "SELECT * FROM ani_character WHERE anime_id=$id";
// Execute the query
$res = mysql_query( $que );
if (!$res){
die ("Could not query the database: <br />". mysql_error( ));
}
// Fetch and display the result


while ($result_row = mysql_fetch_array(($res))){
echo    '<div id="content_character">';
echo    '<div id="cha_img">' . '<img src=" ' .$result_row[5] . '" width="120"/>' . '</div>';
echo    '<div id="cha_right">' .$result_row[1] . '</div>';
echo    '<hr id="m2"/>';
echo    '<div id="cha_rightvj">' . 'Voice Jap : ' .$result_row[2] . '</div>';
echo    '<div id="cha_rightve">' . 'Voice Eng : ' .$result_row[3] . '</div>';
echo    '</div>';
}

 

i'm thinking that it is still maybe using the query from at the top of the page to display the show information or am i just doing this wrong. It should be showing 3 characters at the moment but like i said it is only showing the first one, but when the code is on its own it shows them all.

 

and the $result_row[] lets me put in the colum names where as if i do it with the show query i have to put a number as shown above for the array made by the query.

 

do i need to add a close connection command and what is it, or as i said am i doing something wrong

 

Thanks for the help

[YoungNate_Black_coder]

// wrong function ...
$row = mysql_fetch_array($result);
// u are asking for a interger with fetch array
// do this instead
$row= mysql_fetch_assoc($result);
// u want to fetch a associtive array! and get all row values assocated with the query condition ...

 

you have to read betwwen the lines when trying to learn a simple langauge like php

u kno assocative array what is it assocaited with 

maybe the query!

Young Nate Dagreat Glad to be of assistance

[soullessj]

i did try that and will try it again. while reading it i was trying to remember if i changed the $row_result[1] in to the column names, so i will try it again and see what happens

 

thanks

[YoungNate_Black_coder]

never had a to many column error did u try to get them with fetch array and then explode them ?

[fugix]

With mysql_fetch_array() you can either use the row integer value, or the associative value. Soulessj, since I see that this problem still has not been solved for you, I will jump in on this one. Can you briefly describe what exactly is going wrong for you now so that I may help

[soullessj]

it is simply this there is the first table with the show information that has an id and id=1 then there is the character table where at the moment there are 3 characters and i've put anime_id as the id to link it to the first table and they all have anime_id=1 what i need it to do is to show the show information which is working. Then underneath that i want to qeuery the character table for all the characters with the coresponding id and then display them.

 

ie..

 

show and details then beneath that

character details.

 

i have tried the above command to query and display the information but whether i use mysql_fetch_array or mysql_fetch_assoc all i get is the first character and it doesn't show the rest.

 

But, if i have the query as shown above on its own in a seperate page it shows me all the information i ask for, but when i try to put them together like i said it only shows one of them not all.

 

thanks

[soullessj]

never had a to many column error did u try to get them with fetch array and then explode them ?

 

no i haven't tried that what does it do exactly as i have seen it but haven't looked into it yet

[soullessj]

i've been checking for any similiar help and i found one.

 

http://www.phpfreaks.com/forums/index.php?topic=336634.0

 

in this one the guy has a similiar problem but im wondering if my problem is that at the end of each command of his there is a \n at the end of each line could that be my problem or is it something else

 

thanks

[fugix]

To use a union, both tables must have the same number if columns. Perhaps you should use a join instead

[soullessj]

ok i don't know why but its working now.

 

I continued with my original query and tried it in the mysql command console and it gave me the reults i wanted so i tried the while command above with only the original query being used.

 

SELECT anime.*, ani_character.* FROM anime, ani_character WHERE anime.id=ani_character.anime_id AND anime.id=1

 

something like that.

 

Now i do still have one problem.

 

it seems to display the info now but it displays from the 2nd row onward, basically it joins the show information to the first row of the character table and displays from the row after that. How can i tell it to include the first row so that it can display it.

 

thanks

[soullessj]

sorry anime.id=$id. i'm using a $_GET variable to get the id number from a variable passed through the url

[soullessj]

so i need it to show the first row to the last with the same id

 

[soullessj]

isn't there a command in php to tell the query to start from the first row of the query and repeat it self until the last row of the query is found and how to use it in a while loop

 

thanks

[gizmola]

so i need it to show the first row to the last with the same id

 

This thread has lost steam at this point. 

 

Speaking generally, if you need just the rows for a certain id, and nothing else, then that would go in your where clause.  Then when you fetch all the rows using mysql_fetch_assoc you'll have them all for display or adding to an array.

 

If you're pulling out more than just the one id, but you want to display them in groups, then you would use ORDER BY id, and you would have to use a variable to detect inside the loop when an id had changed.  Simple to do. 

 

Either way, I think at this point you should try and make a new thread describing what you're trying to do and include any code you have.

[soullessj]

thanks for everyones help. I managed to find out what the problem was and fix it.

 

Basically it was me being an idiot.

i had two $row commands capturing the results.

 

deleted the other and left the one in the while command and it worked perfectly

 

So thanks again