Record Calling Problem

Sapper

Hi, is there a way of solving this problem.

ATM a teamID has been passed from an asp page to a php page which works fine but because its passing over a teamid obviously that is only showing the info for the one team.

I need the info to be shown for all teams in the same league as the teamid that was passed from the asp page.

If there is an easy way to do this can someone please explain in a simple manner as I am new to php and the person who was helping me has done a vanishing act in the middle of the project.

Many thanks....

<?php
$conn = mysql_connect("localhost", "username", "password")
or die("Could not connect to mysql.");
$selected = mysql_select_db("football", $conn);
$sql = "select * from teamname WHERE ID = '$_POST[TeamID]'";
$result = mysql_query($sql);

$num = mysql_num_rows($result);
$cur = 1;

echo "<ol>";

while ($num >= $cur) {

$row = mysql_fetch_array($result);

$teamname = $row["TeamName"];
$played = $row["Played"];
$gf = $row["GF"];
$ga = $row["GA"];
$pts = $row["Points"];
$League = $row["League"];
?>
<?php echo "Current League is: $League" ?>
<tr bgcolor="#FFFFFF" bordercolor="#000000">
<td><?php echo "$teamname" ?></td>
<td width="10%" align="center"><?php echo "$played" ?> </td>
<td width="10%" align="center"><?php echo "$gf" ?></td>
<td width="10%" align="center"><?php echo "$ga" ?></td>
<td width="10%" align="center"><?php echo "$pts" ?></td>
</tr>
<?php

$cur++;
}

echo "</ol>";

theeper

I am assuming there is a field in the table that stores a leagueID which denotes which league each team is. If this is done within the same table, it should be easy to change your SQL query to do what you need.

Basically, you would need to pass the lleagueID variable to the script and do rewrite the query to look something like this...

$sql = "select * from teamname WHERE leagueID = '$_POST[leagueID]'";

Sapper

Thanks for your reply.

Unfortunately the leagueid is stored in a different table and whilst the teamid is in both tables the leagueid isnt.

theeper

You will need to do a join in your SQL, can you post the table structures ?

Sapper

The tables are massive so I will just post what i think is relevant if you need more let me know and i can send you login details if its easier then you will see what im trying to do.

The first table is called players and the field is called TeamID at the moment this is sent to the php page using the $_POST method

The second table is called TeamName and this also holds the teamid number but the field is labelled just ID also in this table is the League field.

A bit vague but if you need more let me know and I will give you access...

Thanks

theeper

Roughly, your query would need to look something like this ...

SELECT * FROM teams,players WHERE players.teamID=teams.ID && teams.leagueID = '$_POST[leagueID]

Can you post just the table schemas ?

Sapper

Hope this is what you meant lol.............

TeamName table

ID int(11)

TeamName varchar(50)

AICounter int(11)

League varchar(12)
Played int(11)

GF int(11)

GA int(11)

Points int(11)

Players Table

Name varchar(50)

Nationality varchar(50)
Age int(11)

Caps int(11)

Fitness int(11)

Position varchar(50)

Skill int(11)

Value double

TeamID int(11)

PlayerID int(11)

IsPlaying tinyint(1)

DefaultTeam tinyint(1)

IsPlayingPosition varchar(50)

DefaultPosition varchar(50)

DefaultFormation varchar(50)

TeamRating int(11)
UserName varchar(50)

Strip varchar(20)

TeamName varchar(50)

Listed tinyint(1)

Cards double

Injuries int(2)

Here is the ASP Page that posts the strTeamID

<%Response.Buffer=True%>

<% check_security(4) %>
<%
dim privacy_section
dim features

do_search = request("do_search")
':: handle the default case(s) for each form (separate from action= parameter)

<%
Session.LCID = 2057
Session.Timeout = 60

strUserID = session("user_id")
Dim strUserName
Dim strTeamUser
Dim TeamID
Dim strRemoteIP


Dim objConn
Set objConn = Server.CreateObject ("ADODB.Connection")
DbPath = Server.MapPath("./Data/7223.mdb")
obJConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & DbPath & ";"


Dim RsUserName
Dim SqlRsUserName

SqlRsUserName = "Select * From Users Where user_id =" & strUserID

Set RsUserName = Server.CreateObject ("ADODB.RecordSet")
RsUserName.Open SqlRsUserName, objConn, adOpenDynamic, adLockOptimistic

While Not RsUserName.EOF
	strUserName = RsUserName("user_name")
	RsUserName.MoveNext
Wend


RsUserName.Close
Set RsUserName = Nothing
objConn.Close
Set objConn = Nothing


strTeamUser = "'" & strUserName & "'"


Dim objPlayerConn
Set objPlayerConn = Server.CreateObject ("ADODB.Connection")
obJPlayerConn.Open "driver={mysql}; database=football; server=localhost; uid=username; pwd=password; option=16386;"

' Dim objPlayerConn
' Set objPlayerConn = Server.CreateObject ("ADODB.Connection")
' DbPath = Server.MapPath("Football.mdb")
' obJPlayerConn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & DbPath & ";"

Dim RsTeamID
Dim SqlTeamID
SqlTeamID = "Select * From Players Where UserName =" & strTeamUser

Set RsTeamID = Server.CreateObject ("ADODB.RecordSet")
RsTeamID.Open SqlTeamID, objPlayerConn, adOpenDynamic, adLockOptimistic

If Not RsTeamID.EOF Then
	TeamID = RsTeamID("TeamID")
	Session("TeamID") = TeamID
	Session("TeamName") = RsTeamID("TeamName")
Else
	Response.Redirect "NewUser.asp"
End If

RsTeamID.Close
Set RsTeamID = Nothing

' Dim RsDeticker
' Dim SqlRsDeticker
' Set RsDeticker = Server.CreateObject ("ADODB.RecordSet")
' SqlRsDeticker = "Select * From Players Where DefaultTeam = False And ' TeamID =" & TeamID
' RsDeticker.Open SqlRsDeticker, objPlayerConn, adOpenDynamic, adLockOptimistic

' While Not RsDeticker.EOF
' RsDeticker("IsPlaying").Value = 0
' RsDeticker.Update
' RsDeticker.MoveNext
' Wend

' RsDeticker.Close
' Set RsDeticker = Nothing

' Dim RsDefTeamPicker
' Dim SqlRsDefTeamPicker

' Set RsDefTeamPicker = Server.CreateObject("ADODB.RecordSet")
' SqlRsDefTeamPicker = "Select * From Players Where DefaultTeam = True And TeamID =" & TeamID
' RsDefTeamPicker.CursorLocation = adUseClient
' RsDefTeamPicker.Open SqlRsDefTeamPicker, objPlayerConn, adOpenDynamic, adLockOptimistic

' While Not RsDefTeamPicker.EOF
' RsDefTeamPicker("IsPlaying").Value = 1
' RsDefTeamPicker.Update
' RsDefTeamPicker.MoveNext
' Wend

' If RsDefTeamPicker.RecordCount = 0 Then

' Dim RsRatingCrusher
' Dim SqlRsRatingCrusher
' Set RsRatingCrusher = Server.CreateObject("ADODB.RecordSet")
' SqlRsRatingCrusher = "Select * From Players Where TeamID =" & TeamID
' RsRatingCrusher.Open SqlRsRatingCrusher, adOpenDynamic, adLockOptimistic

' RsRatingCrusher.MoveFirst
' RsRatingCrusher("TeamRating").Value = 0

' While Not RsRatingCrusher.EOF
' RsRatingCrusher("IsPlaying").Value = 0
' RsRatingCrusher.Update
' RsRatingCrusher.MoveNext
' Wend
' End If

Function RemoteIPLogger()

Session.LCID = 2057
Dim strTime
Dim strHour
Dim strMin
Dim strSec
Dim strDate

strHour = Hour(Now)
strHour = strHour + 8
strMin = Minute(Now)
strSec = Second(Now)
strDate = Date

If strHour > 24 Then
	strHour = strHour - 24
	strDate = strDate + 1
ElseIf strHour = 24 Then
	strHour = "00"
End If

strTime = strHour & ":" & strMin & ":" & strSec

