will insert to table, but can't select (login system)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

will insert to table, but can't select (login system)

Post by NikBruce »

i'm having trouble with my login system.
users create an account like so:

Code: Select all

$ti = "INSERT INTO accTable (name, nameU, profession, company, site, email, word, country)
VALUES ('$accNameSafe', '$accNameUSafe', '$accProfessionSafe', '$accCompanySafe', '$accSiteSafe', '$accEmailSafe', '$accPassSafe', '$accCountrySafe')";
 
mysql_query($ti, $con);
everything inserts fine. but when i try to login like this,

Code: Select all

$sql = "SELECT `email`, `word` FROM `accTable` WHERE `email` = `$email` AND `word` = `$password`";
 
$result = mysql_query($sql) or die(mysql_error());
 
$count = mysql_num_rows($result);
 
if($count == 1)
{ blah blah blah
 
i get a
Unknown column '*my email address*' in 'where clause'
What's going on?

thanks
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: will insert to table, but can't select (login system)

Post by aravona »

where are you getting the $email and $password from? your form?

is that the full error ? (except the changed password)
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

Re: will insert to table, but can't select (login system)

Post by NikBruce »

that is the full error, and the variables are posted from a form.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: will insert to table, but can't select (login system)

Post by aravona »

Having looked at old code of mine doing similar things, I don't have any quotes around my field attributes

Code: Select all

 
$sql = "SELECT email, word FROM accTable WHERE email = `$email` AND word = `$password`";
 
Always try the simplist thing first in my opinion, does taking them out effect your code at all?

(Having done php at uni I spent ages trying to find what my errors were and often it wasnt as complicated as I made out!)

But best to eliminate the obvious :)
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

Re: will insert to table, but can't select (login system)

Post by NikBruce »

that's a fair statement.

but no, the error stays the same.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: will insert to table, but can't select (login system)

Post by aravona »

Ok having a look on the net for similar issues and it seems that when ur selecting your email its taking your actual email address as the column name (as we know) instead of 'email'

So having looked more I found that having the variable in single quotes should fix it, alas not so even tho this seems to work for other people.

Can you show your form ?
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

Re: will insert to table, but can't select (login system)

Post by NikBruce »

Code: Select all

<form name="form1" method="post" action="accLogin.php">
          <div align="right">email address: 
            <input type="text" name="email" id="email">
            personal password: 
            <input type="password" name="password" id="password">
            <input type="image" src="img/loginBtn.png" name="button" id="button" value="Submit" />
            </div>
        </form>
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: will insert to table, but can't select (login system)

Post by aravona »

have you tried having ur $email as $_POST[email] and $password as $_POST[password] ?

I've done it in the past :)
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

Re: will insert to table, but can't select (login system)

Post by NikBruce »

nothing, it assumes the variables to be 'email' and 'password' anyway.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: will insert to table, but can't select (login system)

Post by aravona »

not sure then since most people with this problem used the quotes fix. will need some imput from someone with more experience methinks.

but i would have thought

Code: Select all

$sql = "SELECT email, word FROM accTable WHERE email = $_POST[email] AND word = $_POST[password]";
would have worked :?
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

Re: will insert to table, but can't select (login system)

Post by NikBruce »

when i use single quotes instead of back ticks as shown below,

Code: Select all

$sql = "SELECT `email`, `word` FROM `accTable` WHERE `email` = '$email' AND `word` = '$password'";
 
$result = mysql_query($sql) or die(mysql_error());
 
$count = mysql_num_rows($result);
 
if($count == 1)
{
 
while($row = mysql_fetch_array($result))
{
$accEmailSSafe = $row['email'];
$accPassSSafe = $row['word'];
 
$_SESSION["accEmailSafe"] = $accEmailSSafe;
$_SESSION["accPassSafe"] = $accPassSSafe;
}
 
header("Location: accountPage.php");
 
}// from if count == 1
 
else { echo "Your email and password combination are incorrect.<br /><input type='image' src='img/backBtn.png' name='button' id='button' value='Submit' onClick='history.go(-1);'/>"; echo $count; }
no mysql errors show up, but count = 0. but it really should be 1!
The email address and password i'm entering really are correct, i checked on the database.
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

Re: will insert to table, but can't select (login system)

Post by NikBruce »

just to clarify my last post, when i said as shown below i was referring to the variables after the "WHERE" clause in line 1.
NikBruce
Forum Newbie
Posts: 22
Joined: Wed Dec 23, 2009 5:16 am

Re: will insert to table, but can't select (login system)

Post by NikBruce »

found the problem. the word field was a Varchar with a length of 17....
that's not really long enough for a password that's been md5'd.

my bad

Thanks for all you help
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: will insert to table, but can't select (login system)

Post by aravona »

No problem :) thanks for posting your solution :)
Post Reply