Member Avatar for John_K

Hi Everyone I'm hoping someone could help me out please. I have a database of names and email addreses with the following columns:
user_id (auto increments)
user_name
user_email
user_email_sent

Basically I have a form (for myself) that returns all the rows in the database and next to each row I have a submit button that when pressed will send out an email to all the users. What I want to do is keep a count of how many times I've sent that particular email which will update the user_email_sent column by incrementing it by one every time I click the button. I've tried everything but the kitchen sink but I can't get my scripts to work. I'm sure I'm almost there but not quite and was wondering if someone could possibly help me out please?

Here's the form that returns all the rows with the submit button:

$query = "SELECT * FROM tbl_users order by user_name asc"; 
	 
$result = mysql_query($query) or die(mysql_error());
echo "<form action='process.php' method='post'>";
echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Email</th> <th>Email Sent </th><th>Send Email</th></tr>";
	
	while($row = mysql_fetch_array($result)){
                $user_id =  $row['user_id'];
                $user_email_sent =  $row['user_email_sent'];
	echo "<tr><td>"; 
	echo $row['user_name'];
	echo "</td><td>"; 
	echo $row['user_email'];
	echo "</td><td>"; 
	echo $row['user_email_sent'];
	echo "</td><td>";
	echo "<input type='submit' value='Send Mail' name='submit'>";
	echo "</td></tr>";
}

echo "</table>";
echo "</form>";

And here is the form processor:

if ($_POST["$submit"])
   {
	$user_id = $_POST['$user_id'];
	$user_email_sent = $_POST['$user_email_sent'];
	

     
      $sql = "UPDATE tbl_users SET user_email_sent = $user_email_sent+1 WHERE user_id=$user_id";

      $result = mysql_query($sql);
      echo "Email Sent.";
   }

Can anyone see where I'm going wrong? I haven't added the mail function yet, I just wanted to get the incrementing right first.

Any help would be greatly appreciated. Thanks!

  • 5 Contributors
  • 7 Replies
  • 98 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by hielo
Member Avatar for moerpheus

try this:

user_email_sent =user_email_sent+1;

instead of :

user_email_sent = $user_email_sent+1
Member Avatar for GreenDay2001

Modify SQL Query to:

$sql = "UPDATE tbl_users SET user_email_sent = $user_email_sent+1 WHERE user_id='$user_id'";
Member Avatar for moerpheus

Modify SQL Query to:

$sql = "UPDATE tbl_users SET user_email_sent = $user_email_sent+1 WHERE user_id='$user_id'";
$sql = "UPDATE tbl_users SET user_email_sent = user_email_sent+1 WHERE user_id={$user_id}";

if user_id is not string do not use ('$user_id')..

Member Avatar for hemgoyal_1990

Change Your Code with this:

$query = "SELECT * FROM tbl_users order by user_name asc"; 
 
$result = mysql_query($query) or die(mysql_error());
echo "<form action='process.php' method='post'>";
echo "<table border='1'>";
echo "<tr> <th>Name</th> <th>Email</th> <th>Email Sent </th><th>Send Email</th></tr>";
 
	while($row = mysql_fetch_array($result)){
                $user_id =  $row['user_id'];
                $user_email_sent =  $row['user_email_sent'];
	echo "<tr><td>"; 
	echo $row['user_name'];
	echo "</td><td>"; 
	echo $row['user_email'];
	echo "</td><td>"; 
	echo $row['user_email_sent'];
	echo "</td><td>";
	echo "<input type=hidden name=user_id value=$user_id><input type=hidden name=user_email_sent value=$user_email_sent><input type='submit' value='Send Mail' name='submit'>";
	echo "</td></tr>";
}
 
echo "</table>";
echo "</form>";
if (isset($_POST['submit']))
   {
	$user_id = $_POST['user_id'];
	$user_email_sent = $_POST['user_email_sent'];
        $email_counter = $user_email_sent + 1;
 
 
      $sql = "UPDATE tbl_users SET user_email_sent = $email_counter WHERE user_id=$user_id";
 
      $result = mysql_query($sql);
      echo "Email Sent.";
   }
Member Avatar for John_K

Hi Everyone thanks a ton for all your answers. So far the only answer that seems to remotely work is the one above, however only after I move the curly brace from here:

}
 
echo "</table>";
echo "</form>";

to here:

echo "</table>";
echo "</form>";}

The problem comes when you view the form that only one row falls with in the table, and the other entries are all written on one long line.

When I move the curly brace like so the code actually increments the column correctly, but obviously I'd like all rows retuned within the table on the form page. Is it something simple?

Member Avatar for hemgoyal_1990

hay John_K

have your problem solved?
or your getting any new error?

Member Avatar for hielo

and next to each row I have a submit button that when pressed will send out an email to all the users

Did you mean that if you click on the "Send Mail" button for a row, an email should be sent only for the person on that row? What you wrote leads me to believe that no matter which row button I click, EVERYBODY will be emailed! If this is what you actually intended, then why not use a single button?

My guess is you meant "Email only the person in the current row", in which case what you need:
a. ONE form per row since each different row will send an email just for that particular person.

b. In addition to the button, each row/form needs to send/pass a unique identifier via a hidden field so you know which person you are trying to email. From what you posted, user_id IS unique, so that is enough.

c. In process.php your update query just needs to take the current value stored in the `user_email_sent` and add one to it. You do NOT need to update it from some php variable. You can do it directly via sql

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
<title>Hielo</title>
</head>
<body>
<?php
//connect to the db and select the db here first
//...

$query = "SELECT * FROM `tbl_users` order by `user_name` asc";
$result = mysql_query($query) or die(mysql_error());
echo "<table border='1'>";
echo "<tr><th>Name</th><th>Email</th><th>Email Sent</th><th>Send Email</th></tr>";
	
	while($row = mysql_fetch_array($result))
	{
		echo "<tr><td>{$row['user_name']}</td><td>{$row['user_email']}</td><td>{$row['user_email_sent']}</td><td><form action='process.php' method='post'><input type='hidden' name='userid' value='{$row['user_id']}'/><input type='submit' value='Send Mail' name='Submit' /></form></td></tr>";
	}
echo "</table>";

?>
</body>
</html>

process.php
<?php
if ( isset($_POST["Submit"]) )
{
	//connect to the db and select the db here first
	//...

	$user_id = mysql_real_escape_string($_POST['user_id']);
     
      $sql = "UPDATE `tbl_users` SET `user_email_sent` = (`user_email_sent` + 1) WHERE `user_id`=$user_id";

      $result = mysql_query($sql) or die( mysql_error() );
      echo "Email Sent.";
}
?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.