...

View Full Version : Resolved checking for duplicate problem



low tech
12-02-2010, 10:45 AM
Hi all

I want to check for duplicates, if exists I write an error BUT

this works


$checkquery = "SELECT user_email FROM users WHERE user_email='".$user_email."'";



BUT I would like to check two areas txn_id, user_email

and when I do this it doesn't work even tho I send a duplicate


$checkquery = "SELECT txn_id, user_email FROM users WHERE

txn_id='".$txn_id."', user_email='".$user_email."'";

what have I done wrong -- logic?


any help very much appreciated
LT

this is what I have by the way

$checkquery = "SELECT user_email FROM users WHERE user_email='".$user_email."'";
$the_result = mysql_query($checkquery, $Connect) or die("Duplicate txn id check query failed:<br>" . mysql_error() . "<br>" . mysql_errno());
$nm = mysql_num_rows($the_result);
if ($nm == 0) { DO SMTHG

abduraooft
12-02-2010, 11:09 AM
What's txn_id?

$the_result = mysql_query($checkquery, $Connect) or die("Duplicate txn id check query failed:<br>" . mysql_error() . "<br>" . mysql_errno());
Don't you get any errors for your query?

low tech
12-02-2010, 11:19 AM
Hi

txn_id is just number eg 017856342

and I don't get error ---- I just deleted my log and tried again to see if it generated a new error log --- but nothing.

I can check one or the other but when I put them both together I get zero.

If I do one and send a duplicate it errors and I get a mail to myself.


LT

Lamped
12-02-2010, 11:48 AM
Two things:

1. echo($checkquery) so we can see the actual SQL being passed to mysql_query.
2. Use AND, not ","
3. Please sanitise your inputs:


$checkquery = "SELECT txn_id, user_email FROM users WHERE

txn_id='".mysql_real_escape_string($txn_id)."' AND user_email='".mysql_real_escape_string($user_email)."'";

low tech
12-02-2010, 12:03 PM
thanks

I tried this but nothing as yet --- and no error log:-(

I think I've put the echo wrong too ---- not sure what that does actually --- where will it echo to? Anyway --- back later --- the misses iz giving me hell hahahhahaa



$checkquery = "SELECT txn_id, user_email FROM users WHERE txn_id='".mysql_real_escape_string($txn_id)."' AND user_email='".mysql_real_escape_string($user_email)."'";
echo($checkquery);
$result = mysql_query($checkquery, $Connect) or die("Duplicate txn id check query failed:<br>" . mysql_error() . "<br>" . mysql_errno());
$nm = mysql_num_rows($result);
if ($nm == 0)

LT

Lamped
12-02-2010, 12:24 PM
If you run the script from a browser, the echo will go to the browser. If you run the script from the shell/terminal/ssh, it will go into the shell/terminal/ssh window. You should be getting *something* displayed from that echo...

low tech
12-02-2010, 01:04 PM
Hi

Ok

the script is an IPN (instant payment notification) script so its just on my server and I send info from paypal (test area)

I tried to access script via webpage --- ie I put address in browser and the page comes up blank (I guess that's expected)
I get an INVALID IPN sent to my mail account.

I have no idea how to run from the shell/terminal/ssh (never had to do it and my knowledge doesn't go that far)

so far I have these errors (but they don't seem to stop the script working)


mysql_free_result(): supplied argument is not a valid MySQL result resource
mysql_close(): supplied argument is not a valid MySQL-Link resource

I guess errors related to here



$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//////////////////////////////////

//check if transaction ID has been processed before
// on error notify
$checkquery = "SELECT txn_id, user_email FROM users WHERE txn_id='".mysql_real_escape_string($txn_id)."' AND user_email='".mysql_real_escape_string($user_email)."'";
echo($checkquery);
$result = mysql_query($checkquery, $Connect) or die("Duplicate txn id check query failed:<br>" . mysql_error() . "<br>" . mysql_errno());
$nm = mysql_num_rows($result);
if ($nm == 0)


I have this at end of my script


mysql_free_result($result);
mysql_close($Connect);
fclose ($fp);

LT

Lamped
12-02-2010, 01:10 PM
Don't worry about mysql_free_result or mysql_close - we're in the modern age of garbage collecting languages, and all the poor coding practices that brings. Go progress! *cough*

IPN: The curse of debuggers everywhere.

Use file_put_contents('debug.txt', $checkquery); and view the debug.txt

low tech
12-02-2010, 01:20 PM
Hi

i've tried putting a log file --- I thought it would write the contents of $checkquery -- but it didn't

echo($checkquery);
fwrite($log, '".$checkquery."');

I got this is log file hahahhahhaa

ipn - Dec 02 2010 12:16:02
".$checkquery."

How do I get it to echo $checkquery into log file???

LT

low tech
12-02-2010, 01:25 PM
Hi

ok


I got this is debug.txt


SELECT txn_id, user_email FROM users WHERE txn_id='017856342' AND user_email='expirydate@paypalsandbox.com'


the email exists in my DB so it should fail and send out mail (but it doesn;t when used together with txn_id)

LT

this is my query


$checkquery = "SELECT txn_id, user_email FROM users WHERE txn_id='".mysql_real_escape_string($txn_id)."' AND user_email='".mysql_real_escape_string($user_email)."'";
echo($checkquery);
//fwrite($log, '".$checkquery."');
file_put_contents('debug.txt', $checkquery);
$result = mysql_query($checkquery, $Connect) or die("Duplicate txn id check query failed:<br>" . mysql_error() . "<br>" . mysql_errno());
$nm = mysql_num_rows($result);
if ($nm == 0)

Lamped
12-02-2010, 01:26 PM
I think you could do with learning a little about quotes. Firstly, a variable doesn't need to be quoted if it's just the variable you want.


fwrite($log, $checkquery);

Secondly, there's a difference between ' and ":


$hello = "there";
$var = '$hello'; // $var is now $hello
$var = "$hello"; // $var is now there

PHP only replaces variables with their contents (variable substitution) with double quotes ("). Single quotes (') are not processed at all (except for \').

If you want to write $checkquery with quotes into the log, do one of these:



// " quoted
fwrite($log, '"'.$checkquery.'"');
fwrite($log, "\"$checkquery\"");
// ' quoted
fwrite($log, "'$checkquery'");
fwrite($log, '\''.$checkquery.'\'');

Lamped
12-02-2010, 01:30 PM
To save a little code and keep it tidy, instead of:


$nm = mysql_num_rows($result);
if ($nm == 0)

Do:


if (!mysql_fetch_array($result)) {

Back to the topic at hand: Do you have phpmyadmin or access to run the mysql client from the terminal? What happens when you run that SQL through one of those?

low tech
12-02-2010, 01:39 PM
Hello

Yeh you are right --- I'm lost with quotes along with the rest of php (pretty hard programming) :-)

I'm no coder for sure hahahhahaa

anyway if ' ' are not processed at all --- why use them???

I'm very new so it's all uphill.

LT
ps
did yo see post #10 I think I posted as you did
forget this I see you did --- i'm out sequence with posting

low tech
12-02-2010, 01:45 PM
Hi


Do you have phpmyadmin or access to run the mysql client from the terminal?

yeh it's my website ----- just no idea how to do that:-(


it's a bit weird that it will work with one or the other but not both, don;t you think?

LT

low tech
12-02-2010, 02:01 PM
Hi Lamped


I wanna thank you for having a go at this with me ---- thanks a lot really


I'll take another crack it tomorrow I think ----- maybe there is another way I can perform the checks ---- sepeperately maybe? I dunno.

LT

late n tired

Lamped
12-02-2010, 02:02 PM
Hi


Do you have phpmyadmin or access to run the mysql client from the terminal?

yeh it's my website ----- just no idea how to do that:-(


it's a bit weird that it will work with one or the other but not both, don;t you think?

LT

Well, do you have phpmyadmin? Do you have shell access?

It is odd, it's why I'd like to see it run directly and see what mysql actually does with it.

As for single quotes ('), I use them 100% of the time. It's mostly down to personal preference. Double quoted text takes longer for PHP to read because it has to do more work with it, and I find 'something '.$variable more readable than "something $variable". You're also less likely to make a silly mistake with single quoted strings. I even go to the extreme lengths of 'something'."\n" to put a new line, but I admit that's borderline OCD and a bit daft.

low tech
12-02-2010, 03:14 PM
Hi


Well, do you have phpmyadmin?

I have phpadmin on server and on my pc --- I have access to both.


Do you have shell access?
I have no idea:-)


LT

Lamped
12-02-2010, 03:19 PM
Well, pop into phpmyadmin, select the right database, click the SQL tab at the top, paste in the SQL and go...

low tech
12-02-2010, 03:40 PM
Hi

Oh ok:-)

done it -- got this --- i think i got it wrong tho:-)



MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0003 sec )
SELECT txn_id, user_email
FROM users
WHERE txn_id = '".mysql_real_escape_string($txn_id)."'
AND user_email = '".mysql_real_escape_string($user_email)."'
LIMIT 0 , 30


LT
I put this in by the way

SELECT txn_id, user_email FROM users WHERE txn_id='".mysql_real_escape_string($txn_id)."' AND user_email='".mysql_real_escape_string($user_email)."'

Lamped
12-02-2010, 03:41 PM
Yeah, I wanted you to take the SQL that was saved into your log :p

low tech
12-02-2010, 03:44 PM
hahhaha

I figured smthg not right hahahah

ok doing it now

......please hold ...... code in progress ....... hahahhahaha

LT

low tech
12-02-2010, 03:48 PM
Hi

Ok

SELECT txn_id, user_email FROM users WHERE txn_id='907856342' AND user_email='expirydate@paypalsandbox.com'


I get (which is what I expect since they are there)


Showing rows 0 - 0 (1 total, Query took 0.0082 sec)
SELECT txn_id, user_email
FROM users
WHERE txn_id = '907856342'
AND user_email = 'expirydate@paypalsandbox.com'
LIMIT 0 , 30

so why does my code not fail like it should or like it does with only one query???

I don't get it

I want to check for either one of them --- or both if they are there (unlikely in real terms)

LT

low tech
12-02-2010, 03:58 PM
Hi Lamped

------------------------------------------------------------------
EDIT
I changed AND to OR and that now seems to work too ----- go figure.
---------------------------------------------------------------------


I think smthg might be working because I have just received five mails in my account ------- duplicated transactions -- as I expect I should get


so hold on -- you may have solved it with the code you provided earlier -- only I've just got the feedback

I'm going to stop now anyway

i'll post tomorrow if resolved

thanks a million for all yr hekp

LT

abduraooft
12-02-2010, 04:53 PM
Now, why do you need to make an extra SELECT query before insert? Just set a UNIQUE index to the columns to prevent duplicates.

Lamped
12-02-2010, 05:09 PM
Now, why do you need to make an extra SELECT query before insert? Just set a UNIQUE index to the columns to prevent duplicates.

>.<

*walks away from keyboard*

*bangs head against wall*

Why didn't I think of that? *sigh*

abduraooft
12-02-2010, 05:21 PM
I was about to post that hint even in my first post, though I thought the OP should get a result as per his/her current logic. :)

low tech
12-03-2010, 02:55 AM
Hi abduraooft


First thanks Lamped ---- you really helped me and i'm grateful:-)

abduraooft

the email part of my DB is unique and so it won't insert a duplicate

What i'm thinking is to check if a person is trying to register with the same email , which they can't do (not allowed) to do. For example one family may have two kids and they both want to register an account but they may try to use one email address ---- in such a case I want to know -- the script should fail and send me a mail so I can contact them directly with issue.

If I don't do some check --- their detail(email) won;t get registered but I also won;t know they tried and a payment MAY have been accepted (by PayPal).

I don't want that.

The situation is unlikely, and maybe my logic is wrong and there is a better way to handle PayPal stuff --- I really don't know:-(

The duplicate txn_id is more or less teh same --- only this time its like the same payment transaction is made twice so I should ignore the scond one ----- but I want o know its been submitted again and so I want to send a mail to myself ---- I don't know what PayPal does if such a thing really occurs but at least I can know my end.

Does that make sense??

Anyway
with everybody's help i'm learning and I'm very appreciative:-)

LT



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum