How Can I make this mysql query

[thara]

I am trying to build a login system. But have a problem when I am creating the query for it. I will briefly explain what I need is. I have got username and password form login form. Now I need to check those values with my login table weather those are exist or not in the table. As well as I need to check users' registration is expired or not in the same query using tutors , institutes and students tables. students registration is not expiring. Further I need to check their profile is activated or not usingactive column in tutors and institutes tables. Again students table doesn't have a column 'active'. They don't need to activate their profile.

 

These are columns which I need to get from query.

login_id // From login table
username // From login table
login_type // From login table
date-expire true or false // From tutors or institute or student. 

 

I tried it something like this but confusing in how to attach all my tables together in the same query.

$q = "SELECT login_id, username, login_type, IF(date_expires >= NOW(), true, false) 
       FROM login
       INNER JOIN tutors ON login.login_id = tutors.login_id
       INNER JOIN institutes ON login.login_id = institutes.login_id
       WHERE (username='$username' AND password='"  .  get_password_hash($password) .  "' 
               AND (tutors.active IS NULL || institutes.active IS NULL))"; 

 

But this query is not working. Can anybody tell me what is the mistake I have done?

This is my table structure

CREATE TABLE login (
login_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL, 
password VARBINARY(32) NOT NULL,
login_type ENUM('tutor', 'institute', 'student') NOT NULL,
PRIMARY KEY (login_id)
)


CREATE TABLE institutes (
institute_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
login_id SMALLINT UNSIGNED NOT NULL,
active CHAR(32),
date_expires DATE NOT NULL,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (institute_id)
)   

CREATE TABLE tutors (
tutor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
login_id SMALLINT UNSIGNED NOT NULL,
active CHAR(32),
date_expires DATE NOT NULL,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tutor_id)
)

CREATE TABLE students (
student_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
login_id SMALLINT UNSIGNED NOT NULL,
date_registered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id)
)

[Barand]

Please describe "not working" and check what is returned from mysql_error() (or whichever is the equivalent for your library)

[Psycho]

I would redefine the login table such that the Login/Status information is in that table. If Students don't have an expiration or active/inactive status - then just leave those values blank for those records. Makes things so much easier.

 

But, the reason your query is not working is because of the INNER JOINs. An INNER JOIN only pulls results where there are records from both tables that meet the matching condition. In your case there are NO records from the login table that have matching records in both the institutes and tutors tables.

 

You want ALL the records from the first table (login) even when there are no records to JOIN to in the 2nd or 3rd tables. So, you need to use LEFT JOINS. But, I'm not sure how that would work for the date_expires field since you want that from two different tables. I'd just put all the pertinent login information in the, well, login table.

[mikosiko]

in addition ...in your query the column date_expires is ambigous

 

Edit: Psycho already mentioned that

Edited February 5, 2013 by mikosiko

[thara]

Please describe "not working" and check what is returned from mysql_error() (or whichever is the equivalent for your library)

 

When running the query it says

 

An error occurred in script 'C:\wamp\www\LankaInstitute\login.php' on line 61:

Query: SELECT login.login_id, username, login_type, IF(date_expires >= NOW(), true, false)

FROM login

INNER JOIN tutors ON login.login_id = tutors.login_id

INNER JOIN institutes ON login.login_id = institutes.login_id

WHERE (username='lankain' AND password='w��d�@�I��X�S�m�͹���o���A�lf'

AND (tutors.active IS NULL || institutes.active IS NULL))

 

MySQL Error: Column 'date_expires' in field list is ambiguous

 

But I have no idea to fix this reason is date_expires column have in two tables.

[Jessica]

You know how you have login.login_id to specify the table.column? DO that for the other column. 

[thara]

date_expire column have in two tables (tutors, institutes)

 

tutors.date_expire or institutes.date_expire ??

 

I need to check both

[Christian F.]

Then you need to do two IF tests, and specify which table you're testing each time.

[thara]

Then you need to do two IF tests, and specify which table you're testing each time.

 

Can you give an example how I use two IF test in a query. Its completely new thing for me.

Edited February 5, 2013 by thara

[Jessica]

IF(t1.date_expires >= NOW(), IF(t2.date_expires >= NOW(), true, false), false) should work.

[Christian F.]

Or just join them with and AND, should work as well. (Not tested though.)

[Barand]

or just select a boolean expression

 

SELECT (tutors.date_expires >= NOW() AND institutes.date_expires >= NOW()) as expired

[thara]

Can anyone tell me, can I use single query for this? Still I couldn't do it in single query. So I used an alternative method for do this with 3 queries. I added radio buttons along with username and password fields in form to select their login type (tutors, institutes, students). Then I checked login_type == 'tutors' and a query, login_type == 'institute' then another query and so on.

 

