Page 1 of 1

Register and Login (Email) Won't add to database.

Posted: Sun Jan 29, 2012 6:31 pm
by AndrewD92
Can someone tell me the set up for the database for User Login and Register Email Activation. In the tutorial the details slightly change between Cookies and Email activation and now my user information wont add to the database. I cant seem to get it to work correctly.

This is the only reason I think I'm having this problem.
user_system
users
1 user_id int(8) No None AUTO_INCREMENT
2 user_name varchar(24) latin1_swedish_ci No None
3 user_password char(40) latin1_swedish_ci No None
4 user_email varchar(128) latin1_swedish_ci No None

Indexes (I think this is the problem)
PRIMARY BTREE Yes No user_id 1 A
user_name BTREE Yes No user_name 1 A

user_activations
1 user_id int(8) No None AUTO_INCREMENT
2 activation_code varchar(10) latin1_swedish_ci No None

Indexes
uni BTREE Yes No user_id 0 A
activation_code 0 A

Here is my codes:

user.inc.php
<?php
 
// Checks if given username exits in database
function user_exists($user){
        $user = mysql_real_escape_string($user);
 
        $total = mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_name` = '{$user}'");
 
        return (mysql_result($total, 0) == '1') ? true : false;
}
 
// Checks if given username and password combo is valid
function valid_credentials($user, $pass){
        $user = mysql_real_escape_string($user);
        $pass = mysql_real_escape_string($pass);
 
        $total = mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_name` = '{$user}' AND `user_password` = '{$pass}'");
 
        return (mysql_result($total, 0) == '1') ? true : false;
}
 
// Adds user to database
function add_user($user, $email, $pass){
        $user	= mysql_real_escape_string(htmlentities($user));
        $email	= mysql_real_escape_string($email);
        $pass	= sha1($pass);
        
        $charset = array_flip(array_merge(range('a', 'z'), range('A', 'Z'), range(0, 9)));
        $aid	= implode('', array_rand($charset, 10));
        
        $body = <<<EMAIL
			
		Hi,
		Thanks for registering, before you login you need to activate your account.
		To do that simply click the following link.
		
		http://website.co.uk/user_system/activate.php?aid={$aid}

EMAIL;

		mail($email, 'Your new account at website.co.uk', $body, 'From: admin@website.co.uk');        
 
        mysql_query("INSERT INTO `users` (`user_name`, `user_password`, 'user_email') VALUES ('{$user}', '{$pass}', '{$email}')");
        
        $user_id = mysql_insert_id();
        
        mysql_query("INSERT INTO 'user_activations' ('user_id', 'activation_code') VALUES ({$user_id}, '{$aid}')");
} 
?>

Re: Register and Login (Email) Won't add to database.

Posted: Tue Jan 31, 2012 7:23 pm
by jacek
You need to use backticks ` around youe table and column names instead of quotes '

So this line
        mysql_query("INSERT INTO 'user_activations' ('user_id', 'activation_code') VALUES ({$user_id}, '{$aid}')");
should be
        mysql_query("INSERT INTO `user_activations` (`user_id`, `activation_code`) VALUES ({$user_id}, '{$aid}')");
the same goes for anywhere else you did the same thing ;)

If that does not fix it try adding
echo mysql_error();
after which ever query is failing and it should tell you what the problem is.

Re: Register and Login (Email) Won't add to database.

Posted: Wed Feb 01, 2012 3:18 pm
by AndrewD92
Okay, wicked. Thank you. Thats strange. All the other queries used the proper dash - I wonder why it didn't do it there. I dont even remember inserting that it must of auto corrected it each time.

NEW PROBLEM

Now the new member is added to both users table and user_activation table. But no email is sent. This is my add new user code. I think it has something to do with the <<<EMAIL part. What do you think?
<?php
 
// Checks if given username exits in database
function user_exists($user){
        $user = mysql_real_escape_string($user);
 
        $total = mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_name` = '{$user}'");
 
        return (mysql_result($total, 0) == '1') ? true : false;
}
 
// Checks if given username and password combo is valid
function valid_credentials($user, $pass){
        $user = mysql_real_escape_string($user);
        $pass = mysql_real_escape_string($pass);
 
        $total = mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_name` = '{$user}' AND `user_password` = '{$pass}'");
 
        return (mysql_result($total, 0) == '1') ? true : false;
}

// Checks if the given username is active
function is_active($user){
		$user = mysql_real_escape_string($user);
		$sql = "SELECT COUNT (`user_activations`, `user_id`) FROM `users`INNER JOIN `user_activations` ON `users`, `user_id` = `user_activations`, `user_id` WHERE `users`, `user_name` = `{$user}`";
			
		$result = mysql_query($sql);
	
		return (mysql_result($result, 0) == '0') ? true : false;
}

// Activates the account related to the given activation code.
function activate_account($aid){
		$aid - mysql_reaL_escape_string($aid);
		
		mysql_query("DELETE FROM `user_activations` WHERE `activation_code` = '{$aid}'");
}
 
// Adds user to database
function add_user($user, $email, $pass){
        $user 	= mysql_real_escape_string(htmlentities($user));
        $email 	= mysql_real_escape_string($email);
        $pass 	= sha1($pass);
        
        $charset = array_flip(array_merge(range('a','z'), range('A', 'Z'), range(0, 9)));
        $aid = implode('', array_rand($charset, 10));
        
        $body = <<<EMAIL
        
        Email body goes here.
        
        http://localhost/v2/activate.php?aid={$aid}

EMAIL;

		mail($email, 'Subject', $body, 'From: admin@website.co.uk');
        
 
        mysql_query("INSERT INTO `users` (`user_name`, `user_password`, `user_email`) VALUES ('{$user}', '{$pass}', '{$email}')");
        
        $user_id = mysql_insert_id();
        
        mysql_query("INSERT INTO `user_activations` (`user_id`, `activation_code`) VALUES ({$user_id}, '{$aid}')");
}
 
?>
Im also getting the error "Warning: mysql_result() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\v2\core\inc\user.inc.php on line 29"

I am so tempted to look at other peoples threads but then if I do I wont learn so please help! :D

Re: Register and Login (Email) Won't add to database.

Posted: Fri Feb 03, 2012 12:53 am
by jacek
On line 25
COUNT (`user_activations`, `user_id`)
should be
COUNT (`user_activations`.`user_id`)
which should fix the sql error.

As for the email, it might be caused by that ^^ or it might be your ISP blocking it. adding var_dump() around the mail call, so
var_dump(mail(/* thiings */))
if you get bool(true) from that it means the email is being sent and you are not receiving it. If that is the case it would be best to assume that you have some ISP block and it will probably work when you upload it.