Updating row value from one table into another in MySQL database without success

I am trying to store a row value from one table into another without success.

Please find the code example below with explanation.

This is part of the index.php where the value from $userID is stored in $custom variable. The ‘firstname’ row is found in the ‘members’ table.

// code in between excluded for clarity
$userID = !empty($_SESSION['firstname'])?$_SESSION['firstname']:header("Location: {$home_url}login.php?action=please_login");
<!-- code in between excluded for clarity -->
<form action="<?php echo PAYPAL_URL; ?>" method="post">
<!-- code in between excluded for clarity -->
	<!-- Custom variable user ID -->
	<input type="hidden" name="custom" value="<?php echo $userID; ?>">
<!-- code in between excluded for clarity -->

This is a part of the ipn.php where the information is stored into database after successful transaction. The problem is it does not store value from $custom variable into subscr_id row found in members table:
if (strcmp($res, "VERIFIED") == 0 || strcasecmp($res, "VERIFIED") == 0) {
         //... code in between excluded for clarity
	// Retrieve transaction data from PayPal
	$txn_id = !empty($_POST['txn_id'])?$_POST['txn_id']:'';
	$custom = $_POST['custom'];
        //... code in between excluded for clarity
		// Check if transaction data exists with the same TXN ID
		$prPayment = $db->query("SELECT id FROM user_subscr WHERE txn_id = '".$txn_id."'");
		if($prPayment->num_rows > 0){
			// Insert transaction data into the database
			$insert = $db->query("INSERT INTO user_subscr(user_id,txn_id) VALUES('".$custom."','".$txn_id."')");
			// Update subscription id in the members table
			if($insert && !empty($custom)){
                                // 'firstname' is stored in $subscr_id
				$subscr_id = $custom;
                                // does not update value from 'user_id' row found in 'user_subscr' table into 'subscr_id' row found in 'members' table. The 'subscr_id' row is always empty.
				$update = $db->query("UPDATE members SET subscr_id = {$subscr_id} WHERE firstname = {$custom}");

What am I doing wrong here, can someone show me on how to solve this problem?

P.S. I have tried to store a row containing number or email in a session variable before, but can’t login and always returns me to the login page.

Thank you.

Do you have error reporting on? Any errors?

I’m not sure about the logic here:-

So you only want to insert if there is no result from the SELECT query? Is that right?

Also the obligitory comment that you should be using prepared statements, not putting variables straight into queries.

In this part

$subscr_id = $custom;
$update = $db->query("UPDATE members SET subscr_id = {$subscr_id} 
       WHERE firstname = {$custom}");

would the firstname really be the same value as the subscriber ID?

I read that as checking to see that the transaction-id isn’t already in a row in the database, i.e. duplicate detection. Could be done by setting the column to be unique and handling the error, which I recall several posters seemed to prefer a week or two back.


if (strcmp($res, "VERIFIED") == 0 || strcasecmp($res, "VERIFIED") == 0) {

is there really a need to do both of these?

1 Like

Yes, that doesn’t make much sense at all. Both variables are the same value.
Also seems odd the ID the row by “firstname”, normally you would use the PK, and I don’t see “firstname” being a good choice of PK.

I did consider that too, but though I would question it, especially since the if results in a cold exit.

1 Like

Actually, now I read the comment above the query, it seems stranger still:

// does not update value from 'user_id' row found in 'user_subscr' table 
into 'subscr_id' row found in 'members' table. 
The 'subscr_id' row is always empty.

If the query finds a row to update based on the value of $custom, then it would suggest that it should set the subscr_id column to that same value.

Or perhaps that suggests that the row is not being found at all.

I never got as far as using IPN with PayPal. Isn’t this code being called by PayPal after a successful payment has been processed, so it’s not really part of the user interaction? Perhaps that is why a duplicate transaction has a cold exit (to handle http “bounce”, maybe) and why the code ends in die.

For example if as TRUE statement is column ‘firstname’, and ‘email’ column is the response, it returns NULL when var_dump($userID) and also every other column except ‘firstname’ and ‘lastname’. All columns are VARCHAR(32):

    // This returns NULL. Every column is returned NULL except firstname and lastname columns.
    $userID = !empty($_SESSION['firstname'])?$_SESSION['email']:header("Location: {$home_url}login.php?action=please_login");

By this it seems like a conflicting something, but what?

Please replace row with column.

I was mentally doing that.

It’s really confusing that you mix and match userID, firstname and email around the place.

It’s strange here

$userID = !empty($_SESSION['firstname'])?$_SESSION['email']:header("Location: {$home_url}login.php?action=please_login");

that you check to see if the firstname session variable is not empty, and if it is not, you assign the value of a different session variable (email) to the $userID variable. You are not checking to see if there’s anything in the email session-var - what if that’s empty?

From what you are saying it seems that the issue is in putting the variables into the session vars, rather than anything else. If you view the source of your form prior to submitting it, does it have a value for the custom field in there? If it does not, please show the code you use to store the session variables.

1 Like

The comment in this code indicates that a column is not being updated. However, there are lots of ways that this column might not be updated. There are many places in the code where execution may have stopped.
Are you 100% sure that your UPDATE SQL gets executed?
If I were you, I would add some logging statements to see what’s going on:

  1. add a log statement after checking for VERIFIED and add one before the die statement.
  2. add a log statement which prints the $_POST variable.
  3. add a log statement if txn_id is empty
  4. maybe add a log comment if you got a duplicate txn_id. But that doesn’t appear to be your problem.
  5. add a log statement to see if the insert variable was true, and also check for the output of the UPDATE statement.

If there’s no logging system in place, you can temporarily add error_log statements, and remove them after debugging.

1 Like

For some reason PHP error logging was disabled in my localhost, now that I enabled it again, it suddenly became clear that all the time I had undefined index as error log, so have solved the problem that was in my login script by indexing the missing SQL ‘subscr_id’ column found in ‘members’ table. Now everything works smooth thanks to you guys, you are great helping me solve the problem.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.