Page 1 of 2
PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 6:18 am
by hairytea
I have started writing a script for a subscriptions list...i have it working so i inserts the email address into the database etc.
What i need to do is check to see if the email address submitted is in the database already and if so be re-directed to an html error page!
Code: Select all
<?php
$mysqli = mysqli_connect("HIDDEN", "HIDDEN", "HIDDEN", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location: http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
}
}
?>
the html form calling the script...
Code: Select all
<form action="../scripts/insert.php" method="post" name="mailList" class="style3" id="mailList">
<br/><br/>
<input type="text" name="email" id="email" size="20" value="Your E-Mail" onfocus="clearText(this.form);" />
<br/>
<span class="style1 style3 style3">
<input type="radio" name="action" value="sub" checked="checked" />Subscribe
<input type="radio" name="action" value="unsub" />Unsubscribe
</span>
<br/>
<input type="submit" name="submit" id="submit" value="Submit" onclick="return checkEmail(this.form);" />
</form>
Thank you for looking

Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 6:22 am
by idevlin
Before attempting to write the record, perform an appropriate search (using a SELECT statment) on the email address, and if it returns something, then redirect to the error page, else perform your INSERT.
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 6:46 am
by hairytea
thank you,
that is what i want to do.
any ideas how to write this statment as my attempts have all failed?
thanks again
Andrew
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 6:55 am
by onion2k
Show us one of your failed attempts and we'll help you fix it.
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 6:58 am
by veeresh
Veeresh-India
<?php
$mysqli = mysqli_connect("HIDDEN", "HIDDEN", "HIDDEN", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$sql = "select * from table where email_addresses='".$_POST["email"]."' ";
$res = mysqli_fetch_array($mysql, $sql);
if($res['email_addresses']>0)
{
echo 'email already exists';
}
else{
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location:
http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
}
}
}
?>
or
<?php
$mysqli = mysqli_connect("HIDDEN", "HIDDEN", "HIDDEN", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$sql = "select * from table where email_addresses='".$_POST["email"]."' ";
$res = mysql_num_rows($mysql, $sql);
if($res>0)
{
echo 'email already exists';
}
else{
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location:
http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
}
}
}
?>
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:04 am
by hairytea
onion2k wrote:Show us one of your failed attempts and we'll help you fix it.
Code: Select all
<?php
$mysqli = mysqli_connect("localhost", "root", "terrence1", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$mysqli, $check_res;
$check_sql = "SELECT email_addresses FROM emails WHERE email_addresses = ('".$_POST["email"]."')";
$check_res = mysqli_query($mysqli, $check_sql) or die(mysqli_error($mysqli));
if ($check_res == FALSE) {
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location: http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
} else {
header( "Location: http://localhost:8088/networking/htdocs ... cribed.htm" );
mysqli_close($mysqli);
}
}
}
?>
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:13 am
by hairytea
veeresh wrote:Veeresh-India
<?php
$mysqli = mysqli_connect("HIDDEN", "HIDDEN", "HIDDEN", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$sql = "select * from table where email_addresses='".$_POST["email"]."' ";
$res = mysqli_fetch_array($mysql, $sql);
if($res['email_addresses']>0)
{
echo 'email already exists';
}
else{
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location:
http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
}
}
}
?>
or
<?php
$mysqli = mysqli_connect("HIDDEN", "HIDDEN", "HIDDEN", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$sql = "select * from table where email_addresses='".$_POST["email"]."' ";
$res = mysql_num_rows($mysql, $sql);
if($res>0)
{
echo 'email already exists';
}
else{
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location:
http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
}
}
}
?>
Thank you for trying...
I tried both your script attempts and both times it just adds the same email address enetered from the html form and continuosly creates duplicate entries in the sql database and am redirected to the thank you for signing up page?
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:17 am
by veeresh
use trim function and check trim($_POST["email"]")
so that it removes spaces in email adress
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:19 am
by hairytea
veeresh wrote:use trim function and check trim($_POST["email"]")
so that it removes spaces in email adress
OK thank you,
where abouts in the staement should this go?
also, i notice the $res variable is in the script twice doing 2 different things?
and...in phpmyadmin i view the entries in the email_addresses column and they are identical (without spaces) so not that i dont think?
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:23 am
by veeresh
change variable name and
do changes in following
statmt
$sql = "select * from table where email_addresses='".trim($_POST["email"])."' ";
$res = mysqli_num_rows($mysql, $sql);
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:26 am
by veeresh
or
$eml=trim($_POST["email"]);
$sql = "select * from table where email_addresses='".$eml." ";
$res = mysqli_num_rows($mysql, $sql);
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:27 am
by hairytea
ok here is what i have so far (i think i have done everything i need to like change var names etc and all you mentioned)
it still does the same....inputs entry into database and duplicating entries and not being redirected to the error page but to the thanksforsigningup.htm page??
Code: Select all
$mysqli = mysqli_connect("localhost", "root", "terrence1", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$slq = "select * from table where email_addresses='".trim($_POST["email"])."' ";
$ser = mysqli_num_rows($mysql, $slq);
if($ser>0)
{
header( "Location: http://localhost:8088/networking/htdocs ... cribed.htm" );
}
else{
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location: http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
}
}
}
If there is anything obviously wrong here i cant see it....sorry i have only been developing with sql and php for a couple of months now and may be overlooking something here?
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:32 am
by veeresh
$slq = "select * from table where email_addresses='".trim($_POST["email"])."' ";
$ser = mysqli_num_rows($mysql, $slq);
change the table name
<input type="text" name="email" id="email" size="20" value="Your E-Mail" onfocus="clearText(this.form);" />
remove the value="Your E-Mail" and check
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 7:37 am
by hairytea
i removed the value="Your E-Mail" from the html and also put the table name into the sql statment..
thank you again but...it still does the same??
$mysqli = mysqli_connect("localhost", "root", "terrence1", "mailing_list");
if (mysqli_errno($mysqli)) {
printf("Connection Failed: %s\n", mysqli_connect_errno());
exit();
} else {
if (($_POST) && ($_POST["action"] == "sub")) {
$slq = "SELECT * FROM emails WHERE email_addresses='".trim($_POST["email"])."' ";
$ser = mysqli_num_rows($mysql, $slq);
if($ser>0)
{
header( "Location:
http://localhost:8088/networking/htdocs ... cribed.htm" );
}
else{
$sql = "INSERT INTO emails (email_addresses) VALUES ('".$_POST["email"]."')";
$res = mysqli_query($mysqli, $sql);
if ($res === TRUE) {
header( "Location:
http://localhost:8088/networking/htdocs ... ningup.htm" );
} else {
printf("Could not insert record: %s\n", mysqli_errno($mysqli));
}
mysqli_close($mysqli);
}
}
}
Re: PHP MySQL - Check for existing record and redirect according
Posted: Fri Aug 15, 2008 8:10 am
by hairytea
veeresh wrote:$slq = "select * from table where email_addresses='".trim($_POST["email"])."' ";
$ser = mysqli_num_rows($mysql, $slq);
change the table name
<input type="text" name="email" id="email" size="20" value="Your E-Mail" onfocus="clearText(this.form);" />
remove the value="Your E-Mail" and check
I am confused as to why you asked me to remove value="Your E-Mail" from the form?