Here is my USER table
CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `expiry` varchar(6) NOT NULL, `contact_id` int(11) NOT NULL, `email` varchar(255) NOT NULL, `password` varchar(100) NOT NULL, `level` int(3) NOT NULL, `active` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`,`email`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
And here is my contact_info table
CREATE TABLE IF NOT EXISTS `contact_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `email_address` varchar(255) NOT NULL, `company_name` varchar(255) NOT NULL, `license_number` varchar(255) NOT NULL, `phone` varchar(30) NOT NULL, `fax` varchar(30) NOT NULL, `mobile` varchar(30) NOT NULL, `category` varchar(100) NOT NULL, `country` varchar(20) NOT NULL, `state` varchar(20) NOT NULL, `city` varchar(100) NOT NULL, `postcode` varchar(50) NOT NULL, PRIMARY KEY (`id`,`email_address`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
The system uses username to login users. I want to modify it in such a way that it uses email for login. But there is no email_address in users table.
I have added foreign key - email in user table(which is email_address in contact_info).
How should I query database?
No, no, no, no no. Seriously, no. Don't make me come over there and slap you around :-)
You're breaking third normal form by storing the email address twice.
The relationship need only be a short one, that of id. Assuming you're not guaranteeing the IDs will be identical in the two tables (i.e., my users.id isn't necessarily equal to my contact_info.id), just add a ci_id to the users table to act as a foreign key to the contact_info table.
Then the query to get a user's username and email will be something like:
select u.username, ci.email
from users u, contact_info ci
where u.username = 'paxdiablo'
and u.ci_id = ci.id;
By the way, it's not necessary to Community-Wiki questions like this. They're perfectly appropriate here on SO and you'll probably get more answers if there's rep involved. Lack of rep won't stop me of course, I'm just here because I like telling people what to do and that's pretty difficult in a household with a strong-willed wife and two kids under the age of six :-)