3 Replies - 1211 Views - Last Post: 25 July 2011 - 02:04 PM Rate Topic: -----

#1 Rivve   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-July 11

mySQL fetch data from 2 tables

Posted 25 July 2011 - 09:40 AM

Hey,

I've stumbled upon a problem here and since im not very good at mySQL im kinda hoping you guys could help me out.

I've got 2 tables and the intention is to fetch data from Users to tell which Family they are in since im currently creating a Textbased Mafia game, and the other table contains the family information including name,owner,startdate etc.
But since i dont know how to pull the "Family" from Users which tells the name of the current family if a the user is in the family.
And then take the name from users and take the data equal to that name from the other table named Family and show it.
So what i've gotten around to so far is.


<? 
// Pull information from Databases for families. 
	$sql = "SELECT * from users where `id=` '".mysql_real_escape_string($_SESSION['user_id'])."'
UNION ALL
SELECT * from family where `name` = '.$family'";
	$query = mysql_query($sql) or die(mysql_error());
	$row = mysql_fetch_object($query);
	$family = htmlspecialchars($row->family);
	$history = htmlspecialchars($row->description);
	$owner = htmlspecialchars($row->owner);
	$founded = htmlspecialchars($row->started);
	$type = htmlspecialchars($row->type);
	$avatarc = htmlspecialchars($row->avatarc);
	$familyrank = htmlspecialchars($row->rank);
?>
<? echo $owner; //See if Database works - Obviously dont ?>


Im guessing there is a smoother solution than trying to get UNION to work, if anyone could just point me into the right direction and i'd would be grateful just tell me the syntax to use and ill figure out how to use it.
Or if you could link to a tutorial to do it would be great.

Thanks in advance!

Is This A Good Question/Topic? 0
  • +

Replies To: mySQL fetch data from 2 tables

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14339
  • View blog
  • Posts: 57,476
  • Joined: 12-June 08

Re: mySQL fetch data from 2 tables

Posted 25 July 2011 - 09:45 AM

It sounds like you need to be introduced to my good friend 'JOIN'.

Basically a join is a set of specific ways to take a key from tableA, match it up with the same value in tableB and retrieve data from both tableA and tableB.

In your case it sounds like you want to return a user information from tableA, and you have the unique family key for that user, right? That unique key translates into the primary key for the family table? You then join on that key column!

http://www.w3schools...ql/sql_join.asp
http://www.codinghor...-sql-joins.html
Was This Post Helpful? 1
  • +
  • -

#3 Dormilich   User is online

  • 痛覚残留
  • member icon

Reputation: 4213
  • View blog
  • Posts: 13,312
  • Joined: 08-June 10

Re: mySQL fetch data from 2 tables

Posted 25 July 2011 - 09:48 AM

Id try a JOIN.
SELECT 
    family.family, 
    family.description,
    family.owner,
    family.started,
    family.type,
    family.avatarc,
    family.rank
FROM 
    family
INNER JOIN
    users ON users.family = family.name -- this is somewhat of a guess here
WHERE
    users.id = ?

Was This Post Helpful? 0
  • +
  • -

#4 Rivve   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 25-July 11

Re: mySQL fetch data from 2 tables

Posted 25 July 2011 - 02:04 PM

Thanks for linking the JOIN tutorial kinda =)
Fixed it!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1