Page 1 of 1

You have an error in your SQL syntax; ??? help!

Posted: Sun Mar 04, 2007 12:58 pm
by Adamck
Keep getting this error...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET active=1,payment_status='INVALID' WHERE id=''' at line 1
Any reason for this? im running SQL V4.1.21-standard and PHP V4.4.4

Please help!!!

Code: Select all

<?php
## v5.24 -> apr. 06, 2006
session_start();

include_once ("config/config.php");
include ("config/lang/$setts[default_lang]/site.lang");

## overwrite $setts['payment gateway']
$setts['payment_gateway']="Paypal";

// read the post from PayPal system and add 'cmd'
$req = 'cmd=_notify-validate';

foreach ($_POST as $key => $value) {
  $value = urlencode(stripslashes($value));
  $req .= "&$key=$value";
}

// post back to PayPal system to validate
$header .= "POST /cgi-bin/webscr HTTP/1.0\r\n";
$header .= "Content-Type: application/x-www-form-urlencoded\r\n";
$header .= 'Content-Length: ' . strlen($req) . "\r\n\r\n";
$fp = fsockopen ('www.paypal.com', 80, $errno, $errstr, 30);

// assign posted variables to local variables
// note: additional IPN variables also available -- see IPN documentation
//$item_name = $_POST['item_name'];
$receiver_email = $_POST['receiver_email'];
$item_number = $_POST['item_number'];
$invoice = $_POST['invoice'];
$payment_status = $_POST['payment_status'];
$payment_gross = $_POST['mc_gross'];
$payment_currency = $_POST['mc_currency'];
$txn_id = $_POST['txn_id'];
$payer_email = $_POST['payer_email'];
$custom = $_POST['custom'];
$payment_date = $_POST['payment_date'];
$pending_reason = $_POST['pending_reason'];

$currentTime=time();

if ($_REQUEST['table']==1) $_SESSION['table_to_modify']="probid_users";
if ($_REQUEST['table']==2) $_SESSION['table_to_modify']="probid_auctions";
if ($_REQUEST['table']==3) $_SESSION['table_to_modify']="probid_winners";
if ($_REQUEST['table']==4) $theoption=2;

if ($_REQUEST['table']==7) $_SESSION['table_to_modify']="probid_wanted_ads";

if (!$fp) {
  	// ERROR
  	echo "$errstr ($errno)";
} else {
  	fputs ($fp, $header . $req);
  	while (!feof($fp)) {
   	 	$res = fgets ($fp, 1024);
    	if (strcmp ($res, "VERIFIED") == 0) {
		// echo "WE ARE IN THE VERIFIED CYCLE<br>";
	  		if ($payment_status == "Completed"){
				$isTxnId = FALSE;
				$getTxn = getSqlNumber("SELECT * FROM probid_txn_ids WHERE txnid='".$txn_id."' AND processor='".$setts['payment_gateway']."'");
				if ($getTxn) $isTxnId = TRUE;
				
				## only do all this if there is no txn yet.
				if (!$isTxnId) { 
					$addTxn = mysql_query("INSERT INTO probid_txn_ids (txnid, processor) VALUES ('".$txn_id."','".$setts['payment_gateway']."')");
			
					#-------------------------------------------
					if ($_REQUEST['table'] == 101) {
						$pDetails = explode(";",$custom);
						for ($i=0; $i<count($pDetails); $i++) {
							$dets = explode("_",$pDetails);
							//mysql_query("UPDATE probid_auctions SET paidwithdirectpayment='1' WHERE id='".$pDetails[0]."'");
							mysql_query("UPDATE probid_winners SET flag_paid=1, directpayment_paid=1
							WHERE buyerid='".$pDetails[1]."' AND auctionid='".$pDetails[0]."'");
						}
					} 
					if ($_REQUEST['table'] == 100) {
						//$pDetails = explode("_",$custom);
						//mysql_query("UPDATE probid_auctions SET	paidwithdirectpayment='1' WHERE id='".$pDetails[0]."'");
						/*
						mysql_query("UPDATE probid_winners SET flag_paid=1, directpayment_paid=1
						WHERE  buyerid='".$pDetails[1]."' AND auctionid='".$pDetails[0]."'");
						*/
						mysql_query("UPDATE probid_winners SET flag_paid=1, directpayment_paid=1 WHERE  id='".$custom."'");
					} 
					if ($theoption==2) {
						$_SESSION['accsusp']=0;
						$currentBalance = getSqlField("SELECT balance FROM probid_users WHERE id='".$custom."'","balance");
						$updatedBalance = (-1) * $currentBalance;
						if ($updatedBalance<=0) {
							$_SESSION['accsusp']=0;
						}
						## if user is suspended, activate the counters
						$userDCat = getSqlRow("SELECT id, active FROM probid_users WHERE id='".$custom."'");
						if ($userDCat['active'] == 0) counterAddUser($userDCat['id']);
	
						$currentTime = time();
						$updateUser = mysql_query("UPDATE probid_users SET 
						active='1', payment_status='confirmed', balance='0' WHERE id='".$custom."'");
						$updateAuction = mysql_query("UPDATE probid_auctions SET 
						active='1' WHERE ownerid='".$custom."'");
						$updateWantedAd = mysql_query("UPDATE probid_wanted_ads SET 
						active='1' WHERE ownerid='".$custom."'");
						$insertInvoice = mysql_query("INSERT INTO probid_invoices 
						(userid,feename,feevalue,feedate,balance,transtype,processor) VALUES 
						('".$custom."','".$lang[payment_fee]."','".$payment_gross."','".$currentTime."','0','payment','".$setts['payment_gateway']."')");
					} else {
						if ($_REQUEST['table']==3) {
							$updateTable = mysql_query("UPDATE ".$_SESSION['table_to_modify']." SET 
							active = '1',payment_status='confirmed',amountpaid='".$payment_gross."',paymentdate='".$currentTime."',
							txnid='".$txn_id."',processor='".$setts['payment_gateway']."' WHERE auctionid='".$custom."'");
						} else if ($_REQUEST['table']==8) {
							$updateTable = mysql_query("UPDATE probid_users SET
							store_active='1', store_lastpayment='".$currentTime."' WHERE id='".$custom."'") or die(mysql_error());
							## now we submit the accounting details
							$addAccounting = mysql_query("INSERT INTO probid_stores_accounting
							(userid, amountpaid, paymentdate, processor) VALUES
							('".$custom."', '".$payment_gross."', '".$currentTime."', '".$setts['payment_gateway']."')") or die(mysql_error());
						} else {
							## if we activate the users table, count all auctions
							if ($_REQUEST['table']==1) counterAddUser($custom);
	
							$updateTable = mysql_query("UPDATE ".$_SESSION['table_to_modify']." SET 
							active = '1',payment_status='confirmed',
							amountpaid='".$payment_gross."',paymentdate='".$currentTime."',
							processor='".$setts['payment_gateway']."' WHERE id='".$custom."'");
							## if we activate the auctions table, count the auction
							if ($_REQUEST['table']==2) {
								$auctCat = getSqlRow("SELECT category, addlcategory, closed, active, deleted FROM probid_auctions WHERE id='".$custom."'");
								if ($auctCat['closed']==0&&$auctCat['active']==1&&$auctCat['deleted']!=1) {
									addcatcount ($auctCat['category']);
									addcatcount ($auctCat['addlcategory']);
								}
							}
							if ($_REQUEST['table']==7) {
								$wantedCat = getSqlRow("SELECT category, addlcategory, closed, active, deleted FROM probid_wanted_ads WHERE id='".$custom."'");
								if ($wantedCat['closed']==0&&$wantedCat['active']==1&&$wantedCat['deleted']!=1) {
									addwantedcount ($wantedCat['category']);
									addwantedcount ($wantedCat['addlcategory']);
								}
							}
						}
					}
					#-------------------------------------------
				}
			} else {
				if ($_REQUEST['table'] != 100) {
					if ($_REQUEST['table']==3) $query2 = "UPDATE ".$_SESSION['table_to_modify']." SET active = '0',payment_status='".$payment_status."' WHERE auctionid='".$custom."'";
					else $query2 = "UPDATE ".$_SESSION['table_to_modify']." SET active = '0',payment_status='".$payment_status."' WHERE id='".$custom."'";
					$result2=mysql_query ($query2) or die(mysql_error());
				}
			}
      	} else if (strcmp ($res, "INVALID") == 0) {
			if ($_REQUEST['table'] != 100) {
		    	if ($_REQUEST['table']==3) $query6 = "UPDATE ".$_SESSION['table_to_modify']." SET active = '0',payment_status='INVALID' WHERE auctionid='".$custom."'";
				else $query6 = "UPDATE ".$_SESSION['table_to_modify']." SET active=0,payment_status='INVALID' WHERE id='".$custom."'";
				$result6=mysql_query ($query6) or die(mysql_error());
			}
      	}
  	}
  	fclose ($fp);
}
?>

Posted: Sun Mar 04, 2007 1:07 pm
by acpbrian
Usually if your field in MySQL is an INT then you do not need ' ' around the value. This has caused issues for me in the past. Hope this helps..

Particularly from active=

I saw you use ' ' one time and not the other. Try not using them at all.

Posted: Sun Mar 04, 2007 1:09 pm
by impulse()
I don't think $custom has a value.

Posted: Sun Mar 04, 2007 1:26 pm
by Adamck
how do i sort out the $custom value thing then?
The file is part of phpprobid and isnt written by me.
I see alot of their members have this problem but nobody seems to have a fix :(
Ad.

Posted: Sun Mar 04, 2007 1:47 pm
by Adamck
makes me laugh knowing that even phpprobid has a problem with their own auction
http://www.phpprobid.com/skinreview/paymentprocess.php
Ad.

Re: You have an error in your SQL syntax; ??? help!

Posted: Sun Mar 04, 2007 2:38 pm
by impulse()
Try putting $custom = <a valid member id here>; BEFORE the SQL query.

Reason I say this is because the error states:
WHERE id='''

