Incrementing a mysql table

[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!

[moerpheus]

try this:

user_email_sent =user_email_sent+1;

instead of :

user_email_sent = $user_email_sent+1

[GreenDay2001]

Modify SQL Query to:

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

[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')..

[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.";
   }

[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?

[hemgoyal_1990]

hay John_K

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

[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.";
}
?>