Dim RsRemoteIPLog
Dim SqlRsRemoteIPLog

Set RsRemoteIP = Server.CreateObject("ADODB.RecordSet")
SqlRsRemoteIP = "Select * From IP"
RsRemoteIP.Open SqlRsRemoteIP, objPlayerConn, adOpenDynamic, adLockOptimistic
strRemoteIP = Request.ServerVariables("REMOTE_ADDR")
Session("RemoteIP") = strRemoteIP

RsRemoteIP.AddNew
RsRemoteIP("UserID").Value = strUserID
RsRemoteIP("UserName").Value = strTeamUser
RsRemoteIP("IP").Value = strRemoteIP
RsRemoteIP("Date").Value = strDate
RsRemoteIP("Time").Value = strTime
RsRemoteIP.Update

RsRemoteIP.Close
Set RsRemoteIP = Nothing

End Function

Call RemoteIPLogger()

' RsDefTeamPicker.Close
' Set RsDefTeamPicker = Nothing
' Set RsRatingCrusher = Nothing
objPlayerConn.Close
Set objPlayerConn = Nothing

%>
<link rel="stylesheet" href="default.css">
<script lang="javascript">

</script>
<body topmargin="0" bgcolor="#FFFFFF" onLoad="changeMenu()"><TR>
<TD style="{padding:6px;}"> <%
display_errs
display_msg
%>
<tr>
<td >
<% if request("section") = "privacy" then %><% end if %>
</td>
</tr>

<TABLE style="{width:90%;}">
<TR>
<TD style="{padding:6px;}">
<table cellspacing=1 border=0 bgcolor="#FFFFFF">
<tr>
<td style="{padding:6px;color:#222222}" bgcolor="#FFFFFF"> <% Response.Write "<Center>" & "Welcome to Goalrush " & "" & Session("TeamName") & "" %>
</td>
</tr><% Response.Write "<Center>" & "TeamID is " & "" & Session("TeamID") & "" %>
<tr>
<td colspan=2 align=center>Test Page
For GM's Level 4 access.</td>
</tr>
<tr>
<td colspan=2 align=center>You
are logged in from: <% Response.Write "IP Address: " & Session("RemoteIP") %></td>
</tr></center>
<tr>
<td>
<A href="http://www.aspapp.com"> </a>
</td>
</tr>
</table>

</TD>
</TR>
</TABLE>

<%
':: assure that any db resources are freed
on error resume next
rs.Close
set rs = NOTHING
cn.Close
set cn = NOTHING
user_cn.Close
set user_cn = NOTHING
on error goto 0
%>

And the php page is posted as the first message in this thead.

Sapper

Now I am totally confused.

Your quote

SELECT * FROM teams,players WHERE players.teamID=teams.ID && teams.leagueID = '$_POST[leagueID]

I have very limited knowledge of php but allowing for the mistaken wrong table names I presume the line should read like this:

$sql = "SELECT * FROM teamname,players WHERE players.teamID=teamname.ID && teamname.league = '$_POST[league] '";

But if that is the case then the way i read it is that it is expecting a session [league] to be posted from the asp page but no session for the league has been created on the asp page as I am unsure of how to do it.

theeper

Sorry, I misunderstood you a bit.

This probably could be done with one inner join, but it is simpler to do with two seperate queries.

Make one query to get the LeagueID number of the team for which you passed a variable from the ASP script.

$sql = "SELECT * FROM TeamName WHERE TeamID = '" . $_POST["teamID"] . "';

Use mysql_fetch_array() to get the League value from the row, or just select the League column in the query if you don't need anything else from the row.

Then use the League value in a query on the TeamName table to get all teams with this ID number.

$sql = "SELECT * FROM TeamName WHERE League = '$League'";

Sapper

Thanks for your help I appreciate you taking the time to assist me.

I will try your suggestions and come back to you if there are any problems...............

Sapper

I can't do it Iv'e been trying for four days now and it's time to give up.

Normally I pick things up pretty quick like html and other computer stuff but this is just a nightmare.

Normally there are examples around to look at but I can't find anything that is remotely close to what i want.

Cheers anyway..