Almost There - Creation of activation

mrdee

Nov 2 2007, 07:52 PM

I got my mail() function to work on my local machine, thanks to jhaslip's excellent help. (Sincere thanks for that).
Now, I am one step away from completing a script to let it do exactly what I want.

I already have a page where people can fill in a form to subscribe to a newsletter, and they get an email to confirm they have subscribed.
However, i would like to be able to put a link in there, when they click on it it sends them to a webpage to tell them their subscription is now active.
To achieve that, i have added one field to the table called 'active', with an array type of int(1) and set to a default of 0.

The only thing I want now is that when people click the link, it sets the value of 'active' to 1 and takes them to a page confirming this has happened and their subscription is now active.

Unfortunately, I am not 100% sure of how to set up the link (probably something like "http://www.example.com/confirm.php?something) or the code to change the 'active' value for that particular record to 1.

BTW my table 'newsletter' looks like: id int(1) auto_increment primary index, first Varchar(30), last varchar(30), town varchar(30), country varchar(30), email varchar(60), active int(1) default 0.
All fields are NOT NULL.

Any suggestions, please?

Thanks in advance.

jlhaslip

Nov 2 2007, 10:41 PM

CODE

http://www.example.com/confirm.php?something)

The "something" would be their Registration id so you know who is submitting the confirmation.

CODE

http://www.example.com/confirm.php?confirm_id=9806

Then have the confirm.php use the confirm_id value of the query string to find the member by id and alter the confirm code to 'true'

mrdee

Nov 3 2007, 12:56 AM

Thanks, but there are weird things going on.
This is my listing for signing in:

CODE

<HTML>
<HEAD>
<TITLE>Vlaanderen-Flanders</TITLE>
</HEAD>
<BODY>
<?php
$con = mysql_connect("localhost","root","********");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$First      = trim(addslashes(strip_tags($_POST['First'])));
$Name    = trim(addslashes(strip_tags($_POST['Name'])));
$Town     = trim(addslashes(strip_tags($_POST['Town'])));
$Country = trim(addslashes(strip_tags($_POST['Country'])));
$Email     = trim(addslashes(strip_tags($_POST['Email'])));
mysql_select_db("tester", $con);
mysql_query("INSERT INTO newsletter VALUES ('','$First','$Name','$Town','$Country','$Email')") or die(mysql_error());
mysql_close($con);
$email_address_to = "$Email";
$site = "http://www.vlaanderen-flanders.org.uk";
$subject = "Thank you";
$message_contents = "Hello, $First, Thank you for subscribing to our newsletter.\n Click the link below to confirm your subscription:\n http://localhost/home/confirm.php?confirm_id=9806 \n Kind regards,\n The webmaster.\n $site\n";
$header = "From: webbie@vlaanderen-flanders.org.uk\r\n";
$header .= "Reply-To: webbie@vlaanderen-flanders.org.uk\r\n";
$header .= "webbie@vlaanderen-flanders.org.uk\r\n";
mail($email_address_to,$subject,$message_contents,$header);
?>
<div align="center"><img src="Pics/Vlaamse Leeuw.jpg" width="114" height="127" alt="" border="0" align=""></div>
<p align="center"><b>Thank you, you have subscribed and you will receive a confirmation email soon.</b></p><br>
<p align="center"><a href="index.htm"><img src="Pics/begin.gif" width="95" height="30" border="0"></a></p>
</BODY>
</HTML>

This worked before for subscribing, now I am getting : 'Column count doesn't match value count at row 1' from this PHP page.
If that is relevant, the first field is simply called 'id', and is int(6) with auto_increment.
Also, to get to that page, a form has been filled in on a HTML page, the following fields were submitted: First, Name, Town, Country, Email.

Thanks for all the help so far.

jlhaslip

Nov 3 2007, 01:24 AM

CODE

mysql_query("INSERT INTO newsletter VALUES ('','$First','$Name','$Town','$Country','$Email')

Count the number of fields you are INSERTing, and compare it to the number of fields in the Table.
I think you might've forgot to INSERT a value for the 'active int(1)'.
Add a pair of single quotes, if nothing else, then it should assume the default value of '0' (false).

CODE

mysql_query("INSERT INTO newsletter VALUES ('','$First','$Name','$Town','$Country','$Email', '')

mrdee

Nov 3 2007, 02:12 AM

Sorry to bother you again, but still not getting there with the following in confirm.php:

CODE

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
$con = mysql_connect("localhost","root","root");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("tester", $con);
  $confirm_id=9806;
mysql_query("UPDATE newsletter SET active='1' WHERE $confirm_id='$id'") or die(mysql_error());
mysql_close($con);
echo 'That\'s it!!!';
?>
</body>
</html>

I have had all sorts of errors, from parsing errors to unidentified variables, and the latest one is this: 'Notice: Undefined variable: id in c:\program files\easyphp1-8\home\confirm.php on line 17'.

So, I am obviously still doing something wrong.

Thanks.

mrdee

Nov 4 2007, 01:28 PM

I assume confirm_id is a variable? Is that correct?
And why exactly is the value 9806?
Will that be the value for every confirmation of members?
And what is the relationship of config_id (which holds 9806) to the id field in the database?
That is where I really get stuck, getting that particular record of the person who clicked the confirm link, in order to set his activation to 1 (true).

I really don't see how to do it.
Thanks.

sonesay

Nov 5 2007, 09:48 AM

In your first page you are inserting records into a database

CODE

mysql_query("INSERT INTO newsletter VALUES ('','$First','$Name','$Town','$Country','$Email','')") or die(mysql_error());

from the above code:
VALUES ('' is the key you want to retrive and use to distinguish between each unique user and use it in confirm.php later to set the 'active' field for when users subscribe. So you will need to get this key(id) somehow to be able to use it in confirm.php. i dont think mysql stored it after executing an insert query you may have to do an additional query to retrieve it.

maybe something like(if the key is called id)

CODE

$result = mysql_query("Select id from newsletter where email =". $Email);

while($row = mysql_fetch_array($result)){
 $confirm_id = $row['id'];
}

Then when the key is set you can use it

CODE

"Hello, $First, Thank you for subscribing to our newsletter.\n Click the link below to confirm your subscription:\n http://localhost/home/confirm.php?confirm_id=". $confirm_id." \n Kind regards,\n The webmaster.\n $site\n";

This line here where you do the update is wrong

CODE

mysql_query("UPDATE newsletter SET active='1' WHERE $confirm_id='$id'") or die(mysql_error());

$id not defined and you do not need to because you want to insert into id not assign id to $confirm_id in a sql statement.

so it should be something like this

CODE

mysql_query("UPDATE newsletter SET active='1' WHERE id = '".$confirm_id."'") or die(mysql_error());

mrdee

Nov 7 2007, 12:32 AM

Thanks,but I keep getting errors all over the place.
Ultimately, the code for confirm.php changed to this:

CODE

<HTML>
<HEAD>
<TITLE>Vlaanderen-Flanders</TITLE>
</HEAD>
<BODY>
<?php
$con = mysql_connect("localhost","root","*************");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
$Voornaam = trim(addslashes(strip_tags($_POST['First'])));
$Naam     = trim(addslashes(strip_tags($_POST['Name'])));
$Stad     = trim(addslashes(strip_tags($_POST['Town'])));
$Land     = trim(addslashes(strip_tags($_POST['Country'])));
$Epost    = trim(addslashes(strip_tags($_POST['Email'])));
mysql_select_db("tester", $con);
mysql_query("INSERT INTO newsletter VALUES ('','$First','$Name','$Town','$Country','$Email','')") or die(mysql_error());
$result = mysql_query("Select id from newsletter where Email=('$Email)");
while($row = mysql_fetch_array($result)){
$confirm_id = $row['id'];
}
mysql_close($con);
$email_address_to = "$Email";
$site = "http://www.vlaanderen-flanders.org.uk";
$subject = "Thanks";
$message_contents = "Hello, $First, thank you for subscribing to our newsletter.\n Click the link below to confirm your subscription.\n http://localhost/home/confirm.php?confirm_id='$confirm_id' \n Kind regards,\n The webmaster.\n $site\n";
$header = "From: webbie@vlaanderen-flanders.org.uk\r\n";
$header .= "Reply-To: webbie@vlaanderen-flanders.org.uk\r\n";
$header .= "webbie@vlaanderen-flanders.org.uk\r\n";
mail($email_address_to,$subject,$message_contents,$header);
?>
<div align="center"><img src="Pics/Vlaamse Leeuw.jpg" width="114" height="127" alt="" border="0" align=""></div>
<p align="center"><b>Thank you, you are now subscribed.</b></p><br>
<p align="center"><a href="index.htm"><img src="Pics/begin.gif" width="95" height="30" border="0"></a></p>
</BODY>
</HTML>

I had to change in a few places where you did things like

CODE

$result = mysql_query("Select id from newsletter where email =". $Email);

as i got parse errors and all that.
I think I am losing site of the trees through the woods with all the single and double quotes and parentheses and curly brackets and that.
The last errors I got were those:

Notice: Undefined variable: Email in c:\program files\easyphp1-8\home\confirm.php on line 16

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\program files\easyphp1-8\home\confirm.php on line 18

Notice: Undefined variable: First in c:\program files\easyphp1-8\home\confirm.php on line 22

Notice: Undefined variable: confirm_id in c:\program files\easyphp1-8\home\confirm.php on line 22

Notice: Undefined variable: site in c:\program files\easyphp1-8\home\confirm.php on line 22

Notice: Undefined variable: confirm_id in c:\program files\easyphp1-8\home\confirm.php on line 24

Notice: Undefined variable: id in c:\program files\easyphp1-8\home\confirm.php on line 24
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=''' at line 1

I am getting more and more confused now.
Please try to give me further help.

Thanks in advance.

sonesay

Nov 7 2007, 03:49 AM

ok I'll try help out more. Dinner soon for me so when its done I'll edit this reply.

Edit:

Ok looking through the code you changed alot of names for the variables but didnt change them further down the code when you use them.

CODE

$Voornaam = trim(addslashes(strip_tags($_POST['First'])));
$Naam     = trim(addslashes(strip_tags($_POST['Name'])));
$Stad     = trim(addslashes(strip_tags($_POST['Town'])));
$Land     = trim(addslashes(strip_tags($_POST['Country'])));
$Epost    = trim(addslashes(strip_tags($_POST['Email'])));
mysql_select_db("tester", $con);
mysql_query("INSERT INTO newsletter VALUES ('','$First','$Name','$Town','$Country','$Email','')") or die(mysql_error());

Change it to this and you wont get those undefined variable errors.

CODE

$First = trim(addslashes(strip_tags($_POST['First'])));
$Name     = trim(addslashes(strip_tags($_POST['Name'])));
$Town     = trim(addslashes(strip_tags($_POST['Town'])));
$Country     = trim(addslashes(strip_tags($_POST['Country'])));
$Email    = trim(addslashes(strip_tags($_POST['Email'])));
mysql_select_db("tester", $con);
mysql_query("INSERT INTO newsletter VALUES ('','$First','$Name','$Town','$Country','$Email','')") or die(mysql_error());

The error you get from undefined confirm_id
Notice: Undefined variable: confirm_id in c:\program files\easyphp1-8\home\confirm.php on line 22

is because of $result Query failing. the following code should work.

CODE

$result = mysql_query("Select id from newsletter where Email ='".$Email."'");

ADD REPLY / Got an Opinion!	Remove these ADs!	RAPID SEARCH!	Free Web Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE