CODEWITHSUNDEEP
phpmysql
user1341765
user1341765

Reputation: 1

How many time a string is in a column

I'm working on a referral program script. I've got the below table:

id primary key
name varchar
email varchar
ref varchar
bid varchar
reward boolean

Basically once a three friends join by using the same ref, the person with the id equal to the ref will get a reward.

I need to check everytime that someone subscribe if three same ref are in the table and then set the reward to true and send an email to the person with the id equal to the ref.

I hope that is clear.

I'm struggling to get it done.

Below the whole script so far:

$Bname = mysql_real_escape_string($_POST['Bname']);
$Bemail = mysql_real_escape_string($_POST['Bemail']);
$Bref = mysql_real_escape_string($_POST['Bref']);
$Bid = mysql_real_escape_string($_POST['Bid']);

mysql_connect("******", "******", "*******") or die ('Error: '.mysql_error());
mysql_select_db("********");


$checkEmail = "SELECT email FROM betalist WHERE email ='".$Bemail."'";
$result = mysql_query($checkEmail);


if (mysql_num_rows($result) > 0) {
echo $Bemail." is already in our list";
exit;
}

if (mysql_num_rows($result) == 0) {
$query="INSERT INTO betalist (ID, name, email,ref,bid)VALUES ('NULL','".$Bname."','".$Bemail."','".$Bref."','".$Bid."')";
mysql_query ($query) or die ('Error updating database');

So far I've tried to work it out using the mysql_num_rows:

$refCount = "SELECT id, email FROM betalist WHERE ref='".$Bref."'"; 
$result = mysql_query($refCount);
if (mysql_num_rows($result)==3)
{ 
    echo $countarray; 
}

Can you help me?

Upvotes: 0

Views: 98

Answers (2)

PeeHaa
PeeHaa

Reputation: 72672

Your code is was vulnerable to an SQL Injection attack in its current state. I also see you are using the ancient mysql_* functions which is currently in the process of being deprecated . See the red box? Instead you should learn about prepared statements and use either PDO or MySQLi. If you can't decide, this article will help to choose. If you care to learn, here is a good PDO tutorial. You at least have tried to fix the SQLi part, but your current code will still fail, because mysql_real_escape_string() needs an open connection to the database (and you only open the database connection after the calls).

Your code using PDO will look something like the following:

<?php
$dbConnection = new PDO('mysql:dbname=dbname;host=127.0.0.1;charset=utf8', 'user', 'pass');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $stmt = $dbConnection->prepare('SELECT email FROM betalist WHERE email = :email');
    $stmt->execute(array(':email' => $_POST['Bemail']));
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($result) {
        echo htmlspecialchars($_POST['Bemail']), " is already in our list";
        exit;
    }

    $stmt = $dbConnection->prepare('INSERT INTO betalist (ID, name, email, ref, bid) VALUES (NULL, :name, :email, :ref, :bid)');
    $stmt->execute(array(
        ':name'  => $_POST['Bname'],
        ':email' => $_POST['Bemail'],
        ':ref'   => $_POST['Bref'],
        ':bid'   => $_POST['Bid'],
    ));

    $stmt-> $dbConnection->prepare('SELECT count(id) FROM betalist WHERE ref = :ref');
    $stmt->execute(array(':ref' => $_POST['Bref']));
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($result['count'] == 3) {
        // do stuff
    }
} catch(PDOException $e) {
    echo 'Something went wrong: ' , $e->getMessage();
}

It might also be better to validate the emailaddress which the user tries to use. This can easily be done with the built in filter_var() function:

if(filter_var($_POST['Bemail'], FILTER_VALIDATE_EMAIL) === false) {
    // user entered an invalid emailaddress
}

Note that I did a check whether the number of occurrences is exactly 3 so perhaps you want to check for a multiple of three instead.

Upvotes: 1

Mark Reed
Mark Reed

Reputation: 95252

There's a COUNT function in SQL.

SELECT COUNT(*) FROM betalist WHERE ref=?

and then check to see if the result is 3. Much more efficient than returning the actual rows and then counting them..

Upvotes: 0

Related Questions