PHP MySQL - Check for existing record and redirect according

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

hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

PHP MySQL - Check for existing record and redirect according

Post 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/>
&nbsp;&nbsp;&nbsp;&nbsp;
<input type="text" name="email" id="email" size="20" value="Your E-Mail" onfocus="clearText(this.form);" />
<br/>
<span class="style1 style3 style3">
 &nbsp;&nbsp;&nbsp;&nbsp;
<input type="radio" name="action" value="sub" checked="checked" />Subscribe
&nbsp;&nbsp;
<input type="radio" name="action" value="unsub" />Unsubscribe
</span>
<br/>
&nbsp;&nbsp;&nbsp;&nbsp;
<input type="submit" name="submit" id="submit" value="Submit" onclick="return checkEmail(this.form);" />
</form>
 
 
Thank you for looking :-)
User avatar
idevlin
Forum Commoner
Posts: 78
Joined: Tue Jun 26, 2007 1:10 pm
Location: Cambridge, UK

Re: PHP MySQL - Check for existing record and redirect according

Post 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.
hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: PHP MySQL - Check for existing record and redirect according

Post by onion2k »

Show us one of your failed attempts and we'll help you fix it.
veeresh
Forum Newbie
Posts: 8
Joined: Sat Jul 26, 2008 1:37 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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);
}
}
}
?>
hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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);
      }
    }
}
 
?>
 
 
hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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?
veeresh
Forum Newbie
Posts: 8
Joined: Sat Jul 26, 2008 1:37 am

Re: PHP MySQL - Check for existing record and redirect according

Post by veeresh »

use trim function and check trim($_POST["email"]")
so that it removes spaces in email adress
hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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?
veeresh
Forum Newbie
Posts: 8
Joined: Sat Jul 26, 2008 1:37 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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);
veeresh
Forum Newbie
Posts: 8
Joined: Sat Jul 26, 2008 1:37 am

Re: PHP MySQL - Check for existing record and redirect according

Post by veeresh »

or

$eml=trim($_POST["email"]);
$sql = "select * from table where email_addresses='".$eml." ";
$res = mysqli_num_rows($mysql, $sql);
hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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?
veeresh
Forum Newbie
Posts: 8
Joined: Sat Jul 26, 2008 1:37 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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
hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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);
}
}
}
hairytea
Forum Commoner
Posts: 92
Joined: Mon Feb 04, 2008 8:31 am

Re: PHP MySQL - Check for existing record and redirect according

Post 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?
Post Reply