small help with a function please?

[Nicholas]

hi im wondering... can anyone help me select a userid from one table and make it select a username from the users table please?

if you understand what i mean...

i cant figure it out... im probably do it completely wrong due to i dont know how to do left joins and etc...

probably $get_info has to be in $get_wins part but differently.

can someone help me please? ill be very greatful.

 

function Number()
{
global $db,$ir,$c,$h,$userid;
echo "<center><table width='50%' cellspacing='1' class=table border='1' bordercolor='#636363'></center>
<tr>
<th width='25%'>Name</th>
<th width='25%'>Amount</th>
</tr>";

$get_wins = sprintf("SELECT `lotteryID`, `userid`, `winnings` FROM lottery_winners");
$do_wins = $db->query($get_wins);
while($win = $db->fetch_row($do_wins))
{
$get_info = sprintf("SELECT u.*,r.* FROM users u LEFT JOIN lottery_winners r ON r.userid=u.userid WHERE r.lotteryID = {$win['lotteryID']}");
$user = $db->query($get_info);
echo "<tr>
<td align='center'>".htmlentites(stripslashes($user['username']))."</td>
<td align='center'>".number_format($win['winnings'])."</td>
</tr>";
}
echo "</table>";
}

[03laceys]

So what you want to do is pull the userid from the raffle table, match the userid with the users table and call the username from the usertable. Is that right?

[Nicholas]

So what you want to do is pull the userid from the raffle table, match the userid with the users table and call the username from the usertable. Is that right?

thats correct. yes. :)

[Joshua]

Simple left join statement

try this

 

Not Tested, but I think this is what you are shooting for

 

function Number()
{
global $db,$ir,$c,$h,$userid;


echo "<center><table width='50%' cellspacing='1' class=table border='1' bordercolor='#636363'></center>
<tr>
<th width='25%'>Name</th>
<th width='25%'>Amount</th>
</tr>";


$get_wins = "SELECT `lotteryID`, `userid`, `winnings` FROM `lottery_winners`");
$do_wins = $db->query($get_wins);

if(!$db->num_rows($do_wins))
{
echo 'There are no winners at this moment';
return;
}

while($win = $db->fetch_row($do_wins))
{
$get_info = "SELECT `u`.`userid`, `u`.`username`, `r`.`winnings` " .
		"FROM `users` `u` " .
		"LEFT JOIN `lottery_winners` `r` " .
		"ON `r`.`userid`=`u`.`userid` " .
		"WHERE `r`.`userid` = ".$win['userid']}");
$user = $db->query($get_info);
echo "<tr>
<td align='center'>".htmlentites(stripslashes($user['username']))."</td>
<td align='center'>".number_format($win['winnings'])."</td>
</tr>";
}
echo "</table>";
}

[Djkanna]

$get_wins = "SELECT `lotteryID`, `userid`, `winnings` FROM `lottery_winners`"); will break.

So will:

$get_info = "SELECT `u`.`userid`, `u`.`username`, `r`.`winnings` " .

"FROM `users` `u` " .

"LEFT JOIN `lottery_winners` `r` " .

"ON `r`.`userid`=`u`.`userid` " .

"WHERE `r`.`userid` = ".$win['userid']}");

[Rasheed saeed]

@Josh:

With the query you used. Is all that other stuff necessary? I mean seriously.. You've already selected what's be needing selected :P

Also..

".$win['userid']}");

TO

".$win['userid']);

[Joshua]

Ok so yea i typed a little fast, I'm used to uploading and it telling me where i made the error lol one sec.

 

function Number()
{
   global $db,$ir,$c,$h,$userid;


   echo "<center><table width='50%' cellspacing='1' class=table border='1' bordercolor='#636363'></center>
   <tr>
   <th width='25%'>Name</th>
   <th width='25%'>Amount</th>
   </tr>";




   $get_wins = "SELECT `lotteryID`, `userid`, `winnings` FROM `lottery_winners`";
   $do_wins = $db->query($get_wins);

if(!$db->num_rows($do_wins))
{
echo 'There are no winners at this moment';
return;
}

while($win = $db->fetch_row($do_wins))
{
$get_info = "SELECT `u`.`userid`, `u`.`username`, `r`.`winnings` " .
           "FROM `users` `u` " .
           "LEFT JOIN `lottery_winners` `r` " .
           "ON `r`.`userid`=`u`.`userid` " .
           "WHERE `r`.`userid` = ".$win['userid']."";
$user = $db->query($get_info);
echo "<tr>
<td align='center'>".htmlentites(stripslashes($user['username']))."</td>
<td align='center'>".number_format($user['winnings'])."</td>
</tr>";
}
echo "</table>";
}

 

shhhh dj i've been up quite awhile lol.

[Rasheed saeed]

Lmao.

BTW Josh you don't need to add anything after the [ at the end of the query. It's still in there, plus save you like 4 secs. :P

[Joshua]

I like balance ;-)

It looks better that way and i don't think it takes me 4 seconds to type ." lol

maybe a half a milli second :P

[Nicholas]

thanks username is showing now. but another problem cant figure it out

(yes i did try to sort it out but not having any luck)

but well when you go to

lottery_win.php?Raffle=Number&ID=1 (first winner)

it shows the 2 players that have won the weekly lottery.

and same with

lottery_win.php?Raffle=Number&ID=2 (secound winner)

it shows the 2 players that have won.

any idea on how to make it only show the winner of that raffle ID?

sorry if im being annoying... not made this probably simple mod before lol.

once i see how its done, ill never really have to ask for help on this type of selecting again...

as i think i get how to do left join queries now :D

 

ive created new file for the time being until i can get this setup working

NOTE: this file is called "lottery_winners.php" and one im trying to set up in called "lottery_win.php" which is the function setup up top were working on.

<?php
include "globals.php";

global $db,$ir,$c,$h,$userid;

echo "<center><h3>Last 25 weekly lottery winners and amount!</h3></center>";

echo "<center><table width='60%' cellspacing='1' class=table border='1' bordercolor='#636363'></center>
<tr>
<th width='15%'>Lottery Number</th>
<th width='25%'>Name</th>
<th width='20%'>Amount</th>
</tr>";

$get_wins = sprintf("SELECT u.userid, u.username,r.* FROM users u LEFT JOIN lottery_winners r ON r.userid=u.userid WHERE r.userid = u.userid ORDER BY lotteryID DESC LIMIT 25");
$do_wins = $db->query($get_wins);
while($win = $db->fetch_row($do_wins))
{
echo "<tr>
<td align='center'>#. ".number_format($win['lotteryID'])."</td>
<td align='center'>".htmlentities(stripslashes($win['username']))."</td>
<td align='center'>$".number_format($win['winnings'])."</td>
</tr>";
}
echo "</table>";
$h->endpage();
?>

[Joshua]

I'm not sure now i understand how the mod works.

Maybe change

$get_wins = "SELECT `lotteryID`, `userid`, `winnings` FROM `lottery_winners`";

$do_wins = $db->query($get_wins);

 

to

$_GET['ID'] = abs((int) $_GET['ID']);

 

$get_wins = "SELECT `lotteryID`, `userid`, `winnings` FROM `lottery_winners` WHERE `lotteryID`=".$_GET['ID']."";

$do_wins = $db->query($get_wins);

[Joshua]

in retrospect you may want to add

if(!$_GET['ID'])

{

echo "You did not select a valid link ID";

return;

}

[Nicholas]

$_GET['ID'] = abs((int) $_GET['ID']);

and

lotteryID = ".abs(intval($_GET['ID']))."

that made it work fine, thanks :)

im surprized that simple line never crossed my mind...