[SOLVED] Unknown column in 'where clause' error

mikebyrne · November 28, 2007

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 · November 28, 2007

Try:

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

Orio.

PhaZZed · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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

adam291086 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

Sorry im still a little confused

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

adam291086 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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 · November 28, 2007

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

mikebyrne · November 28, 2007

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 · November 28, 2007

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 · November 29, 2007

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 · November 29, 2007

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 · November 29, 2007

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 · November 29, 2007

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']";

Sign In

[SOLVED] Unknown column in 'where clause' error

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information