MYSQL spillover function

Question

I am currently trying to code a Networking website and am stuck at the spillover function stage.Here is how it works, each registered user of the site is only allowed to refer two people into the network (they have a referral link for this). If however,a member gave his/her link to refer more than two people and the registering folk wants to sign up with the link,the following event should occur:

1. PHP should query MYSQL Database to ascertain if the sponsor has referred up to two(2) people, if YES then MYSQL will search for a random sponsor-username to replace the initial sponsor . 2. If on the contrary,MYSQL checks and found that the sponsor hasn't referred two people yet, then MYSQL will proceed to using the sponsor username for the new registering member.

Below is what the database table looks like:

My MYSQL database snapshot

The table name is affiliateuser, the referedby column is where the sponsors are shown for each member,i need member to only be able to appear as sponsor twice (maximum) under the referedby column. Looking at the table above,the user yelefash2 has referred two people with his link while user mipe305 hasnt referred anyone with his link or username,i need to set a balance and if a third person tries to register with yelefash2's username/referral link,let PHP/MYSQL replace him with a user who hasnt referred two people yet (it could be random pick or otherwise), this will spill over members automatically as referrals onto available spaces, e.g mipe305

I have tried the following PHP codes but it doesn't work:

 $ref=mysqli_real_escape_string($con,$_POST['referral']);//data from the referrer webform field//

$result = mysqli_query($con,"SELECT count(*) FROM  affiliateuser where username = '$ref'");

$row = mysqli_fetch_row($result);
$numrows = $row[0];
if ($numrows==0)
{
$msg=$msg."Sponsor/Referral Username Not Found..<BR>";//for checking if provided sponsor exits
$status= "NOTOK";
}

$reea = mysqli_query($con,"SELECT username,referedby, COUNT(username) FROM affiliateuser GROUP BY referedby ASC");
$reeeb = mysqli_query($con,"SELECT count(*) FROM affiliateuser where referedby='$ref' ");
$row = mysqli_fetch_row($reeeb););
$refcount = $rowp[0];



if ($refcount ==2 OR $refcount >2)
{$reee = mysqli_query($con,"SELECT username,referedby, COUNT(username) FROM affiliateuser GROUP BY referedby ASC");
$reeel = mysqli_query($con,"SELECT referedby FROM affiliateuser where COUNT(username)<2 ");
$row = mysqli_fetch_row($reeel);
$refpick = $row[0];
}
else
{$refpick=mysqli_real_escape_string($con,$_POST['referral']);}

I know i must be doing something wrong,am kinda new to MYSQL and PHP, any help would be pretty much appreciated

The $refpick variable is suppose to be the final result of the whole process that is sent into the database as the chosen sponsor for a registering member,This result is fiannlly added in the user's referedby column ,however, this code isnt working ,if i try to register a new user and i state the sponsor as yelefash2,i expect it to detect yelefash2 is used up(has referred two people already) and I expect the code to slot another random available user as his sponsor....it simply do nothing and sends yelefash2 as the sponsor anyways ( who already has limit of 2). — Powhattan, Jun 10 '17 at 16:08
Your code is prone to SQL Injection attack try using prepared statements — Black Mamba, Jun 10 '17 at 17:20
Ishan, pls shed some light on how i can further prevent sql injections,thanks — Powhattan, Jun 12 '17 at 06:48
Actually, despite the fact that there are a LOT of both semantic and syntactic errors in the code, there is **NO SQL INJECTION VULNERABILITY HERE** — symcbean, Jun 12 '17 at 16:22

AC TheGypsy · Answer 1 · 2017-06-12T16:16:06.067

Changed my awnser:

  $reea = mysqli_query($con,"SELECT username,referedby, COUNT(username) 
   FROM affiliateuser GROUP BY referedby ASC");

is not doing anything.but it could be something going somewhere else in the page and its not in this post.

but your result array variable is wrong.

  $refcount = $rowp[0];
 should be 
  $refcount = $row[0];

because $rowp is not defined anywhere.... also its row result:

   $row = mysqli_fetch_row($reeeb););

is wrong. it should be:

   $row = mysqli_fetch_row($reeeb);

at the end, the else condition:

  {$refpick=mysqli_real_escape_string($con,$_POST['referral']);}

can be simplified by:

  {$refpick=$ref;}

One thing about comparing,

  the if ($refcount ==2 OR $refcount >2) should work,
   but if (($refcount ==2) OR ($refcount >2)) will guarantee the correct operation. 
   I personally use || (double pipe) instead of "or" personally. 
   so I would have wrote it as:  if (($refcount ==2) || ($refcount >2)) {

I don't think the first variable has any issue,i use that kinda query multiple times within the website and the result is ok..The $ref=mysqli_real_escape_string($con,$_POST['referral']); also works as it pass data from the webform unto my script bypassing sql injections, Am just stuck with this spillover programming thing and need help even if i need a different sets of codes altogether to make it work — Powhattan, Jun 10 '17 at 16:36

MYSQL spillover function

1 Answers1