Can I do this without adding any HTML for my form?

 

Thank you.

[Psycho]

I still think you need to put the expiration and status in the login table. Since that information is used as part of the login process it makes sense. Just leave empty for those users to which the values do not apply.

 

But, since you seem hell bent on approaching it in this manner - the below should work. You don't state what values are used in the active fields - so I guessed.

 

SELECT l.login_id, l.username, l.login_type,
   IF(   (l.login_type = 'student')
	  OR (l.login_type = 'tutor'
		    AND t.date_expires >= NOW()
		    AND t.active = 1)
	  OR (l.login_type = 'institute'
		    AND i.date_expires >= NOW()
		    AND i.active = 1),
	  true, false) AS login_status
FROM login AS l
LEFT JOIN tutors AS t
 ON l.login_id = t.login_id
LEFT JOIN institutes AS i
 ON l.login_id = i.login_id

WHERE username='$username'
 AND password='$passwordhash'

[thara]

@Psycho Thank you for your query. Its working for me. But you have mentioned its better if I add date_expire column into login table. But I cant understand why it is. This information is not only used in login process. When tutors or institutes registering with the site they need to select their subscription period and using that period I make expiration date and add that column along with date_registered and subscription_period columns in my tutors and institutes tables. Its easy to managed me.

 

And also you have mentioned login status. I have no idea about it and what do you mean it? I just checking expiration date when users login to system for limit to access their control panel. That mean if tutor or institute have expired their subscription period I need to stop some features they have got through their profile. So now I would like to know, do I need to store those values in database? if so which table. (is it login table?)

 

So about this issue I am expecting some explanation form you. Because I think I should be something (with your idea) that I couldn't understand so far.

 

your idea will be highly appreciated.

Thank you.

[Psycho]

I have no insight into your application - and am not willing to take the time to do so. If the status and expiration date make sense for those associated tables then keep that data there. I really don't care.

 

But, I don't really understand what you are trying to accomplish with the query in this thread. If you are wanting to use that query for login purposes I don't think you should try and use the active and expiration dates to exclude records in the query search. Have the query simply find the record in the login table based on username and password AND grab the associative data from the other tables. If no match, then you know that the username/password is not valid.

 

But, if you get a record THEN check the other fields to see if the user is active and not past the expiration period. That way you can give the user a useful message as to why they cannot log in.

 

I would suggest using this query

SELECT l.login_id, l.username, l.login_type,
 IF( (l.login_type = 'tutor' AND t.date_expires < NOW()) OR
	 (l.login_type = 'institute' AND i.date_expires < NOW()),
	 false, true) AS expired,
 IF( (l.login_type = 'tutor' AND t.active <> 1) OR
	 (l.login_type = 'institute' AND i.active <> 1),
	 true, false) AS active
FROM login AS l
LEFT JOIN tutors AS t
ON l.login_id = t.login_id
LEFT JOIN institutes AS i
ON l.login_id = i.login_id

 

This will return login_id, username, login_type and two dynamically generated variables: expired and active.

- If the user is a student OR if the user is a tutor/institute and the corresponding expiration is in the future the value of expiration will be false (else it is true).

- If the user is a student OR if the user is a tutor/institute and the corresponding active value is true, the value of active will be true (else it is false)

 

After running that query you could have some logic such as this:

$query = "SELECT l.login_id, l.username, l.login_type,    
			 IF( (l.login_type = 'tutor' AND t.date_expires < NOW()) OR
				 (l.login_type = 'institute' AND i.date_expires < NOW()),
				 false, true) AS expired
			 IF( (l.login_type = 'tutor' AND t.active <> 1) OR
				 (l.login_type = 'institute' AND i.active <> 1),
				 true, false) AS active
	  FROM login AS l
	  LEFT JOIN tutors AS t
	    ON l.login_id = t.login_id
	  LEFT JOIN institutes AS i
	    ON l.login_id = i.login_id

	  WHERE username='$username'
	    AND password='$passwordhash'";

$result = mysql_quer($query) or die(mysql_error());

if(!mysql_num_rows($result))
{
   echo "Your credentials are incorrect.";
}
else
{
   $user = mysql_fetch_assoc($result);

   if($user['expired'])
   {
    echo "Your account has expired.";
   }
   elseif(!$user['active'])
   {
    echo "Your account is inactive.";
   }
   else
   {
    //Everything checks out - complete login process
   }
}

Edited February 6, 2013 by Psycho

[thara]

@pshcho. Thank you very much for your help. Now its solved. I greatly appreciated your answer and your ideas. I am little bit hard to understand above query reason is I am completely new to such a query. I have never used such a conditional queries before and need to make sense. And also I still confuse to understand how inner join and left join sometimes work. So If you have any free time don't forget to post some explanation.

 

Thank you again.