PHP - Php/mysql Ranking Users

This topic has been moved to Other Libraries and Frameworks.

http://www.phpfreaks.com/forums/index.php?topic=359217.0

I have an auctions website and I want to create a feature that is similar to ebay in which users will receive an email if one of the auctions they are watching will end in less than 3 hours. I will be using a cron job to call up this page every 15 minutes. When the cron job executes, I would like it to send 1 email per auction to every user that has that auction on their watchlist if the auction will be ending in 3 hours. I don't know whether it needs to be a separate email for each user or one mass email where their emails are hidden.

The 4 tables in my database we are concerned about are "auctions, "users, "watchlists" and "products."

I am trying to use the script phpMailer to execute the code because I heard it was the best one. Anways here is what I have so far. I am missing alot because I had no clue what to do.


<?php
require("class.phpmailer.php");


$holder = mysql_connect("localhost", "user", "password");
mysql_select_db("database", $holder);

// NEED TO FIX THIS // Need to get ALL of the auction id's where the end time is less than 3 hours and the notification hasn't already been sent //
$auctionid = mysql_query("SELECT id FROM auctions WHERE DATE_ADD(NOW(), INTERVAL 3 HOUR) <= end_time AND notification = 0", $holder);

// get the auction title of  EACH of the auctions selected above which is not stored in the auctions table but in the products table..will be used for body of email /// AGAIN, NEED THIS TO GET ME ALL OF THE NAMES OF AUCTIONS THAT ARE ENDING IN 3 HOURS//
$auctiontitle = mysql_query("SELECT name FROM products LEFT JOIN auctions ON auctions.product_id=products.id WHERE auctions.id = $auctionid", $holder);

// PROBABLY NEED TO FIX THIS // Need to get ALL of the email addresses  who have ANY of the above auction ids on their watchlist //
$email = mysql_query("SELECT email FROM users LEFT JOIN watchlists ON users.id=watchlists.user_id WHERE watchlists.auction_id = $auctionid", $holder);

// Update the auctions table. Turn notification to 1 so the notification for that auction can't be sent again // AGAIN NEED THIS FOR ALL OF THE AUCTIONS ENDING IN 3 HOURS //
$query1="UPDATE auctions SET notification = '1' WHERE id = '$auctionid'";
mysql_query($query1) or die(mysql_error());



$mail = new PHPMailer();

$mail->From = "no-reply@domain.com";
$mail->FromName = "Site Name";


// Getting and error message for the foreach but I saw a similar example and this is what I was told to do // NEED THIS TO ADD EACH OF THE EMAIL ADDRESSES INDIVIDUALLY //
foreach ( $email as $recipients ) {
$mail->AddAddress ($recipients);
}
$mail->WordWrap = 50;                                 // set word wrap to 50 characters
$mail->IsHTML(true);                                  // set email format to HTML

$mail->Subject = "Your Watched Auction is Ending Soon";

// Sample Body // WANT TO DISPLAY THE TITLE OF THE AUCTION (NAME OF PRODUCT) FOR THE AUCTION ID USING $auctiontile FROM ABOVE //
$mail->Body    = "Your auction titled $auctiontile is ending soon";

// Same as above //
$mail->AltBody = Your auction titled $auctiontile is ending soon";


if(!$mail->Send())
{
   echo "Message could not be sent.";
   echo "Mailer Error: " . $mail->ErrorInfo;
   exit;
}

echo "Message has been sent";


?>

Hello fellow PHP Freaks 

Is there a way to assign mysql access privileges using a php script? Maybe when your registering a user and assign them a role, depending on the role, they get certain privileges on the db...
I need help.

Thank you in advance.

Hi All,

