/    Sign up×
Community /Pin to ProfileBookmark

MySQL Insert Query

Copy linkTweet thisAlerts:
Oct 12.2006

I have set up IPN with paypal to get the variables put into a mysql database but I would like to take one of those variables and add it to another table.
What I have currently is below. It is supposed to check to see if the email address is already in the other table and then if it’s not, it should add it. Can anyone see a problem with the SQL or PHP as it’s not adding the email address to the 2nd table.

[code=php]//insert email into members database
//create MySQL connection
$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());

$sql1 = ‘SELECT * FROM members WHERE emailaddress = $payer_email’;
$result1 = mysql_query($sql1);
if(!$result1){
$sql2 = “INSERT INTO members(emailaddress) VALUES (‘” . $payer_email . “‘)”;
$result2 = mysql_query($sql2);
}[/code]

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@DoppleauthorOct 12.2006 — It's cool. That's it sorted. Changed it to
[code=php]//insert email into members database
//create MySQL connection
$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());

$sql1 = "SELECT * FROM members WHERE emailaddress = '".$payer_email."'";
$result1 = mysql_query($sql1);

if(!mysql_num_rows($result1))
{
$sql2 = "INSERT INTO members(emailaddress) VALUES ('" . $payer_email . "')";
$result2 = mysql_query($sql2);
}[/code]
Copy linkTweet thisAlerts:
@neilemrichOct 12.2006 — on the first sql query you should have inverted commas around the email address and you are currently accessing it as a litteral string '$payer_email'

i.e.

[CODE]$sql1 = "SELECT * FROM members WHERE emailaddress = '".$payer_email.";"';[/CODE]

and you might need a space in the second query between 'members' and '(emailaddress)'

Not sure if this will work but give it a go
Copy linkTweet thisAlerts:
@bokehOct 12.2006 — It's cool. That's it sorted.[/QUOTE]Ok, but it's still wrong. Make the [I]emailaddress[/I] column UNIQUE and just run the INSERT query. Drop the SELECT query altogether.
Copy linkTweet thisAlerts:
@DoppleauthorOct 12.2006 — Ha ha. I can always rely on you to point out the obvious flaws in my coding bokeh! I take it if it is already in there, it just won't insert the email? Can I make more than 2 fields UNIQUE? I also have a mobile number field which must also be unique.

I'm currently learning SQL accessing oracle as part of my college degree so hopefully in a few months time I won't be making errors like this (and I'll also be a better database programmer full stop!)

Thanks

Graham
Copy linkTweet thisAlerts:
@bokehOct 12.2006 — I take it if it is already in there[/QUOTE]That's correct. There is also ON DUPLICATE KEY syntax which can be appended to tthe query to carry out other actions if there is a duplication. Can I make more than 2 fields UNIQUE? I also have a mobile number field which must also be unique.[/QUOTE]Yes!

I also believe it is possible to make a UNIQUE pair so the query fails only when both are duplicated. Maybe someone else can confirm this.
Copy linkTweet thisAlerts:
@neilemrichOct 12.2006 — You can to make a unique key from multiple fields, I often do
×

Success!

Help @Dopple spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.19,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...