PHP Forum

**gh05** · 05:26 PM

Can anyone can tell me where I'm going wrong? I had an script which added records to my database and I'm trying to add some checking to it so that an email address can't be entered twice.

It does add records to the database but doesn't stop me from entering the same email twice and it also gives this error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in addsignatures2.php on line 32

If I've left out something obvious then it's because I'm trying to use a tutorial but I'm not sure if it's complete. Thanks.

PHP Code:


			
<html>
<head></head>
<body>
<?php

#connect to mySQL
$conn = mysql_connect("localhost", "_______", "_______") or die("Error connecting to mysql");
#select the database
$rs = @mysql_select_db("fightpetition", $conn)
or die ("could not connect");

#check to see if user already exists

if($_POST['submit']) {
$sql = "SELECT email AS email FROM signatures";
$sql .= "WHERE email='".mysql_real_escape_string($_POST['email'])."' ";
$sql .= "LIMIT 0,1";

$result = mysql_query($sql);
}
#Get the resource ID of this query connection as $result.
if(($row = mysql_fetch_assoc($result)) == True)
{
# Allows error suppression and validation in one shot
/* This record already exists in the database, and its accessible in $row['email'] So now you can do as you please. For example: */
echo "The user already exists.";
} else {
 
#retrieve variables
  $fullname = $_POST['name'] ;
  $email = $_POST['email'] ;
  $comments = $_POST['comments'] ;

#create the sql query
$sql="insert into signatures (fullname, email, comments) values (\"$fullname\", \"$email\", \"$comments\")";
$rs = @mysql_query($sql, $conn)
or die ("Could not execute SQL query");
#confirm entry and display link to view signatures
if ($rs)
{ $msg = "Thank You, your entry has been saved.";
  $msg.= "<a href=\"viewsignatures.php\">";
  $msg.= "View Signatures</a>";
}
 
 
#write the page
echo($msg);
}

?>
</body></html>

**JeremyMiller** · 01:12 PM

I'd use something like this:

PHP Code:


			
<html>
<head></head>
<body>
<?php
$msg = '';
// Establish MySQL connection
$conn = mysql_connect("localhost", "_______", "_______");
if (!$conn) {
  $msg = '['.__LINE__.'] Our site is temporarily unavailable.  Please contact us for additional assistance.';
}
// Connect to database
$rs = mysql_select_db("fightpetition", $conn);
if (!$rs) {
  $msg = '['.__LINE__.'] Our site is temporarily unavailable.  Please contact us for additional assistance.';
}

// check to see if user already exists

if($_POST['submit'] && strlen($msg) > 0) {
  $sql = "SELECT '' FROM signatures";
  $sql .= " WHERE email LIKE '".mysql_real_escape_string(trim($_POST['email']))."' ";
  $sql .= " LIMIT 0,1";

  $result = mysql_query($sql, $conn);

  // Check for whether or not the query returns a result -- a result implies that the email was found
  if(!$result || mysql_num_rows($result) > 0) {
    // Email address already exists, so inform user
    $msg = 'Your email address is already registered.  Please try our forgot password feature to login to your account.';
  } else {
    // Sanitize variables
    $fullname = mysql_real_escape_string(trim($_POST['name']));
    $email = mysql_real_escape_string(trim($_POST['email']));
    $comments = mysql_real_escape_string(trim($_POST['comments']));

    // Create the SQL insert query
    $sql = "INSERT INTO signatures (fullname, email, comments) VALUES ('". $fullname. "', '". $email ."', '". $comments . "')";
    $rs = @mysql_query($sql, $conn);
    
    // Check for errors
    if (mysql_errno($rs) == 0) {
      // Insert was successful, so display success message
      $msg = 'Thank You, your entry has been saved.';
      $msg.= '<a href="viewsignatures.php">View Signatures</a>';
    } else {
      // An error occurred, so inform the user politely
      $msg = 'There was an error processing your registration.  Please forward the following message to support@ourdomain.com and we\'ll be happy to help:';
      $msg .= '<br>'.mysql_error($conn);
    }
  }
}
echo $msg;
?>
</body></html>

**gh05** · 06-16-2010, 03:47 AM

Thanks for the reply Jeremy.

Whe I try that code and press submit on the form it is just taking me to a blank screen but there is no error message. Have checked the database and it's not entering anything.

Any ideas?

Thanks again

**JeremyMiller** · 06-16-2010, 01:13 PM

I'd have to see the form you're submitting from to see what's going on (or a echo '<pre>', print_r($_POST, true), '</pre>'; as the first line of the PHP).

Also, I made 2 changes to the PHP as noted above (nothing significant, just a little cleaning up).

**tripy** · 06-16-2010, 04:16 PM

Or you could use a INSERT ... SELECT query with a WHERE NOT EXISTS clause.

Code:

INSERT INTO signatures (fullname, email, comments)
SELECT '$fullname','$email','$comments'
WHERE NOT EXISTS (
  SELECT 1
  FROM signatures
  WHERE email='$email'
)

**gh05** · 06-16-2010, 05:54 PM

Quote:

Originally Posted by JeremyMiller

I'd have to see the form you're submitting from to see what's going on (or a echo '<pre>', print_r($_POST, true), '</pre>'; as the first line of the PHP).

Also, I made 2 changes to the PHP as noted above (nothing significant, just a little cleaning up).

Hi,

The form just simply posts id, email and comments to the php file (code below). I tried adding that line as the first line of php but still just get a blank screen. (Do you mean after the initial "<?" ?) I've had this blank screen a few times when playing around with various scripts and still don't understand why there is no error displayed??

Thanks.

Code:

<table width="400" border="0" align="center" cellpadding="3" cellspacing="0">
<tr>
<td><strong>Add Your Signature</strong></td>
</tr>
</table>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#CCCCCC">
<tr>
<form method="post" action="addsignatures4.php">
<td>
<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#FFFFFF">
<tr>
<td width="117">Full Name</td>
<td width="14">:</td>
<td width="357"><input name="name" type="text" id="name" size="40" /></td>
</tr>
<tr>
<td>Email</td>
<td>:</td>
<td><input name="email" type="text" id="email" size="40" /></td>
</tr>
<tr>
<td valign="top">Comment</td>
<td valign="top">:</td>
<td><input name="comments" type="text" id="comments" size="100" /></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td><input type="submit" name="Submit" value="Submit" /> <input type="reset" name="Submit2" value="Reset" /></td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<table width="400" border="0" align="center" cellpadding="3" cellspacing="0">
<tr>
<td><strong><a href="viewguestbook.php">View Guestbook</a> </strong></td>
</tr>
</table>

**gh05** · 06-16-2010, 05:55 PM

Quote:

Originally Posted by tripy

Or you could use a INSERT ... SELECT query with a WHERE NOT EXISTS clause.

Code:

INSERT INTO signatures (fullname, email, comments)
SELECT '$fullname','$email','$comments'
WHERE NOT EXISTS (
  SELECT 1
  FROM signatures
  WHERE email='$email'
)

Thanks, hadn't thought about doing that. Will try this if can't get it working the other way.

**badams** · 06-16-2010, 08:55 PM

PHP Code:


			
  if(!$result || mysql_num_rows($result) > 0) {
    // Email address already exists, so inform user
    $msg = 'Your email address is already registered.  Please try our forgot password feature to login to your account.';
  } else {

That should be

PHP Code:


			
 if($result && mysql_num_rows($result) > 0) {
    // Email address already exists, so inform user
    $msg = 'Your email address is already registered.  Please try our forgot password feature to login to your account.';
  } else {

**JeremyMiller** · 06-17-2010, 03:01 AM

Nice solution tripy! I hadn't thought of that either.

@badams:

The code I post was actually correct. $result is the return value of mysql_query which returns false on an error or a non-false equivalent value on success. So, !$result would be true only if an error occurred. I then checked for either the query failing (!$result) or some result being returned (indicating that the email address existed) and then returned an error message in either event.

Thanks, though, for checking it! I've been known (as we all have) to make small mistakes before.

**tripy** · 06-17-2010, 03:11 AM

@Jeremy
That's my DBA side. Don't do something out of the db, if you can do it in the db.

**gh05** · 06-17-2010, 05:46 AM

Although Jeremy says that his code is correct I tried your code badams and get an "unexpected $end" error. I'm not sure which is better...a blank screen or an unexpected end?? Either way I'm not sure what's going on with it.

**JeremyMiller** · 06-17-2010, 04:41 PM

The code on that line was correct, but the code on another line was incorrect in 2 ways:

PHP Code:


			
if($_POST['submit'] && strlen($msg) > 0) {

should be

PHP Code:


			
if($_POST['Submit'] && strlen($msg) == 0) {

Though, I'd more highly recommend

PHP Code:


			
if(isset($_POST['Submit']) && strlen($msg) == 0) {

The 2 errors were that you did not capitalize the "s" in Submit for the $_POST array and I had strlen($msg) > 0 instead of == 0.

As a side tip, it's generally not good practice to name your form fields "submit" -- it oftentimes conflicts with javascript that looks for the submit method.

**gh05** · 06-17-2010, 05:33 PM

Thanks! All working great.

Only thing now is it is giving a warning:

Warning: mysql_errno(): supplied argument is not a valid MySQL-Link resource in \addsignatures5.php on line 42

line 42 is:
if (mysql_errno($rs) == 0) {

I've read that it's something to do with the connection code but can't see anything wrong?

**JeremyMiller** · 06-17-2010, 06:04 PM

Again, another small error. Should have been mysql_errno($conn) not with $rs.

**gh05** · 07:53 PM

Thanks again Jeremy.

This isn't really a question about coding but if anyone can help i'd appreciate it: I'm trying to create a basic online petition and I want it to be fairly accurate so that people can only sign up once (hence the email validation). The thing is, at the moment I could still quite easily click 'back' on my browser and sign the petition form again with a slightly different email address and it would be accepted. The only way (I guess?) is to make it a requirement that the user then validates their address by clicking a link which is sent to their email account.

Is this something which is easily done in php? I suppose I'd need to send an automated email to each account and have a boolean type field for whether that user had validated their email? Then, in the 'view signatures' script only signatures which have validated=true are displayed/counted. Is this how it would be done and is it tricky to program?

As you can probably tell, I'm new to this type of web programming.

Thanks.

After thought - Maybe there is another way to do it which is simpler for the user..(recording ip address?)