php-sql help - thanks in advance

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
flemingmike
Forum Newbie
Posts: 17
Joined: Wed Mar 18, 2009 5:29 pm

php-sql help - thanks in advance

Post 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.
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: php-sql help - thanks in advance

Post 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.
flemingmike
Forum Newbie
Posts: 17
Joined: Wed Mar 18, 2009 5:29 pm

Re: php-sql help - thanks in advance

Post by flemingmike »

no, the $team part is working. i need the $account part to be changed around to read from 2 different tables
flemingmike
Forum Newbie
Posts: 17
Joined: Wed Mar 18, 2009 5:29 pm

Re: php-sql help - thanks in advance

Post by flemingmike »

maybe using {$_COOKIE['user']} ?? does that make sence? how can i find out what the user id cookie would be?
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: php-sql help - thanks in advance

Post 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
flemingmike
Forum Newbie
Posts: 17
Joined: Wed Mar 18, 2009 5:29 pm

Re: php-sql help - thanks in advance

Post 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....
User avatar
William
Forum Contributor
Posts: 332
Joined: Sat Oct 25, 2003 4:03 am
Location: New York City

Re: php-sql help - thanks in advance

Post 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.
flemingmike
Forum Newbie
Posts: 17
Joined: Wed Mar 18, 2009 5:29 pm

Re: php-sql help - thanks in advance

Post 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...
DevGiven
Forum Newbie
Posts: 7
Joined: Wed Mar 04, 2009 5:41 pm

Re: php-sql help - thanks in advance

Post 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.
Post Reply