[SOLVED] Unknown column in 'where clause' error

[mikebyrne]

I get the following error when I click on my confirmation email

 

Unknown column '67ea431d7b9cdba28d88526a243a60ac' in 'where clause'

 

The '67ea431d7b9cdba28d88526a243a60ac' refers to a randomly generated key (passkey) but cant figure out why im getting this error

 

The code i have is:

 

<?php
include('config.php');

// Passkey that got from link
## added here
if (isset($_GET['passkey']))
{
$passkey=$_GET['passkey'];
} 
else
{
echo "Error here :: PASSKEY NOT FOUND ";
}

## table name
$tbl_name="temp_users";

// Retrieve data from table where row matchs this passkey
$sql1="SELECT * FROM $tbl_name WHERE confirm_code={$passkey}";
$result1=mysql_query($sql1) or die(mysql_error());

// If successfully queried
if($result1)
{
// Count how many row has this passkey
$count=mysql_num_rows($result1);
## again addeded here
echo "I HAVE REACHED HERE, RESULT WAS SUCCESSFUL AND TOTAL ROWS  ".$count;
##
// if found the passkey in the database, retrieve data from table "temp_users"
## ARE YOU SURE YOU ARE GETTING ONLY ONE RESULTS 
if($count > 0) // i changed here.........
{
$rows=mysql_fetch_array($result1);
$name=$_POST['name'];
$address=$_POST['address'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$address3=$_POST['address3'];
$address4=$_POST['address4'];
$county=$_POST['county'];
$zip=$_POST['zip'];
$telephone=$_POST['telephone'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];

$tbl_name2="users";

// Insert data that retrieves from "temp_users" into table "users"

$sql2="INSERT INTO `$tbl_name2` (`name`, `address`, `address1`, `address2`, `address3`, `address4`, `county`, `zip`, `telephone`, `email`, `username`, `password`) VALUES ('$name', '$address', '$address1', '$address2','$address3', '$address4','$county' ,'$zip', '$telephone', '$email', '$username', '$password')";
$result2=mysql_query($sql2) or die(mysql_error());
}

// if not found passkey, display message "Wrong Confirmation code"
else {
echo "Wrong Confirmation code";
	 }

// if successfully moved data from table"temp_users" to table "users" displays message "Your account has been activated" and don't forget to delete confirmation code from table "temp_members_db"
if($result2)
{

echo "Your account has been activated";
// Delete information of this user from table "temp_users" that has this passkey
$sql3="DELETE FROM $tbl_name1 WHERE confirm_code = '$passkey'";
$result3=mysql_query($sql3) or die(mysql_error());

}

}
?> 

[Orio]

Try:

 

$sql1="SELECT * FROM $tbl_name WHERE confirm_code='$passkey'";

 

Orio.

[PhaZZed]

I often use this format:

 

$sql1 = "SELECT * FROM $tbl_name WHERE confirm_code=' " .$passkey. " ' ";

 

but Orio's will work! It's all about the quotes

[mikebyrne]

im now getting

 

I HAVE REACHED HERE, RESULT WAS SUCCESSFUL AND TOTAL ROWS 1Your account has been activatedYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE confirm_code = '67ea431d7b9cdba28d88526a243a60ac'' at line 1

 

 

[Orio]

Are you sure it should be $table_name1 and not simply $table_name like in your first query? Because I can't see you defining $table_name1 anywhere.

 

So your last query should be:

DELETE FROM $tbl_name WHERE confirm_code = '$passkey'

 

Orio.

[mikebyrne]

That got rid of the error but I still have a problem.

 

My code doesnt seem to be addeding the new users from the temp_users to the users table. It just adds them as blank fields the only entry is in ID which is the primary key so therefore all I can see is ID 1, Name (blank), Address (blank) etc 

[adam291086]

Have you tired echoing the

 

$name=$_POST['name'];
$address=$_POST['address'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$address3=$_POST['address3'];
$address4=$_POST['address4'];
$county=$_POST['county'];
$zip=$_POST['zip'];
$telephone=$_POST['telephone'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];

 

to see if any variables are being passed?

[mikebyrne]

How do I do that. I'm very new to php (As you can tell!)

[adam291086]

name=$_POST['name'];
$address=$_POST['address'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$address3=$_POST['address3'];
$address4=$_POST['address4'];
$county=$_POST['county'];
$zip=$_POST['zip'];
$telephone=$_POST['telephone'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];
name=$_POST['name'];


echo $address1;
echo $address2;

 

ect ect ect. This will see if the variable you are posting from you form and being carried across.

[mikebyrne]

Here's my output:

 

I HAVE REACHED HERE, RESULT WAS SUCCESSFUL AND TOTAL ROWS 1Your account has been activated

 

from this code:

 

<?php
include('config.php');

// Passkey that got from link
## added here
if (isset($_GET['passkey']))
{
$passkey=$_GET['passkey'];
} 
else
{
echo "Error here :: PASSKEY NOT FOUND ";
}

## table name
$tbl_name="temp_users";

// Retrieve data from table where row matchs this passkey
$sql1="SELECT * FROM $tbl_name WHERE confirm_code='$passkey'";
$result1=mysql_query($sql1) or die(mysql_error());

// If successfully queried
if($result1)
{
// Count how many row has this passkey
$count=mysql_num_rows($result1);
## again addeded here
echo "I HAVE REACHED HERE, RESULT WAS SUCCESSFUL AND TOTAL ROWS  ".$count;
##
// if found the passkey in the database, retrieve data from table "temp_users"
## ARE YOU SURE YOU ARE GETTING ONLY ONE RESULTS 
if($count > 0) // i changed here.........
{
$rows=mysql_fetch_array($result1);

$name=$_POST['name'];
$address=$_POST['address'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$address3=$_POST['address3'];
$address4=$_POST['address4'];
$county=$_POST['county'];
$zip=$_POST['zip'];
$telephone=$_POST['telephone'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];

$tbl_name2="users";

echo $address1;
echo $address2;
echo $address3;
echo $address4;

// Insert data that retrieves from "temp_users" into table "users"

$sql2="INSERT INTO `$tbl_name2` (`name`, `address`, `address1`, `address2`, `address3`, `address4`, `county`, `zip`, `telephone`, `email`, `username`, `password`) VALUES ('$name', '$address', '$address1', '$address2','$address3', '$address4','$county' ,'$zip', '$telephone', '$email', '$username', '$password')";
$result2=mysql_query($sql2) or die(mysql_error());
}

// if not found passkey, display message "Wrong Confirmation code"
else {
echo "Wrong Confirmation code";
	 }

// if successfully moved data from table"temp_users" to table "users" displays message "Your account has been activated" and don't forget to delete confirmation code from table "temp_members_db"
if($result2)
{

echo "Your account has been activated";
// Delete information of this user from table "temp_users" that has this passkey
$sql3="DELETE FROM $tbl_name WHERE confirm_code = '$passkey'";
$result3=mysql_query($sql3) or die(mysql_error());

}

}
?> 

 

So i presume the varibles aren't beeing carried

 

they work fine here though

 

<?php
include('config.php');

// table name
$tbl_name=temp_users;

// Random confirmation code
$confirm_code=md5(uniqid(rand()));

// values sent from form
$name=$_POST['name'];
$address=$_POST['address'];
$address1=$_POST['address1'];
$address2=$_POST['address2'];
$address3=$_POST['address3'];
$address4=$_POST['address4'];
$county=$_POST['county'];
$zip=$_POST['zip'];
$telephone=$_POST['telephone'];
$email=$_POST['email'];
$username=$_POST['username'];
$password=$_POST['password'];


// Insert data into database
$sql="INSERT INTO $tbl_name(confirm_code, name, address, address1, address2, address3, address4, county, zip, telephone, email, username, password)VALUES('$confirm_code', '$name', '$address', '$address1', '$address2','$address3', '$address4','$county' ,'$zip', '$telephone', '$email', '$username', '$password')";
$result=mysql_query($sql)or die(mysql_error());

// if suceesfully inserted data into database, send confirmation link to email
if($result){

// ---------------- SEND MAIL FORM ----------------

// send e-mail to ...
$to=$email;

// Your subject
$subject="Your confirmation link here";

// From
$header="from: postmaster@localhost <postmaster@localhost>";

// Your message
$message="Your Comfirmation link \r\n";
$message.="Click on this link to activate your account \r\n";
$message.="http://localhost/confirm.php?passkey=$confirm_code";

// send email
$sentmail = mail($to,$subject,$message,$header);

}

// if not found
else {
echo "Not found your email in our database";
}

// if your email succesfully sent
if($sentmail){
echo "Your Confirmation link Has Been Sent To Your Email Address.";
}
else {
echo "Cannot send Confirmation link to your e-mail address";
}

?>

[adam291086]

so i take it the varibles are not being echoed. You get the messge I HAVE REACHED HERE, RESULT WAS SUCCESSFUL AND TOTAL ROWS  ".$count;

 

Are you sending the form to the php script. You can't call on the post method unless something is being sent to it. This is where your problem could be.

 

You are posting the registration details to the user. They then recieve an email with a code they need to activate. But you are trying to post result that not longer exist. Hence the reason there is nothing in the database.

 

Here is a suggestion.

 

You already have a script that takes the new users details, puts their info into a database and then sends them an activation link. In the database add a feild like status. Here the status can either be confirmed or not confirmed.

 

When the user gets the email and activates their acount you change the status from not confirmed to confirmed and therefore they can now login.

[mikebyrne]

How would you suggest I code that?

 

I'd idealy like to it happen automaticaly when the users confirms by clicking the link rather that manually selecting yes/no in the confirmation field

[adam291086]

Well when you send them an email it takes them to a php file. In this file you set the php to take the code from the URL using the GET method. Query the code and then if correct then echo welcome and if not redirect them to the register page.

 

edit........ in the activation code add in the users ID from the database and therefore you can change their status feild.

[mikebyrne]

Sorry im still a little confused

 

Would it be possible to show me a little example of what you mean

[adam291086]

ok.. after reading your code throughly instead of skimming you already have this idea set up. Sorry about the confusion but i am at work supposed to be working.

 

First think i have notice is that you need to get the passcode from the url before anything

 

<?php
include('config.php');

$passkey=$_GET['passkey'];

// Passkey that got from link
## added here
if (!isset($_GET['passkey']))

{
echo "Error here :: PASSKEY NOT FOUND ";
}
else
{

## table name
$tbl_name="temp_users";

// Retrieve data from table where row matchs this passkey
$sql1="SELECT * FROM $tbl_name WHERE confirm_code='$passkey'";
$rows=mysql_fetch_array($sql1);

$name=$rows['name'];
$address=$rows['address'];
$address1=$rows['address1'];
$address2=$rows['address2'];
$address3=$rows['address3'];
$address4=$rows['address4'];
$county=$rows['county'];
$zip=$rows['zip'];
$telephone=$rows['telephone'];
$email=$rows['email'];
$username=$rows['username'];
$password=$rows['password'];

$tbl_name2="users";

echo $address1;
echo $address2;
echo $address3;
echo $address4;
}

if($rows) 
{

// Insert data that retrieves from "temp_users" into table "users"

$sql2="INSERT INTO `$tbl_name2` (`name`, `address`, `address1`, `address2`, `address3`, `address4`, `county`, `zip`, `telephone`, `email`, `username`, `password`) VALUES ('$name', '$address', '$address1', '$address2','$address3', '$address4','$county' ,'$zip', '$telephone', '$email', '$username', '$password')";
$result2=mysql_query($sql2) or die(mysql_error());
}

// if not found passkey, display message "Wrong Confirmation code"
else {
echo "Wrong Confirmation code";
	 }
?> 

 

 

I haven't tested this as i am at work. But this should get the activation code from the url. Then query the database on the activation code and set some variable up. Then it update the users table. Get this working and then you can add in the delete bit to delet users out of the temp table. Then you can add in query test. I am new to php to and there is likely to be mistakes.

 

When your new start off small and build up 

 

 

 

edit ...... just added in an else part

[mikebyrne]

I'm getting an error

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\confirm.php on line 22

Wrong Confirmation code

 

 

[adam291086]

Lets start with somthing simple. Then we can move it up.

 

<?php
include('config.php');

$passkey=$_GET['passkey'];

// Passkey that got from link
## added here
if (!isset($_GET['passkey']))

{
echo "Error here :: PASSKEY NOT FOUND ";
}
else
{

## table name
$tbl_name="temp_users";

// after connecting succesfully: 
$query = "SELECT * FROM '$tbl_name'"; 
$result = mysql_query($query); 

// while there is a result, fetch it into an array... 
while ($row = mysql_fetch_array($result)) 
{ 
echo "$row[address]"; 
echo "$row[address1]"; 

} 
}
?> 

 

you should get the database results here for address and address1.

[mikebyrne]

I get the error:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\confirm.php on line 24

[adam291086]

after using google, the table temp_users does exsist right and you have copied it exactly?

[mikebyrne]

Yeah its exits here's the SQL

 

CREATE TABLE `temp_users` (

  `confirm_code` varchar(65) NOT NULL default '',

  `name` varchar(65) NOT NULL default '',

  `address` varchar(65) NOT NULL default '',

  `address1` varchar(65) NOT NULL default '',

  `address2` varchar(65) NOT NULL default '',

  `address3` varchar(65) NOT NULL default '',

  `address4` varchar(65) NOT NULL default '',

  `county` varchar(25) NOT NULL default '',

  `zip` varchar(65) NOT NULL default '',

  `telephone` varchar(25) NOT NULL default '',

  `email` varchar(25) NOT NULL default '',

  `username` varchar(65) NOT NULL default '',

  `password` varchar(15) NOT NULL default ''

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

 

 

[mikebyrne]

I changed your code to

 

<?php
include('config.php');

$passkey=$_GET['confirm_code'];

// Passkey that got from link
## added here
if (!isset($_GET['confirm_code']))

{
echo "Error here :: PASSKEY NOT FOUND ";
}
else
{

## table name
$tbl_name="temp_users";

// after connecting succesfully: 
$query = "SELECT * FROM '$tbl_name'"; 
$result = mysql_query($query); 

// while there is a result, fetch it into an array... 
while ($row = mysql_fetch_array($result)) 
{ 
echo "$row[address]"; 
echo "$row[address1]"; 

} 
}
?> 

 

Now getting the error

 

Error here :: PASSKEY NOT FOUND

[adam291086]

ok the script is starting to work the reason you are getting the error message is because of this

 

$passkey=$_GET['confirm_code'];

 

you are looking for 'confirm_code' in the url. But in you mail script you set the url to passkey. Therfore chage it to

 

$passkey=$_GET['passkey'];

 

 

[mikebyrne]

Im noe getting the error

 

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\confirm.php on line 24

 

<?php

include('config.php');


$passkey=$_GET['passkey'];


// Passkey that got from link

## added here

if (!isset($_GET['passkey']))


{

echo "Error here :: PASSKEY NOT FOUND ";

}

else

{


## table name

$tbl_name="temp_users";


// after connecting succesfully: 

$query = "SELECT * FROM '$tbl_name'"; 

$result = mysql_query($query); 


// while there is a result, fetch it into an array... 

while ($row = mysql_fetch_array($result)) 

{ 

echo "$row[address]"; 

echo "$row[address1]"; 


} 

}

?> 

[/Code]

 

ITS MY BIRTHDAY AND WOULD LOVE TO GET THIS WORKING!!!!!!!   

[adam291086]

we will get this done today. Can you check your database table name temp_users is spelled exactly the same. Also check the fields in the database and ensure all spelling is matched up.

 

Therefore if address1 is like this in the database it needs to be exactly the same in the php script.

 

BTW Happy Birthday

[adam291086]

hey also change

 

$result = mysql_query($query); 

to

$result = mysql_query($query) or die(mysql_error()); 

 

 

also change this

 

echo "$row[address]"; 
echo "$row[address1]"; 

to

echo "$row['address']"; 
echo "$row['address1']";