I've searched long and hard accross the web for an answer to this and finnally given in and requesting help. Here's what i have, i have a database setup and working fine. What i would like to do is for an administrator to be able to update my users details. It may sound odd, why don't you let your users update their own details? Well the administrators are dispatchers if you like, and my users are the 'dispatchees', for want of a better word. So i would like my administrators to be able to dispatch my users with routes and my users be able to see the routes that have been dispatched to them. I've setup a login area and a page that pulls there routes off the database, depending on their login details, i.e. jack will see his routes and jill will see her's independantly. This works by me editing the appropriate columns/rows of my database using phpmyadmin. What i'd like now is for administrators (who are directed to a seperate page, with more controls) to be able to do the same as me (updating the database) but by using a php form/script.

I'd like to be able to select the routes from a second table on the same database if possible, to try and keep everything tidy. So my dispatcher would select Route001 from a drop down list, this would fill in the text fields next to the route field with From To, so my dispatcher would know what route001 actually is from/ too, choose a username (now being driven from my other table) and hit dispatch. My user would login to their area, hit view dispatched routes and it would display Route 001 with the correct information.

The login area was a downloaded script i modified to suit and is called Login-Redirect_v1.31_FULL

Many thanks in advance, hope you can sort of understand what i want 

Josh

PHP/MySQL ability:Novice

Hello   I am trying to work out how many regular users I have to my site and how long those users tend to be users..   So, I have a table that logs every time a user visits my site and logs in, it stores the date / time as a unix timestamp and it logs their user id.   I started by getting the id's of any user who logs in more than 5 times in a specified period, but now I want to extend that...  

 
SELECT userID as user, count(userID) as logins FROM login_history where timestamp > UNIX_TIMESTAMP('2014-06-01 00:00:00') and timestamp < UNIX_TIMESTAMP('2014-07-01 00:00:00') group by user having logins > 5;
 

I would like to create something like foursquare badges. When ppl upload files, the most downloaded files will have a new badge, how do i create that?

I am trying to figure out how to assign a number rank to a set of results, but I need to reset the ranking when a certain column changes.  For example:

Brand    Rank
a   1
a   2
a   3
b   1
b   2
b   3

I set $rank=0; and then for each item in my loop I add $rank++;  How do I get the rank to reset in the loop when the brand changes?  Thanks for any help.

I have a txt file of numbers e.g. 1,4,3,5,19,3,11 all in a column. Is it possible to echo only the highest 3 numbers?

Hello,

I'm in need of assistance trying to get this PHP Script working and I'm hoping you'll be able to find a solution.

SQL Table Format: http://pastebin.com/LUuu2pLp
Code: [Select]
--
-- Table structure for table `hungergames_records`
--

CREATE TABLE IF NOT EXISTS `hungergames_records` (
  `user_id` int(10) unsigned NOT NULL,
  `victories` int(32) unsigned NOT NULL DEFAULT '0',
  `biggest_kill_streak` int(32) unsigned NOT NULL DEFAULT '0',
  `most_chests_opened` int(32) unsigned NOT NULL DEFAULT '0',
  `total_chests_opened` int(32) unsigned NOT NULL DEFAULT '0',
  `lastlogin` int(32) unsigned NOT NULL DEFAULT '0',
  `longest_lifespan` int(32) unsigned NOT NULL DEFAULT '0',
  `total_lifespan` int(32) unsigned NOT NULL DEFAULT '0',
  `total_points` int(32) unsigned NOT NULL DEFAULT '100',
  `most_points` int(32) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `hungergames_users`
--

CREATE TABLE IF NOT EXISTS `hungergames_users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `donor` int(32) unsigned NOT NULL DEFAULT '0',
  `user` varchar(40) NOT NULL,
  `donor_cooldown` int(100) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user` (`user`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12641 ;

PHP Code: http://pastebin.com/aQANz0BL
Code: [Select]
<?php
 
include("config.php");
 
$getRankQuery = "SELECT hungergames_users.id, hungergames_records.user_id, hungergames_records.victories, hungergames_records.biggest_kill_streak, hungergames_records.most_chests_opened, hungergames_records.total_chests_opened, hungergames_records.lastlogin, hungergames_records.longest_lifespan, hungergames_records.total_lifespan, hungergames_records.total_points, hungergames_records.most_points, hungergames_users.donor, hungergames_users.user FROM hungergames_records, hungergames_users WHERE hungergames_records.user_id = hungergames_users.id ORDER BY hungergames_records.total_points DESC";
$getRankResult =  mysql_query($getRankQuery);
 
//Source From: http://stackoverflow.com/questions/7704311/calculate-rank-with-points 
function getUserRank($searchedUserID)
{
    $userArray = getAllUsersOrderedByPoints();
    $rank       = 0;
    $lastPoints = -1; // Never happens
    foreach ( $userArray as $user) {
        if ($user['total_points'] != $lastPoints)  $rank++;
        if ($user['user_id'] == $searchedUserID) break;
    }
    return $rank;
}
 
while ($data = mysql_fetch_array($result)) {
echo getUserRank($data["user_id"]);
}
 
?>

What am I trying to accomplish? Simple Ranking System...

Basically, The total points is stored into total_points field for each user and whoever has the highest score will be Ranked #1 and count down to a the full database (Currently at 12,641 users)

Now the above function getUserRank is the code I pulled from the above source listed, I think my issue relies in the function of getAllUsersOrderedByPoints(); - I've tried doing something like.

Code: [Select]
$row = mysql_fetch_array($getRankResult);

Then adding:

    $userArray = $row["user_id"];

Didn't work - So if you can help me correct this than I'll be greatly appertiated.

Lastly, I've tried doing:

Code: [Select]
$index = 1;
  while ($data = mysql_fetch_array($getRankQuery)) {
    echo $index++ . " " . $data["id"] . " ".$data["user"]." <br>\n";
}

This solution works, but this solution would not work for when a "User is Search" it would count them as the highest rank 1,2,3 and wouldn't work, so either the function up there needs to be corrected or please help get a working solution, I've been googleing this issue and seems too be a common code issue with a lot people are having and not many solutions.

The application that records data (Not PHP) doesn't store any data with a "Rank" table otherwise this would be a simple echo'ed out data.

For a better understanding of what I'm trying to do - You may take a look at: http://justonemoreblock.com/lb/leaderboards.php

Regards,
Cory

Hello everyone... I made point system that ranks users in my app.  There are two parts to the system, a primary score and a dynamic score.  The primary score is based on how you well you perform and the dynamic score is based on how well your friends perform.  Both of these get added together to equal your total score.  The problem is that I have a lot of looping occurring which results in about 400 queries being run when I access the page that executes this class.  Can this be optimized?  What's the best way to do this?  I don't want to store the data as I want this presented in real-time.

Code: [Select]
<?php

class edge
{
public function get_all_users_edge()
{
$sql = "SELECT id FROM users WHERE status = 3";

$result = mysql_query($sql) or die(mysql_error());

$ids = array();
while (($row = mysql_fetch_assoc($result)) !== false)
{
$ids[] = $row;
}

$all_edges = array();

foreach ($ids as $k => $id)
{
$edge = $this->get_users_edge($id['id']);
$all_edges[$k] = $edge;

}

return $all_edges;
}

public function get_users_edge($uid)
{
$users_primary_edge = $this->get_users_primary_edge($uid);
$users_dynamic_edge = $this->get_users_dynamic_edge($uid);

return $users_primary_edge + $users_dynamic_edge;
}

public function get_users_primary_edge($uid)
{
if (active_id($uid) === false)
{
return false;
}

$uid = (int)$uid;

$sql = "SELECT
CHAR_LENGTH(`users`.`credentials`) AS `cred_chars`,
CHAR_LENGTH(`users`.`specialties`) AS `spec_chars`,
`companies`.`companyvideo` AS `video`,
`investor_info`.`accredited` AS `accredited`,
           ( SELECT COUNT(`user_id`) FROM `blog_posts` WHERE `user_id` = '${uid}' ) AS `post_count`,
           ( SELECT COUNT(`uid`) FROM `votes` WHERE `uid` = '${uid}' ) AS `vote_sent_count`,
`votes_received`.`inv_vote_received_count`,
`votes_received`.`pub_vote_received_count`,
           ( SELECT COUNT(`userid`) FROM `employees` WHERE `userid` = '${uid}' ) AS `joined_count`,
`votes2`.`ent_count`,
`votes2`.`inv_count`,
(
SELECT
COUNT(`company_id`)
FROM `company_fans`
LEFT JOIN `companies` ON `company_fans`.`company_id` = `companies`.`companyid`
LEFT JOIN `employees` ON `employees`.`companyid` = `companies`.`companyid`
WHERE `userid` = '${uid}'
) AS `company_fans_count`,
        ( 
         SELECT 
         COUNT(`user_id`) 
         FROM `recommendations` 
         WHERE `user_id` = '${uid}' 
        ) AS `recommendation_count`,
        ( 
         SELECT 
         COUNT(`partner_id`) 
         FROM `partners` 
         WHERE (`user_id` = '${uid}' OR `friend_id` = '${uid}') 
         AND `approved` = '1' 
        ) AS `associate_count`
FROM `users`
        LEFT JOIN `investor_info` ON `investor_info`.`uid` = `users`.`id`
LEFT JOIN `employees` ON `employees`.`userid` = `users`.`id`
LEFT JOIN `companies` ON `employees`.`companyid` = `companies`.`companyid`
LEFT JOIN (
SELECT
`employees`.`userid`,
               SUM( IF( `votes`.`vote_type` = 1, 1, 0 ) ) AS `inv_vote_received_count`,
               SUM( IF( `votes`.`vote_type` = 0, 1, 0 ) ) AS `pub_vote_received_count`
FROM `votes`
INNER JOIN `employees` ON `employees`.`companyid` = `votes`.`company_id`
WHERE `employees`.`userid` = '${uid}'
             GROUP BY `employees`.`userid`
         ) AS `votes_received` ON `votes_received`.`userid` = `users`.`id`
LEFT JOIN (
SELECT
`beta_keys`.`ref_id`,
               SUM( IF( `users`.`accounttype` = 1, 1, 0 ) ) AS `ent_count`,
               SUM( IF( `users`.`accounttype` = 0, 1, 0 ) ) AS `inv_count`
FROM `beta_keys`
INNER JOIN `users` ON `users`.`id` = `beta_keys`.`userid`
WHERE `beta_keys`.`ref_id` = '${uid}' AND `users`.`status` = 3
             GROUP BY `users`.`id`
         ) AS `votes2` ON `votes2`.`ref_id` = `users`.`id`
WHERE `users`.`id` = '${uid}'";

$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);

$score = 0;

if((!empty($row['cred_chars'])) && (!empty($row['spec_chars'])))
{
$score += 200;
}

if(!empty($row['video']))
{
$score += 50;
}

if($row['accredited'] == 1)
{
$score += 100;
}

$score += (50 * $row['post_count']);
$score += (5 * $row['vote_sent_count']);
$score += (3 * $row['inv_vote_received_count']);
$score += (1 * $row['pub_vote_received_count']);
$score += (200 * $row['joined_count']);
$score += (40 * $row['ent_count']);
$score += (200 * $row['inv_count']);
$score += (10 * $row['company_fans_count']);
$score += (30 * $row['recommendation_count']);
$score += (20 * $row['associate_count']);

return $score;
}

public function get_users_dynamic_edge($uid)
{
$uid = (int)$uid;

$sql = "(
SELECT 
`users`.`id`
FROM partners
INNER JOIN `users`
ON `partners`.`user_id` = `users`.`id`
WHERE partners.friend_id = '${uid}'
AND `approved` = 1
)
UNION ALL
(
SELECT 
`users`.`id`
FROM `partners`
INNER JOIN `users`
ON `partners`.`friend_id` = `users`.`id`
WHERE `partners`.`user_id` = '${uid}'
AND `approved` = 1
)";

$result = mysql_query($sql) or die(mysql_error());

$i  = 0;
$score  = 0;

while (($row = mysql_fetch_assoc($result)) !== false)
{
$dynamic_scores[$i] = array(
'uid'  => $row['id'],
'score' => $this->get_users_primary_edge($row['id']),
);

$i++;
}

if(!empty($dynamic_scores))
{
foreach($dynamic_scores as $k => $dynamic_score)
{
$score += $dynamic_score['score'];
}
}

return ($score * 0.1);
}
}

