im getting a 0 value when i need a correct order ID

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

im getting a 0 value when i need a correct order ID

Post by jonnyfortis »

i have a payment system set up. the process is the user selects a pay button this then goes through the the payment page that allocates an order ID before they are sent through to the credit card company. what is happening is when the user first goes to the order ID allocation page im a getting a 0 value rather than the correct order ID, if i then go back then try again i am getting the order ID required. There are two querys on the page. The first checks if order exists then if it does continue if it doesnt the second query creates the order.

below are the scripts

Code: Select all

//post the weeks paid
$total_weeks_paid_ref = $_POST['total_weeks_paid'];
$student_year_ref = $_POST['student_year'];

$colname_Recordset1 = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Recordset1 = $_SESSION['MM_Username'];
}
mysql_select_db($database_hostprop, $hostprop);
$query_Recordset1 = sprintf("SELECT * FROM plus_signupComplete, host_editprop2015 WHERE host_editprop2015.prop_id = plus_signupComplete.prop_id AND plus_signupComplete.userid = %s AND plus_signupComplete.year = '2015'", GetSQLValueString($colname_Recordset1, "text"));
$Recordset1 = mysql_query($query_Recordset1, $hostprop) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

// The amount to pay is passed to this page through POST
@$amount = $_POST['amount'];
if(!is_numeric($amount) || empty($amount))
{
	if(isset($_SESSION['post_data']['amount']))
	{
		$amount = $_SESSION['post_data']['amount'];
	}
	else
	{
		$amount = "0.01";
	}
}
$percent_fee = 1.0085;
@$fee = $_POST['fee'];
// Check for percentage or flat fee
if($fee[0] == 'p') {
	// Percentage Supplied
	$fee = substr($fee, 1) + 1;
	$amount = $amount * $fee;
} elseif($fee[0] == 'f') {
	// Flat Fee Supplied
	$amount = $amount + substr($fee, 1);
} elseif(!is_numeric($fee)) {
	// Non-numeric value supplied
	// Do not add anything
} else {
	// numeric value supplied
	if($fee < 1){
		// Assume $fee is a percentage
		$amount = $amount * (1 + $fee);
	} else {
		// Assume $fee is a flat fee
		$amount = $amount + $fee;
	}
}

// add default percentage fee
$amount = $amount * $percent_fee;

// Round result to 2 decimal places
$amount = round($amount,2);

$type = $_POST['type'];
if(empty($type))
{
	if(isset($_SESSION['post_data']['type']))
	{
		$type = $_SESSION['post_data']['type'];
	}
	else
	{
		$type = "";
	}
}

// Set Payment Option Type
if($type == "Option 1: Balance Before")
{
	$type_query = "UPDATE plus_signupComplete SET payment_option = '1' WHERE userid = %s";
}
elseif($type == "Option 2: Balance Before")
{
	$type_query = "UPDATE plus_signupComplete SET payment_option = '2' WHERE userid = %s";
}
elseif($type == "Option 3: Balance Before")
{
	$type_query = "UPDATE plus_signupComplete SET payment_option = '3' WHERE userid = %s";
}
elseif($type == "Option 4: Final Payment")
{
	$type_query = "UPDATE plus_signupComplete SET payment_option = '4' WHERE userid = %s";
}

$userid = GetSQLValueString($colname_Recordset1, 'text');
$studentid = GetSQLValueString($colname_Recordset1, 'id');

if(isset($type_query))
{
	$type_query = sprintf($type_query, $userid);
	$results = mysql_query($type_query);
}

i think the issue is in this part

Code: Select all

$timestamp = GetSQLValueString(date('M d Y H:i:s'), 'text');
$amount_due = GetSQLValueString($amount, 'text');
$transaction_status = GetSQLValueString("Awaiting Payment", 'text');
$payment_type = GetSQLValueString($type, 'text');
$id = $row_Recordset1['studentID'];
$year = 2015;

// Check if an order already exists
$query = "SELECT payment_id FROM host_payments2014 WHERE payment_userid = %s AND id = %s AND payment_transaction_status = %s AND payment_amount_due = %s AND payment_type = %s AND year = %s LIMIT 1";
$query = sprintf($query, $userid, $id, $transaction_status, $amount_due, $payment_type, $year);
$results = mysql_query($query);
if(mysql_num_rows($results) > 0)
{
	$row = mysql_fetch_array($results);
	$ORDERID = $row['payment_id'];
	}

else

{
	// Create order
	$query = "INSERT INTO host_payments2014 (id, payment_userid, payment_created_timestamp, payment_amount_due, payment_transaction_status, payment_type, payment_weeks, year) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)";
	$query = sprintf($query, $id, $userid, $timestamp, $amount_due, $transaction_status, $payment_type,$total_weeks_paid_ref, $student_year_ref);
	$results = mysql_query($query);
	
	$query = "SELECT payment_id FROM host_payments2014 WHERE payment_id = %s AND payment_created_timestamp = %s AND payment_amount_due = %s AND payment_type = %s AND id = %s AND year = %s LIMIT 1";
	$query = sprintf($query, $userid, $timestamp, $amount_due, $payment_type, $id, $year);
	$results = mysql_query($query);
	$row = mysql_fetch_array($results);
	$ORDERID = mysql_insert_id();
	}
thanks
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: im getting a 0 value when i need a correct order ID

