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'
WHERE userid = SELECT @UID
AND UPDATE tbl_profile SET referrer= SET @UID
Using a stored procedure for this sort of thing is massive over-kill considering you can do it with a simple
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=?
? 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.