Posted: Sun Mar 04, 2007 3:28 pm
by Adamck
where abouts.
I have tried adding "1" in a few areas with no luck!
There is only 3 users with ID's 1,2 and 3
and no current auctions.
Thanks for your reply too.
Ad.

Re: You have an error in your SQL syntax; ??? help!

Posted: Sun Mar 04, 2007 5:57 pm
by volka
Adamck wrote:Keep getting this error...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET active=1,payment_status='INVALID' WHERE id=''' at line 1
The code snippet you've posted contains no query that updates a record to active='1' and payment_status='INVALID'

mysql complains about a syntax error, i.e. wrong sql. Therefore WHERE id='' isn't the issue. It might be useless and semantically wrong but it does not cause a syntax error.
I guess your sql statement is missing the table identifier. There's probably another query like
$updateTable = mysql_query("UPDATE ".$_SESSION['table_to_modify']." SET
active = '1',payment_status='confirmed',amountpaid='".$payment_gross."',paymentdate='".$currentTime."',
txnid='".$txn_id."',processor='".$setts['payment_gateway']."' WHERE auctionid='".$custom."'");
in your script. If there is no $_SESSION['table_to_modify'] the query looks like UPDATE SET active = '1',payment_status='confirmed' ... causing exactly the kind of syntax error message you got.
In case a mysql error occurs print the query along with the error message.