Member Avatar for kaosjon

Hi, i am just adding the last component to my registration page, but i am having a little trouble with it. Basically my website includes a referal based system, wherebye when the user signs up they can add a referal that benefits the referal later on. The code i am trying to add is to increase the number of referals a member has by 1. It might sound a bit confusing and trust me its taken a few hours to get my head around it, but my code must find the information of the referal and update their number of referals by 1. The code i have below is what i think should work, but it does not update the number of referals by 1.

if($referal_check > 0)
	 {
		
		mysql_query("UPDATE referrals SET no_of_referrals = no_of_referrals + '1' WHERE username = '$referal'");
	 }

The referal_check is a query to find whether the referal exists in the system, the code is below

$sql_referal_check = mysql_query("SELECT username FROM user_info WHERE username='$referal'");
     $referal_check = mysql_num_rows($sql_referal_check);

Sorry if this is a bit confusing, please ask if you need some more clarification on the code.

Thanks

  • 2 Contributors
  • 7 Replies
  • 134 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by kaosjon

Dani AI

Generated

Quick summary and what likely fixed it

This was a classic logic/debugging gap: the registration flow ran a lookup, then tried to increment a stored counter but nothing changed. guided the OP to echo queries for debugging, and later reported that taking the existence check out made the update run — which points to the existence check or surrounding control flow, not the SQL arithmetic itself, being the real blocker.

Why an existence check can silently fail

  • The lookup can return “no rows” for reasons other than the record truly not existing: the checked variable may contain trailing whitespace, different case/collation, or an unexpected character.
  • The SELECT and UPDATE may be using different connections or databases (an include file or a connection scope issue can cause this).
  • Errors were being suppressed or the script redirected before output, so echoing didn’t appear.
  • Permissions, transactions (autocommit off), or a mismatched column name/type can prevent an update from taking effect even when a query runs.

Practical debugging checklist

  • Turn on full error reporting and display or log errors.
  • Inspect the exact values used in the lookup/update (use trim() and a var_dump/log) to rule out whitespace/encoding.
  • Confirm the SELECT returns rows by checking the result count directly and log it.
  • Ensure both queries run on the same DB connection and database.
  • After an update attempt check the number of affected rows and any DB error text.
  • Run the same statements directly in a DB client to confirm behavior outside PHP.

Safer, more robust pattern

Use parameterized statements and explicit error handling (PDO example shows the approach and how to check affected rows). See PDO prepared statements for details.

$pdo = new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$stmt = $pdo->prepare('UPDATE my_table SET counter = counter + 1 WHERE user = :u');
$stmt->execute([':u' => trim($username)]);
$rowsUpdated = $stmt->rowCount();

Caution: avoid deprecated mysql_* functions in new code, always validate/trim user input, and log DB errors while debugging to spot silent failures.

Recommended Answers

All 7 Replies

Member Avatar for Member #120589 
"UPDATE referrals SET no_of_referrals = no_of_referrals +1 WHERE username = '$referal'"

no need for quotes around 1.
echo out the sql to see if its what you expect

Member Avatar for kaosjon

Hi, i removed the quotes around the 1 and still does not add the referal. I then tried to echo out the query but it just goes to a white screen with no output. I removed the include at the end as it goes to a seperate page, so you won't see the echo, but when it reloads onto registration.php its just a white screen.

Any ideas?

Thanks

Member Avatar for Member #120589 
if($referal_check > 0)
	 {
 echo "UPDATE referrals SET no_of_referrals = no_of_referrals + 1 WHERE username = '$referal'";
		mysql_query("UPDATE referrals SET no_of_referrals = no_of_referrals + 1 WHERE username = '$referal'");
	 }
echo "SELECT username FROM user_info WHERE username='$referal'";
$sql_referal_check = mysql_query("SELECT username FROM user_info WHERE username='$referal'");
     $referal_check = mysql_num_rows($sql_referal_check);

I didn't understand any of the include stuff you stated as it's not included in the code you supplied, so the above is what I suggest.

If you don't get any output from the UPDATE echo, it's because the $referal_check is not > 0.
If you do get output - copy the output to the screen and paste it into phpmysql and see if it works.

Edited by Member #120589 because: n/a
Member Avatar for kaosjon

Hi, sorry about the include stuff, didnt realise i did not include the code.

I entered the two echo commands but i only get one output

SELECT username FROM user_info WHERE username='admin1'

And when i tried the above code in phpmysql i get the username show up. I am guessing that because the second echo did not show it means that it is not recognizing the referal_check as being greater than 0. I will try and have a play around with the if statement (referal_check > 0) Any advice you can give i would greatly appreciate.

Thanks

Member Avatar for kaosjon

Hi, i have tried everything i can think of but it is still not working, the query works but it is not submitting it in the table.

Any ideas?

Thanks

Member Avatar for Member #120589

once again it seems to be the

if($referal_check > 0)

Try taking it off to see what happens.

Member Avatar for kaosjon

Brilliant it works now, thanks a lot

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.