CODEWITHSUNDEEP
phpmysqlsql
TNK
TNK

Reputation: 4333

How Can I make this mysql query

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 using active 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)
)

Upvotes: 1

Views: 168

Answers (1)

Mr. Radical
Mr. Radical

Reputation: 1838

EDIT:

$q = "SELECT l.login_id, l.username, l.login_type, t.date_expires
FROM login AS l
INNER JOIN tutors AS t ON l.login_id = t.login_id
WHERE l.username='$username' AND l.password='" . get_password_hash($password) . "'
AND t.date_expires >= NOW()
AND t.active IS NULL";

I have split up your query. This is just the start of what could be the answer to your question. I'm still not clear about all the requirement you need.

If you could post some sample data with the required output I will be able to help you further.

Upvotes: 2

Related Questions