SQL Problems and Questions

[Lorddraco98]

Okey, need some help hehe.
I'm kinda new to SQL and am just learning PHP/MySQL.

first off, on this query
$query = "insert into user values ('".null."', '".$loginname."', '".$loginpassword."', '".$emailaddress."')";
that code works, now why do I need the periods before and after the variable names, and why do I need all those single and double quotes?? I'm having a hard time following this.

My DB is set up this way:
Database
table: user
columns in the user table: userid, username, password, email

the userid colum is an auto-incrament and its the primary key, the rest are all text

I'm just trying to create right now a series of pages where you can: register a user name, pass, and e-mail address, insert that info to the DB. Then make a login page, that asks for user name and pass, and then checks the name and pass against that stored in the DB.

WIth the query listed above, I can successfully add users, just not encrypted passwords yet hehe. Problem comes when i want to check the user name/password that was typed in against the DB.

I'm using this code:
$query = "select username FROM user where username is '.loginname.'";
$result= mysql_query($query)
or die("Couldn't execute query");
$num1= mysql_num_rows($result);
if ($num >1)
{
$query ="SELECT password FROM user WHERE username='.$loginname.' AND password='.$loginpassword.'";
$result2 = mysql_query($query)
or die("Couldn't connect ot MySQL server");
$num2 =mysql_num_rows($result2);
if ($num2 >0)
{
$auth="yes";
}
else
{
echo "login not accepted";
}
}


the variables loginname and loginpassword and from forms listed on the previous page. Right now, i run this and get my error message that "query couldn't be executed"
Can anyone help me here so I can get user/password checking against the DB working and also explain the stuff in my first question? Thanks!

[nsr81]

now why do I need the periods before and after the variable names, and why do I need all those single and double quotes?? I'm having a hard time following this.


You don't need them. Here is the modified query:
$query = "insert into user values ('', '$loginname', '$loginpassword', '$emailaddress')";


For authenticating user, you don't need the first query you are doing to see if the username exist. Just use the following:

$query ="SELECT password FROM user WHERE username='$loginname' AND password='$loginpassword'";
$result2 = mysql_query($query)or die("Couldn't connect ot MySQL server");
$num2 =mysql_num_rows($result2);
if ($num2 >0)
{
$auth="yes";
}
else
{
echo "login not accepted";
}

[Lorddraco98]

nice thanks, I'll go ahead and see if that works!

[Lorddraco98]

Originally posted by TNasir
You don't need them. Here is the modified query:
$query = "insert into user values ('', '$loginname', '$loginpassword', '$emailaddress')";


For authenticating user, you don't need the first query you are doing to see if the username exist. Just use the following:

$query ="SELECT password FROM user WHERE username='$loginname' AND password='$loginpassword'";
$result2 = mysql_query($query)or die("Couldn't connect ot MySQL server");
$num2 =mysql_num_rows($result2);
if ($num2 >0)
{
$auth="yes";
}
else
{
echo "login not accepted";
}

hmm
i'm having a problem. I did all that(except named $num2 $num1), then here is my code after:

]if ($auth ="yes")
{
echo "Username and Password Accepted.<br>";
echo "<a href= 'membersonly.php'>Click here to contiune.</a><br>";
echo "<a href= 'authorize.php'>Authorize New Users</a><br>";
}
else
{
echo "Username or Password incorrect. Please try again!";
echo "<a href='login.php'>Login Page</a><br>";
}

with that in there, even if you type in the wrong user/pass, it displayers username and password accepted and gives you the links..
ANy ideas why it's not dening the wrong user/pass

[nsr81]

in your code you have the following mistake:if ($auth ="yes")it should be:
if ($auth =="yes")When you have single = sign, you will always get "Yes" in $auth, because single = sign is assignment operator not a comparison operator.

http://www.php.net/manual/en/language.operators.php

[Lorddraco98]

Originally posted by TNasir
in your code you have the following mistake:if ($auth ="yes")it should be:
if ($auth =="yes")When you have single = sign, you will always get "Yes" in $auth, because single = sign is assignment operator not a comparison operator.

http://www.php.net/manual/en/language.operators.php
eh, i've been looking at that code for too long :D
Thanks man, hehe. Everything works now!
Next step is to MD5 the passwords, and when registering having a check if the user/e-mail has already been taken.