?>

Hey,

I've been working on a script to match playing card hands in a poker game against the river to return what the hand the user has and who won against other players.

The problem is how do i approach an algorithm to do this in the first place.

Cards are stored and structured by 2 characters seperated by a full stop.

Example - 2 of diamonds and 3 of clubs is:
D.2,C.3

now imagine a line of 5 cards (the river) and im trying to work out what they have and return the answer such as:

2 pairs or royal flush etc

I so far decided my easiest way is to explode on the comma to get each card - then explode a second time on the full stop to get the suit and value into different variables.

How ever - after that point im lost how i can do this up against the river to check what hand the user has.

Any one got any tips on the best approach ?

Hi

I've set-up a multidimensional PHP array from a SQL query which pulls back parcels despatched per item on a daily basis.

e.g.

Item|Day1|Day2 |Day3|Day4|Day5
1   |100 | 120 | 90 |150 |60
2   |150 | 200 | 80 |90  |100
3   | 1  |  2  | 3  | 4  | 5

I want to be able to assign a ranked value to each day based upon the amount of items sent out each day and put that either into the existing array, or create a new array to which it can reference

I have a mysql table which will store users email addresses (each is unique and is the primary field) and a timestamp. 
I have added another column called `'unique_code' (varchar(64), utf8_unicode_ci)`.

