Welcome to WebmasterWorld Guest from 54.144.77.26

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Getting data from two tables

     

Biggi

7:11 pm on Dec 27, 2011 (gmt 0)



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

8:08 pm on Dec 27, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

8:13 pm on Dec 27, 2011 (gmt 0)



MySQL :)

Biggi

8:24 pm on Dec 27, 2011 (gmt 0)



 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

1:11 pm on Dec 28, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

2:18 pm on Dec 28, 2011 (gmt 0)



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

6:00 pm on Dec 28, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



<?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

6:33 pm on Dec 28, 2011 (gmt 0)



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

Biggi

6:42 pm on Dec 28, 2011 (gmt 0)



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

StoutFiles

6:51 pm on Dec 28, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



$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

7:48 pm on Dec 28, 2011 (gmt 0)



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

StoutFiles

3:23 am on Dec 29, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

1:09 am on Jan 1, 2012 (gmt 0)



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

Biggi

4:07 pm on Mar 15, 2012 (gmt 0)



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

12:42 am on Apr 6, 2012 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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>
 

Featured Threads

Hot Threads This Week

Hot Threads This Month