Page 1 of 1

php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 5:31 pm
by flemingmike
$account=mysql_query("SELECT teamid FROM membersteams WHERE memberid=' ******I need this to equal the person who is logged in's users id. the user id is located in members table 'members' and it is field 'ID'******'");
while(list($teamid)=mysql_fetch_row($account)){
}


and this has to pull info from another table when the teamid from $account is equal to ID from table 'teams'.

$team=mysql_query("SELECT *,DATE_FORMAT(joined,'%m/%d/%Y') as \"Joined\" FROM teams WHERE id='$account'");
$team=mysql_fetch_array($team);


thanks alot.

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 5:35 pm
by William
flemingmike wrote:$account=mysql_query("SELECT teamid FROM membersteams WHERE memberid=' ******I need this to equal the person who is logged in's users id. the user id is located in members table 'members' and it is field 'ID'******'");
while(list($teamid)=mysql_fetch_row($account)){
}


and this has to pull info from another table when the teamid from $account is equal to ID from table 'teams'.

$team=mysql_query("SELECT *,DATE_FORMAT(joined,'%m/%d/%Y') as \"Joined\" FROM teams WHERE id='$account'");
$team=mysql_fetch_array($team);


thanks alot.

Code: Select all

SELECT t.*, DATE_FORMAT(JOIN, '%m/%d/%y') Joined FROM membersteams m JOIN teams t ON t.id=m.team_id WHERE m.memberid=5125912
Something like that maybe? I'm not 100% sure I understand what you're asking for.

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 5:38 pm
by flemingmike
no, the $team part is working. i need the $account part to be changed around to read from 2 different tables

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 5:41 pm
by flemingmike
maybe using {$_COOKIE['user']} ?? does that make sence? how can i find out what the user id cookie would be?

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 5:44 pm
by William
flemingmike wrote:maybe using {$_COOKIE['user']} ?? does that make sence? how can i find out what the user id cookie would be?
Try print_r(array($_COOKIES, $_SESSION)) http://www.php.net/print_r

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 6:03 pm
by flemingmike
hmm.. i dont know... im trying to change an existing code i have:

this code works when i goto: test.php?account=6

Code: Select all

<?
 
 
include("./includes/incglobal.php");
 
if(!$account){
error("<font color='red'>Invalid Link</font>");
}
 
$team=mysql_query("SELECT *,DATE_FORMAT(joined,'%m/%d/%Y') as \"Joined\" FROM teams WHERE id='$account'");
$team=mysql_fetch_array($team);
 
 
if(!$team[id]){
error("<font color='red'>Invalid ID</font>");
}
 
$query4 = "SELECT * FROM ladder_$team[ladderid] WHERE teamid='$team[id]'" or die("Error in query4"); 
$result4 = mysql_query($query4) or die("Error in result4");
while($row = mysql_fetch_array($result4)) {
 
}
 
$stats=mysql_query("SELECT *,DATE_FORMAT(lastmatch,'%M %d') as \"LastMatchDate\" FROM ladder_$team[ladderid] WHERE teamid='$team[id]'");
$stats=mysql_fetch_array($stats);
 
$standings=mysql_query("SELECT name,rank,lrank FROM ladder_$team[ladderid] WHERE teamid='$team[id]'");
$standings=mysql_fetch_array($standings);
 
$ladder=mysql_query("SELECT name,isteam FROM ladders WHERE id='$team[ladderid]'");
$ladder=mysql_fetch_array($ladder);
 
 
 
$out[body].="";
 
 
$out[body].="
<tr>
  <td width='100%'><img src='./images/stats/spacer.gif' border='0' /><br /><table width='94%' border='0' cellspacing='1' cellpadding='2' bgcolor='#000000'>
<tr bgcolor='$config[altcolora]'>
 
</tr>
</table></td>
</tr>
</table></td></tr></table>
<br />
<div align='center'>";
 
 
 
$out[body].="";
 
$members=mysql_query("SELECT memberid,position,joined,DATE_FORMAT(joined,'%M %d, %Y') FROM membersteams WHERE teamid='$team[id]' ORDER BY position");
while(list($memberid,$position,$joined,$date)=mysql_fetch_row($members)){
$mem=mysql_query("SELECT * FROM members WHERE id='$memberid'");
$mem=mysql_fetch_array($mem);
 
 
}
 
 
 
 
 
 
 
$out[body].="
</table><br>
<script type='text/javascript'>
  <!--
    function Challenge( id, login ) {
      window.open('./scheduler.php?chall_id='+id + 'login[id2]='+login, '_details', 'width=600,height=500,toolbar=0,statusbar=0,scrollbars=0,menubar=0');
    }
  //-->
  </script>
<table width='95%' border='0' cellspacing='1' cellpadding='2' bgcolor='#000000'>
<tr bgcolor='$config[altcolora]'>
<td width='100%' valign='center'  background='$config[bg]' align='left' colspan='5'><b>  Challenges</b></td>
</tr>
<tr bgcolor='$config[altcolorb]'>
<td width='5%' valign='center'  background='$config[bg2]' align='center'><b>ID</b></td>
<td width='5%' valign='center'  background='$config[bg2]' align='center'><b></b></td>
<td width='30%' valign='center' background='$config[bg2]' align='center'><b>Challenger</b></td>
<td width='30%' valign='center'  background='$config[bg2]' align='center'><b>Challenged</b></td>
<td width='30%' valign='center'  background='$config[bg2]' align='center'><b>Date</b></td>
</tr>";
 
$totalchall=mysql_query("SELECT COUNT(*) FROM challenges WHERE challgrid='$team[id]' OR challgdid='$team[id]'");
$totalchall=mysql_fetch_array($totalchall);
$totalchall="$totalchall[0]";
 