What I would very much appreciate assistance with is;

a) Generating a 5 digit alphanumeric code, ie: 5ABH6 
b) Check all rows the 'unique_code' column to ensure it is unique, otherwise re-generate and check again 
c) Insert the uniquely generated 5 digit alphanumeric code into `'unique_code'` column, corresponding to the email address just entered. 
d) display the code on screen.

What code must I put and where?


**My current php is as follows:**

Code: [Select]
    require "includes/connect.php";
   
    $msg = '';
   
    if($_POST['email']){
   
    // Requested with AJAX:
    $ajax = ($_SERVER['HTTP_X_REQUESTED_WITH']  == 'XMLHttpRequest');
   
    try{
    if(!filter_input(INPUT_POST,'email',FILTER_VALIDATE_EMAIL)){
    throw new Exception('Invalid Email!');
    }
   
    $mysqli->query("INSERT INTO coming_soon_emails
    SET email='".$mysqli->real_escape_string($_POST['email'])."'");
   
    if($mysqli->affected_rows != 1){
    throw new Exception('You are already on the notification list.');
    }
   
    if($ajax){
    die('{"status":1}');
    }
   
    $msg = "Thank you!";
   
    }
    catch (Exception $e){
   
    if($ajax){
    die(json_encode(array('error'=>$e->getMessage())));
    }
   
    $msg = $e->getMessage();
    }
    }

hi i have a table like this


rank        xp
1              0
2             50
3             100
4             200

say my username has 75xp how would a select which rank i am

not sure how i can do this? :S

cheers matt

Hello everyone...

My following code is supposed to order rank by from Greatest covertaction but for some reason it is ranking the greatest last eg.
900,000 Rank 1
900,001 Rank 2
900,002 Rank 3

Here is the code:

Code: [Select]
<?php
$q = "SELECT * FROM `accountinfo_db` ORDER BY `covertaction`, `anticovertaction` ASC";
$res = mysql_query($q) or die(mysql_error());
$i = 1;
while($player=mysql_fetch_array($res)){
$id = securevar($player['id']);
$time = time();
$user = securevar($player['username']);
$q = "UPDATE `accountinfo_db` SET `covertrank` = '$i', `lastTurnTime` = '$time' WHERE `id` = '$id'";
if(mysql_query($q)){
echo "Covert & Anti Covert Rank set to $user as ".number_format($i)."!<br />";
} else {
// query failed with an error
// put your error reporting/logging code here...
}
$i++;
}
?>

Any help will be appreciated!
Brian