Getting data from two tables

[Biggi]

Okay I have the table accounts and the table characters
In accounts I have ID and Username Password and loads more...
In characters there is charactername loads including account.
Now accounts ID and characters account have the same id
So I wanted to know how to get PHP to match the two and desplay the list of charactername.
Please help me im new to php and I'm starting to high up for what I am doing but I need to get this coded.

[StoutFiles]

What kind of database is this (MySQL, Oracle, etc)? Do you know SQL as well? Are you able to get any information out of a database at all? Do you have any code to show of your progress?

[Biggi]

MySQL :)

[Biggi]

 mysql_select_db("test") or die(mysql_error()); 
$data = mysql_query("SELECT accounts.id, accounts.username, characters.account, characters.charactername
FROM accounts, characters
WHERE accounts.id = characters.account
ORDER BY accounts.id
") 
 or die(mysql_error()); 
 Print "<table border cellpadding=3>"; 
 while($info = mysql_fetch_array( $data )) 
 { 
 Print "<tr>"; 
 Print "<th>Character Name:</th> <td>".$info['charactername'] . "</td> "; 
 Print "<th>Account ID:</th> <td>".$info['id'] . "</td> "; 
 Print "<th>Account Name:</th> <td>".$info['username'] . " </td></tr>"; 
 } 
 
 Print "</table>"; 
 ?> 

That seems to kidna work.. I seem to have a slight problem now.. This is what appears:
Character Name:Chad_TomsonAccount ID:1Account Name:Sherman Tank
Character Name:Sherman_TankAccount ID:1Account Name:Sherman Tank
Character Name:Michelle_McAngelAccount ID:1Account Name:Sherman Tank
Character Name:Robin_DeRoseAccount ID:1Account Name:Sherman Tank
Character Name:Ashley_McAngelAccount ID:210147Account Name:Logan Morello
Character Name:Neji_TankAccount ID:210147Account Name:Logan Morello
Character Name:Golden_Dragon_MafiaAccount ID:210147Account Name:Logan Morello


-
Thats what I kinda want.. Just.. I'd rather it say Account Name Sherman Tanks Character Name Sherman Tanks, Robin Derose,ect... But Also I dont want to be able to see other peoples or them to see mine

[StoutFiles]

Just.. I'd rather it say Account Name Sherman Tanks Character Name Sherman Tanks, Robin Derose

So you would like to have...

Account Name | Sherman Tanks | Character Name | Chad_Tomson, Sherman Tanks, Robin Derose, Michelle_McAngel

Account Name | Logan Morello | Character Name | Ashley_McAngel, Neji_Tank, Golden_Dragon_Mafia

I assume that's what you want?

Also I dont want to be able to see other peoples or them to see mine

Not sure what you mean by that...what information from the database do you not want them to see?

[Biggi]

Well You are right on the girst bit thats what id like and I dont want to see Logan Morello's accounts or anyone elses

[StoutFiles]

<?php

$id = "1"; //you need to come into this process with the ID defined.

mysql_select_db("test") or die(mysql_error());

$data = mysql_query("SELECT id, username FROM accounts WHERE id = $id") or die(mysql_error());

while($info = mysql_fetch_array( $data ))
{
$account_name = $info['username'];
}

$data = mysql_query("SELECT account, charactername FROM characters WHERE account = $id") or die(mysql_error());

$count = 0;
while($info = mysql_fetch_array( $data ))
{
$character_name[$count] = $info['charactername'];
//get rid of underscore for display
$character_name[$count] = str_replace("_"," ",$character_name[$count]);
$count++;
}

echo "Account Name: ".$account_name;
echo "<br>";
echo "Character Name(s): ";

for($i=0; $i < $count; $i++)
{
if($i != 0)
{echo ", ";}
echo $character_name[$i];
}

?>

[Biggi]

Nice that works awesomly. Now to make links out of the names of the characters

[Biggi]

Though now when anther person logs in it shows my information :/

[StoutFiles]

$id = "1";

You need to change this variable with their ID. I just set it as 1 as an example. Get their ID, and make $id equal their ID.

[Biggi]

Could you tell me how to do that I am learning a fair bit by doing this :D

[StoutFiles]

You should have logging when they log in. You need a SQL table with usernames, passwords, and unique ID's. Once they login you can store their ID in a session or cookie variable. Look up "php session" and "php cookie" on a search engine to see what I'm talking about.

[Biggi]

I looked at them.. They just dont seem to make much sence to me :/

[Biggi]

I have this all working now thanks, but could you tell me how I could make it so when it shows the:
Account Name | Sherman Tanks | Character Name | Chad_Tomson, Sherman Tanks, Robin Derose, Michelle_McAngel
All the Character names will have a link which I can click and make it shows theres info? (I can do the info bit but I cant do the links :))

if not can you tell me how to make it in to a table with the info I wanted in :)

[coopster]

As you loop through the information you merely wrap the text in anchor elements with your desired links. In HTML it would look something like this:
<a href="/link/to/info/Sherman.php">Sherman Tanks</a>