[SOLVED] Help with pulling data from 2 mysql tables?

[ragu78]

Hi guys,

 

I am new to coding and all  .. I'm slowly learning and am trying to pull data from two different tables to produce one outcome. I have two separate tables one called "teams" and the other called "userteams".

 

In the "teams" table I have the following:

 

team_id

name

mail

 

In the" userteams" I have the following:

 

uid (user id for my website)

team_id

 

Now what I'm trying to do is call the persons team ID from the userteams table by there cookie when there logged in, like so:

 

$getteamid = $db->sql_query("SELECT team_id FROM ".$prefix."_ WHERE uid='$cookie[0]'");

$teamid = mysql_num_rows($getteamid);

 

Now that works fine and I end up with the number 1 which is that persons team_id number. My problems start when I try to use the persons team_id number to pull the persons team name from the other table called "teams" like so:

 

$getteamname = $db->sql_query("SELECT name FROM ".$prefix."_teams

WHERE team_id='$teamid'");

$teamname = mysql_fetch_array($getteamname);

 

I also tried:

 

$getteamname = $db->sql_query("SELECT name FROM ".$prefix."_teams

WHERE team_id='$getteamid'");

$teamname = mysql_fetch_array($getteamname);

 

Is this not the correct way to do it? Cause when I use "$teamname" on my page it does not show that persons team name. Any help would be greatly appreciated.

 

Thx guys

[fenway]

You likely want a JOIN.

[ragu78]

Thx for the reply it is appreciated ...

 

I dont quite understand what you mean? Im a ober noob when it comes to coding and so I have no clue on how to join. Could you possible give me a example that I could go with if you have time. Thx again.

[fenway]

You want the team name?

 

select t.teamname
from teams as t
inner join userteams as u on ( u.team_id = t.team_id )
where uid=<the-uid-you-want>

[ragu78]

Yes I'm wanting the team name (row "name") and team email (row "mail"). So adding $cookie[0] were <the-uid-you-want> would give me the results I'm looking for? How does it know what database database and tables Im calling it from? or is that just a code I'm suppose to be adding to my existing sql query:

 

$getteamid = $db->sql_query("SELECT team_id FROM ".$prefix."_ WHERE uid='$cookie[0]'");

$teamid = mysql_num_rows($getteamid);

 

 

thanks, your time helping me with this is greatly appreciated

[fenway]

I just gave you the query....

[ragu78]

I just gave you the query....

 

Ya, I'm just confused on how it knows what database tables ("_userteams" & "_teams") to pull the "team_id", "uid", "mail", and "name" rows from. Please try not to get frustrated with me, Like I said I'm very new to this and I'm still trying to learn. Thx again for all your help and time

[fenway]

Use this as your query:

"select t.teamname, t.mail
from ".$prefix."_teams as t
inner join ".$prefix."_userteams as u on ( u.team_id = t.team_id )
where uid='$cookie[0]'"

[ragu78]

Ok that didnt work, only because I dont think I am explaining this well since I'm pretty much a idiot at this stuff.

 

Heres what I trying to do ....

 

Im trying to make a simple join form (<-thats already done)  for a tournament, the person creates a team (<- thats already done) and his info is inserted into 2 diferent tables:

 

Table 1: _teams

Rows:

team_id

name (<-teams name)

mail (<- teams email)

 

Table 2: _userteams (<- holds the users for the team that is created into table 1)

Rows:

uid (<- my websites userid asigned to them when they register to my site)

team_id (<- holds the same team id for the team created into table 1)

name (<- users website name they used when registering to my site)

 

Now here is the code to my join form that opens inside my whole tournament modules index:

 

//-----------------------------
//-------- Signup--------------
//-----------------------------
if(($tournament) AND ($cmd == "signup")){

$getpnum = $db->sql_query("SELECT * FROM ".$prefix."_tourney2 WHERE tid='$tournament'");
$pnum = mysql_num_rows($getpnum);

$getteam = $db->sql_query("select t.teamname from ".$prefix."_teams as t
inner join ".$prefix."_userteams as u on ( u.team_id = t.team_id )
where uid='$cookie[0]'");
$gettheteam = @mysql_fetch_array($getteam);

if(!$cookie[0]){
OpenTable();
echo "<CENTER><B>"._REGISTERLOGIN."</B></CENTER>";
CloseTable();
CloseTable();
include("footer.php");
die;}


$display = "<TR><TD ALIGN=center COLSPAN=100% CLASS='$tourneysettings[signupbg]'><br><hr><br><br><br>
"._ENTERNAME."<BR>
<INPUT TYPE=text NAME='teamname' VALUE='$getteamname'><BR><BR></TD></TR>";



echo "<TABLE ALIGN=center WIDTH=90%>
<FORM ACTION='modules.php?name=&tournament=$tournament' METHOD=post>
<TR><TD ALIGN=center COLSPAN=100%><HR></TD></TR>
<TR>
<TH ALIGN=center COLSPAN=100%><FONT CLASS=mp-title>"._SIGNUP." : $tourney[name]</FONT></TH>
</TR>
<TR><TD ALIGN=center COLSPAN=100%><HR></TD></TR>
$display
<TR><TD ALIGN=center COLSPAN=100%><HR></TD></TR>
<INPUT TYPE=hidden NAME='cmd' VALUE='signup2'>
</FORM></TABLE>";}

 

Now the problem I'm having is pulling the persons team name for the "value" of the teams name input on the join form.

$gettheteam = $db->sql_query("select t.teamname from ".$prefix."_teams as t
inner join ".$prefix."_userteams as u on ( u.team_id = t.team_id )
where uid='$cookie[0]'");

 

The value I currently have is "$gettheteam" which is calling the sql query up at the top of the form.

"._ENTERNAME."<BR>
<INPUT TYPE=text NAME='teamname' VALUE='$gettheteam'><BR>

But it is not displaying anything at all, The input box is blank.

 

So a quick run down of what Im trying to do is diplay the persons team name in the input box by pulling the information from the logged in persons cookie. I hope this explains it better

 

thanks again for your time.

 

[fenway]

You don't need to keep explaining it to me.

 

You're issuing a query, storing the result set in $gettheteam... and then you're assigning the fetch_assoc() to itself (and assuming that only a single one will come back).  But you're not pulling the field out of this hash.

[ragu78]

ok then I guess I need to know how to pull the field out of the hash? I get the feeling I'm bothering you. If so then I am sry, I thought this site was here to help people like me that dont know much about mysql and php. If so then just let me know and I will look for help else where. I have no clue what pulling a field out of a hash is or what a fetch_assoc is, but I trying my best to learn this stuff.

 

thanks for your time.

[fenway]

Try using $gettheteam['cityname'].

 

You're not bothering me, it's just synatx.

[ragu78]

That work !! Thank you so much for all the help and sticking with me threw this, GREATLY APPRECIATED. I now understand what you meant.