if($totalchall == 0){
$out[body].="<tr bgcolor='$config[altcolora]'>
<td width='100%' valign='center' align='center' colspan='5' background='$config[cellbg]'>No outstandings challenges.</td></tr>";
}else{
$challenges=mysql_query("SELECT challid,challgrid,challgdid,challgrname,challgdname,DATE_FORMAT(created,'%M %d at %l:%i %p') FROM challenges WHERE challgrid='$team[id]' OR challgdid='$team[id]' ORDER BY created DESC");
while(list($challid,$challgrid,$challgdid,$challgrname,$challgdname,$created)=mysql_fetch_row($challenges)){
 
$check=mysql_query("SELECT * FROM chall_chat WHERE chall_id='$challid'");
$check=mysql_fetch_array($check);
 
if ($check[ed_status]==Accepted OR $check[er_status]==Accepted){
$msg = "<span style=\'color:red\'><center><strong>Both Teams have Accepted the Match Date!</strong></center></span><br />&nbsp;<span style=\'color:black;font-weight:bold;\'>Match Details:</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Date:</strong> $check[month] $check[day], $check[year]<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Time:</strong> $check[hour]:$check[min] $check[ampm] $check[zone] <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Server:</strong> $check[server]<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Game Name:</strong> $check[game]";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', HEIGHT, 60, WIDTH, 260, CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar2.gif' border='0'></a>";
}elseif ($check[ed_status]==Declined OR $check[er_status]==Declined){
$msg = "<center>The Challenge has been Declined.</center>";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar3.gif' border='0'></a>";
}elseif ($check[ed_status]==Pending OR $check[er_status]==Pending){
$msg = "<center>This Challenge is Pending Approval.</center>";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar.gif' border='0'></a>";
}else{
$msg = "<center>No Suggestions have been made.</center>";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar1.gif' border='0'></a>"; }
 
if($config[altcolorx]==$config[altcolora]){
$config[altcolorx]="$config[altcolorb]";
}else{
$config[altcolorx]="$config[altcolora]";
}
if($config[cellbgx]==$config[cellbg]){$config[cellbgx]="$config[cellbg2]";}else{$config[cellbgx]="$config[cellbg]";}
 
$out[body].="
<tr bgcolor='$config[altcolorx]'>
<td width='5%' valign='center' align='center' background='$config[cellbgx]'>$challid</td>
<td width='5%' valign='center' align='center' background='$config[cellbgx]'>$calendarimg</td>
<td width='30%' valign='center' align='center' background='$config[cellbgx]'><a href='$config[scripturl]/stats.php?account=$challgrid'>$challgrname ($challgrid)</a></td>
<td width='30%' valign='center' align='center' background='$config[cellbgx]'><a href='$config[scripturl]/stats.php?account=$challgdid'> $challgdname ($challgdid)</a></td>
<td width='30%' valign='center' align='center' background='$config[cellbgx]'> $created</td>
</tr>";
}
}
 
 
 
$out[body].="
</table>
<br />
</div>";
 
include("$config[html]");
?>
currently that pulls up a teamid from the table 'teams' in the field 'id' and it knows what team id to poll based on the url. i need it to pull up all the users teams (the user id's are located in the 'members' table in the field 'id') because they can be apart of more than one team. so if i can get it to find all teams with the the same userid (which the references can be found in the table 'membersteams' team id is in field 'teamid' and members id is in the field 'memberid')

so if i can get it to look in the 'members' table and pull all records with the currently logged in users id, then look in the 'membersteams' table and pull all teamid that the membersid is apart of.

here is my new code that doesnt work. says 'no outstanding challenges'.

Code: Select all

<?
 
 
include("./includes/incglobal.php");
 
 
$account=mysql_query("SELECT teamid FROM membersteams WHERE memberid='{$_COOKIE['user']}'");
while(list($teamid)=mysql_fetch_row($account)){
}
 
$team=mysql_query("SELECT *,DATE_FORMAT(joined,'%m/%d/%Y') as \"Joined\" FROM teams WHERE id='$account'");
$team=mysql_fetch_array($team);
 
 
$ladder=mysql_query("SELECT name,isteam FROM ladders WHERE id='$team[ladderid]'");
$ladder=mysql_fetch_array($ladder);
 
 
 
$out[body].="";
 
 
$out[body].="
<tr>
  <td width='100%'><img src='./images/stats/spacer.gif' border='0' /><br /><table width='94%' border='0' cellspacing='1' cellpadding='2' bgcolor='#000000'>
<tr bgcolor='$config[altcolora]'>
 
</tr>
</table></td>
</tr>
</table></td></tr></table>
<br />
<div align='center'>";
 
 
 
$out[body].="";
 
$members=mysql_query("SELECT memberid,position,joined,DATE_FORMAT(joined,'%M %d, %Y') FROM membersteams WHERE teamid='$team[id]' ORDER BY position");
while(list($memberid,$position,$joined,$date)=mysql_fetch_row($members)){
$mem=mysql_query("SELECT * FROM members WHERE id='$memberid'");
$mem=mysql_fetch_array($mem);
 
 
}
 
 
 
 
 
 
 
$out[body].="
</table><br>
<script type='text/javascript'>
  <!--
    function Challenge( id, login ) {
      window.open('./scheduler.php?chall_id='+id + 'login[id2]='+login, '_details', 'width=600,height=500,toolbar=0,statusbar=0,scrollbars=0,menubar=0');
    }
  //-->
  </script>
<table width='95%' border='0' cellspacing='1' cellpadding='2' bgcolor='#000000'>
<tr bgcolor='$config[altcolora]'>
<td width='100%' valign='center'  background='$config[bg]' align='left' colspan='5'><b>  Challenges</b></td>
</tr>
<tr bgcolor='$config[altcolorb]'>
<td width='5%' valign='center'  background='$config[bg2]' align='center'><b>ID</b></td>
<td width='5%' valign='center'  background='$config[bg2]' align='center'><b></b></td>
<td width='30%' valign='center' background='$config[bg2]' align='center'><b>Challenger</b></td>
<td width='30%' valign='center'  background='$config[bg2]' align='center'><b>Challenged</b></td>
<td width='30%' valign='center'  background='$config[bg2]' align='center'><b>Date</b></td>
</tr>";
 
$totalchall=mysql_query("SELECT COUNT(*) FROM challenges WHERE challgrid='$team[id]' OR challgdid='$team[id]'");
$totalchall=mysql_fetch_array($totalchall);
$totalchall="$totalchall[0]";
 
if($totalchall == 0){
$out[body].="<tr bgcolor='$config[altcolora]'>
<td width='100%' valign='center' align='center' colspan='5' background='$config[cellbg]'>No outstandings challenges.</td></tr>";
}else{
$challenges=mysql_query("SELECT challid,challgrid,challgdid,challgrname,challgdname,DATE_FORMAT(created,'%M %d at %l:%i %p') FROM challenges WHERE challgrid='$team[id]' OR challgdid='$team[id]' ORDER BY created DESC");
while(list($challid,$challgrid,$challgdid,$challgrname,$challgdname,$created)=mysql_fetch_row($challenges)){
 
$check=mysql_query("SELECT * FROM chall_chat WHERE chall_id='$challid'");
$check=mysql_fetch_array($check);
 
if ($check[ed_status]==Accepted OR $check[er_status]==Accepted){
$msg = "<span style=\'color:red\'><center><strong>Both Teams have Accepted the Match Date!</strong></center></span><br />&nbsp;<span style=\'color:black;font-weight:bold;\'>Match Details:</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Date:</strong> $check[month] $check[day], $check[year]<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Time:</strong> $check[hour]:$check[min] $check[ampm] $check[zone] <br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Server:</strong> $check[server]<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Game Name:</strong> $check[game]";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', HEIGHT, 60, WIDTH, 260, CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar2.gif' border='0'></a>";
}elseif ($check[ed_status]==Declined OR $check[er_status]==Declined){
$msg = "<center>The Challenge has been Declined.</center>";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar3.gif' border='0'></a>";
}elseif ($check[ed_status]==Pending OR $check[er_status]==Pending){
$msg = "<center>This Challenge is Pending Approval.</center>";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar.gif' border='0'></a>";
}else{
$msg = "<center>No Suggestions have been made.</center>";
$hover = "<a href='javascript&#058; void Challenge($challid,$team[id])' onmouseover=\"return overlib('$msg', CAPTION, '<span style=\'color:blue;\'>Current Challenge Status</span>');\" onmouseout=\"return nd();\">";
$calendarimg = "$hover<img src='./images/calendar1.gif' border='0'></a>"; }
 
if($config[altcolorx]==$config[altcolora]){
$config[altcolorx]="$config[altcolorb]";
}else{
$config[altcolorx]="$config[altcolora]";
}
if($config[cellbgx]==$config[cellbg]){$config[cellbgx]="$config[cellbg2]";}else{$config[cellbgx]="$config[cellbg]";}
 
$out[body].="
<tr bgcolor='$config[altcolorx]'>
<td width='5%' valign='center' align='center' background='$config[cellbgx]'>$challid</td>
<td width='5%' valign='center' align='center' background='$config[cellbgx]'>$calendarimg</td>
<td width='30%' valign='center' align='center' background='$config[cellbgx]'><a href='$config[scripturl]/stats.php?account=$challgrid'>$challgrname ($challgrid)</a></td>
<td width='30%' valign='center' align='center' background='$config[cellbgx]'><a href='$config[scripturl]/stats.php?account=$challgdid'> $challgdname ($challgdid)</a></td>
<td width='30%' valign='center' align='center' background='$config[cellbgx]'> $created</td>
</tr>";
}
}
 
 
 
$out[body].="
</table>
<br />
</div>";
 
include("$config[html]");
?>

hope this is an easy thing im missing....

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 6:10 pm
by William
$account=mysql_query("SELECT teamid FROM membersteams WHERE memberid='{$_COOKIE['user']}'");
while(list($teamid)=mysql_fetch_row($account)){
}

$team=mysql_query("SELECT *,DATE_FORMAT(joined,'%m/%d/%Y') as \"Joined\" FROM teams WHERE id='$account'");
$team=mysql_fetch_array($team);
Can be done by...

Code: Select all

 
$teams = mysql_query("SELECT t.*, DATE_FORMAT(t.joined, '%m/%d/%Y') Joined FROM memberteams m JOIN teams t ON mt.team_id=t.id WHERE mt.member_id={$_COOKIE['user']}");
 
The query above "should" return all the teams associated with that user. I'd like to point out that the above query is vulnerable to SQL injection ($_COOKIE can't be trusted).

Edit: It looks like you don't fully understand what you're trying to do. You might want to read a few PHP & MySQL tutorials to get a better understanding to help accomplish what you're trying to do.

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 6:18 pm
by flemingmike
still comes up with no current challenges.


and your right, im not too great with php. i hired a guy to help me, and he completed everything but this. and i just need a little help. unless i scrap this feature all together...

Re: php-sql help - thanks in advance

Posted: Wed Mar 18, 2009 6:26 pm
by DevGiven
The problem is in your incglobal.php from where the $account variable is triggered.

This is a solution: If you use sessions in your login you can make a query like this(for getting memberid)
$q = "SELECT memberid FROM member WHERE Email='".$_SESSION["email"]."' and Password='".$_SESSION["password"]."'";

Hope this helps.