How many time a string is in a column

Question

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?

Triggers might be a good fit: http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html — Rudolf Mühlbauer, Oct 14 '12 at 14:06
I'm using the mysql_real_escape_string now would that be enough? — user1341765, Oct 14 '12 at 14:38
I'm working on changing the mysql_* functions to the PDO API now. — user1341765, Oct 14 '12 at 14:39
so far nothing, but at this point I don't know how to get how many times a ref is in the db. — user1341765, Oct 14 '12 at 14:50
@LinusKleen I believe I do not need to post anything on meta in order to educate the community. — PeeHaa, Oct 14 '12 at 18:12

score 1 · Answer 1 · edited May 23 '17 at 10:24

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.

score 0 · Answer 2 · answered Oct 14 '12 at 14:47

0

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

answered Oct 14 '12 at 14:47

Mark Reed

91,912
16
138
175

How many time a string is in a column

2 Answers2