CODEWITHSUNDEEP
mysql
JonnyDevs
JonnyDevs

Reputation: 45

using only mysql for a referral system

I'm trying to setup a referral system, pretty simple but the difficult part is that I'd like to use only SQL queries very limited PHP, here is my extremerly lame attemp:

IF EXISTS( SET @UID=userid FROM tbl_users WHERE username = 'seang' 
   THEN
       BEGIN
           UPDATE tbl_profile
              SET refcount=refcount+1
           WHERE userid = SELECT @UID 
           AND UPDATE tbl_profile SET referrer= SET @UID
      END;
ENDIF;

Obviously that's very wrong, but I still tried :/ Here is what I'm trying to do, in english. check if the username exists of the person who referred the new user (seang referred him/her), if seang does exist we'll increase his referral count by 1 and well set the new users referrer to seang's userid. seang's username and userid is stored in tbl_users, his referral count is stored in tbl_profile. seang's userid is also in the tbl_profile table.

Upvotes: 1

Views: 164

Answers (1)

tadman
tadman

Reputation: 211690

Using a stored procedure for this sort of thing is massive over-kill considering you can do it with a simple UPDATE statement:

UPDATE tbl_profile r, tbl_users u, tbl_profile p
  SET p.refcount=p.refcount+1,
      r.referrer=u.userid
  WHERE r.referrer IS NULL
    AND p.userid=u.userid
    AND u.username=?
    AND r.userid=?

Where ? is a placeholder value for your user's name and the second placeholder is for the referred user's userid.

This query demonstrates why this is really not a good idea to lean so heavily on SQL to do things. This gets exponentially more complicated as you try and stay in the SQL domain.

Keep in mind there's no reason to use "only SQL queries" for this. Application layers are important and often a better place to perform certain operations like resolving usernames to ID values.

Consider: If some day your user and profile tables are stored on different database servers most ORMs will deal automatically but your SQL only code will cease to work.

Upvotes: 2

Related Questions