Email Activation Error

[liquiddrive]

Hello,

I followed the User System tutorial, followed by the Cookie Extension tutorial and everything worked out perfectly.

On the Email Activation tutorial [03] I unfortunately came across an error when I'm testing if an account is active or not.

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/liquid/public_html/projects/login/core/inc/user.inc.php on line 35

Warning: Cannot modify header information - headers already sent by (output started at /home/liquid/public_html/projects/login/core/inc/user.inc.php:35) in /home/liquid/public_html/projects/login/login.php on line 32

The code on line 35 (core/inc/user.inc.php) is the following:

return (mysql_result($result, 0) == '0') ? true : false;

And the code from line 32 (login.php) is:

header('Location: protected.php');

I've looked at the code from mine and the tutorial I followed on the BetterPHP YouTube channel and everything seems to be exactly the same.

Any ideas what the problem is?

Thanks, Joe.

[jacek]

The second error is most likely caused by the output of the first error, so let's not worry about that for now.

The first error, means your mysql query is failing for some reason. If you add

echo mysql_error();

after your mysql_query() line it should tell you why.

[liquiddrive]

Hello, this is was the output:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'users`.`user_name` = 'joe'' at line 5

[jacek]

Right... Does that help you spot the problem ?

Generally the problem is at the start of the thing it tells you it is near so look just before users`.`user_name` in your SQL.

[liquiddrive]

Unfortunately not, can you point me in the right direction?

[jacek]

Unfortunately not, can you point me in the right direction?

I can if you post the SQL.

[liquiddrive]

-- phpMyAdmin SQL Dump
-- version 3.3.9.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 05, 2011 at 04:09 PM
-- Server version: 5.0.92
-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `liquid_radio`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(6) NOT NULL auto_increment,
  `user_name` varchar(24) NOT NULL,
  `user_password` char(40) NOT NULL,
  `user_email` varchar(128) NOT NULL,
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `user_name`, `user_password`, `user_email`) VALUES
(14, 'joe', '7110eda4d09e062aa5e4a390b0a572ac0d2c0220', 'dawson303@hotmail.co.uk'),
(15, 'carl', '7110eda4d09e062aa5e4a390b0a572ac0d2c0220', 'rez.fx@live.com');

I hope that is correct

[jacek]

I meant the SQL you are using in your code, although knowing the table structure is also useful

It should be the $sql variable ...

[liquiddrive]

$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}'";

I hope this is fine?

[jacek]

Hmm, that looks okay to me.

Can you post the full user.inc.php ?

[liquiddrive]

Sure! It all looked the same to me too :/

<?php

// checks if the given username exists in the 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 the given username and password combination 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 username entered 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);
	echo mysql_error();
	
	return (mysql_result($result, 0) == '0') ? true : false;
	
}

// adds a user to the 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,
	
	Thank-you for registering. Activation link below:
	
	http://liquid-drive.co.uk/projects/logi ... ?aid={$aid}	
	
EMAIL;

	mail($email, 'Your new account at Liquid-Radio', $body, 'From: info@liquid-drive.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}')");
}

?>

[jacek]

Ah ! I spotted it, this time it was actually at the end of where mysql said is was near

ON `users`.`user_id` = `user_activations`.`user_id'

The ' and the end of this line should be a `

[liquiddrive]

Got it! Thank-you so much!