Post by social_experiment »

Code: Select all

<?php
$query = "SELECT payment_id FROM host_payments2014 WHERE payment_userid = %s AND id = %s AND payment_transaction_status = %s AND payment_amount_due = %s AND payment_type = %s AND year = %s LIMIT 1";
?>
It seems that the payment records will have some type of unique value for each record so you should remove the LIMIT 1 syntax from the query; you are after all checking whether $results have more than 0 rows.

what results do you get if you echo $ORDERID in the segment below

Code: Select all

<?php
if(mysql_num_rows($results) > 0)
{
        $row = mysql_fetch_array($results);
        $ORDERID = $row['payment_id'];
        }
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: im getting a 0 value when i need a correct order ID

Post by jonnyfortis »

social_experiment wrote:

Code: Select all

<?php
$query = "SELECT payment_id FROM host_payments2014 WHERE payment_userid = %s AND id = %s AND payment_transaction_status = %s AND payment_amount_due = %s AND payment_type = %s AND year = %s LIMIT 1";
?>
It seems that the payment records will have some type of unique value for each record so you should remove the LIMIT 1 syntax from the query; you are after all checking whether $results have more than 0 rows.

what results do you get if you echo $ORDERID in the segment below

Code: Select all

<?php
if(mysql_num_rows($results) > 0)
{
        $row = mysql_fetch_array($results);
        $ORDERID = $row['payment_id'];
        }
?>
if i echo out the $ORDERID when i first it shows 0. then if i refresh the page i get the correct orderID. if i then look in the database the order has been added to the database.

i tried to remove the LIMIT 1 but am still getting the same results.

so basically when i first go on the page i get a orderID of ZERO if i refresh the page i get a "confirm form resubmission" if i continue this the page reloads with the correct orderID.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: im getting a 0 value when i need a correct order ID

Post by social_experiment »

Code: Select all

<?php
// Check if an order already exists
$query = "SELECT payment_id FROM host_payments2014 WHERE payment_userid = %s AND id = %s AND payment_transaction_status = %s AND payment_amount_due = %s AND payment_type = %s AND year = %s LIMIT 1";

?>
what are the results when you echo the query to the screen, when you execute the script the first time and after re-submission
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: im getting a 0 value when i need a correct order ID

Post by jonnyfortis »

social_experiment wrote:

Code: Select all

<?php
// Check if an order already exists
$query = "SELECT payment_id FROM host_payments2014 WHERE payment_userid = %s AND id = %s AND payment_transaction_status = %s AND payment_amount_due = %s AND payment_type = %s AND year = %s LIMIT 1";

?>
what are the results when you echo the query to the screen, when you execute the script the first time and after re-submission

if i echo

<?php echo $query ; ?>

the first echo shows

Code: Select all

SELECT payment_id FROM host_payments2014 WHERE payment_userid = 'name' AND payment_created_timestamp = 'Sep 03 2015 08:14:25' AND payment_amount_due = '100.00' AND payment_type = 'Option 1: Balance Before' AND id = 345 AND year = 2015 LIMIT 1
if i refresh i get the following

Code: Select all

SELECT payment_id FROM host_payments2014 WHERE payment_userid = 'name' AND id = 345 AND payment_transaction_status = 'Awaiting Payment' AND payment_amount_due = '100.00' AND payment_type = 'Option 1: Balance Before' AND year = 2015 LIMIT 1 
i just echoed out

Code: Select all

$ORDERID = mysql_insert_id();
on the first page load and it is showing a 0, i looked in the database and the order is there so it looks like the issue is after the create order. the order has been added but the query after is not getting that order id

so it looks like the issue is with the

Code: Select all

	$query = "SELECT payment_id FROM host_payments2014 WHERE payment_userid = %s AND payment_created_timestamp = %s AND payment_amount_due = %s AND payment_type = %s AND id = %s AND year = %s LIMIT 1";
	$query = sprintf($query, $userid, $timestamp, $amount_due, $payment_type, $id, $year);
	$results = mysql_query($query);
	$row = mysql_fetch_array($results);
	$ORDERID = mysql_insert_id();
	}
query
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: im getting a 0 value when i need a correct order ID

Post by Weirdan »

You're using mysql_insert_id() after a select query. Probably you want to replace that line with

Code: Select all

 // $ORDERID = mysql_insert_id();
 $ORDERID = $row['payment_id'];
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: im getting a 0 value when i need a correct order ID

Post by jonnyfortis »

Weirdan wrote:You're using mysql_insert_id() after a select query. Probably you want to replace that line with

Code: Select all

 // $ORDERID = mysql_insert_id();
 $ORDERID = $row['payment_id'];
i will try that but in the meantime i made another query below the others

Code: Select all

mysql_select_db($database_hostprop, $hostprop);
$query_rsOrders = "SELECT host_payments2014.payment_id FROM host_payments2014, plus_signupComplete WHERE host_payments2014.id = plus_signupComplete.studentID AND host_payments2014.payment_type = $payment_type AND host_payments2014.payment_amount_due = $amount_due";
$rsOrders = mysql_query($query_rsOrders, $hostprop) or die(mysql_error());
$row_rsOrders = mysql_fetch_assoc($rsOrders);
$totalRows_rsOrders = mysql_num_rows($rsOrders);

and echoed out $row_rsOrders['payment_id'];

and that seemed to work
Post Reply