How can I fetch the task assigned to a particular user having deadline today?

Question

task_list table :

CREATE TABLE "task_list" (
    "task_id"   INTEGER NOT NULL,
    "task_code" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "description"   TEXT NOT NULL,
    "department_id" INTEGER NOT NULL,
    "employee_id"   INTEGER,
    "status"    INTEGER NOT NULL DEFAULT 1,
    "date_created"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date_updated"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date"  TEXT,
    PRIMARY KEY("task_id" AUTOINCREMENT),
    FOREIGN KEY("employee_id") REFERENCES "employee_list"("employee_id") on DELETE SET NULL,
    FOREIGN KEY("department_id") REFERENCES "department_list"("department_id") on DELETE CASCADE
);

task_assignees table :

CREATE TABLE "task_assignees" (
    "task_id"   INTEGER NOT NULL,
    "employee_id"   INTEGER NOT NULL,
    FOREIGN KEY("task_id") REFERENCES "task_list"("task_id") on DELETE CASCADE,
    FOREIGN KEY("employee_id") REFERENCES "employee_list"("employee_id") on DELETE CASCADE
);

employee_table :

CREATE TABLE "employee_list" (
    "employee_id"   INTEGER NOT NULL,
    "employee_code" INTEGER NOT NULL,
    "fullname"  INTEGER NOT NULL,
    "email" TEXT NOT NULL,
    "password"  TEXT NOT NULL,
    "gender"    TEXT NOT NULL,
    "dob"   DATE NOT NULL,
    "contact"   TEXT NOT NULL,
    "address"   TEXT NOT NULL,
    "department_id" INTEGER NOT NULL,
    "type"  TEXT NOT NULL,
    "status"    INTEGER NOT NULL DEFAULT 1,
    "date_created"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "date_updated"  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY("department_id") REFERENCES "department_list"("department_id") on DELETE CASCADE,
    PRIMARY KEY("employee_id" AUTOINCREMENT)
);

Query to fetch the task assigned to a particular user :

"SELECT * FROM `task_list` where task_id in (SELECT task_id FROM `task_assignees` where employee_id = '{$_SESSION['employee_id']}') order by strftime('%s',date_created) desc";

Query for the task which having deadline today :

 "SELECT * FROM `task_list` where DATE(`date`) = DATE('now') ";

I want to join both queries so I can fetch the list of tasks assigned to the user whose session is active and has the deadline today.

<?php 
    $sql = "SELECT * 
            FROM task_list 
            where task_id 
                in (SELECT task_id 
                    FROM task_assignees 
                    where employee_id = '{$_SESSION['employee_id']}'
                    ) 
            AND (DATE(date)=DATE(now))"; 

    $qry = $conn->query($sql); 
    $i = 1; 
    while($row = $qry->fetchArray()): 
?>

Hint: 1) use inner join instead of subquery. 2) a WHERE clause can have AND and OR to combine multiple conditions together — ADyson, Sep 07 '22 at 10:18
will it work?"SELECT * FROM `task_list` where task_id in (SELECT task_id FROM `task_assignees` where employee_id = '{$_SESSION['employee_id']}') AND (DATE(`date`)=DATE(`now`))"; — Chirag Dixit, Sep 07 '22 at 10:20
It is not working & throwing error "Call to a member function fetchArray() on bool" — Chirag Dixit, Sep 07 '22 at 10:29
What code library are you using in the PHP? You need to get it to report the specific SQL error, because that error you're seeing is just a symptom of the query command returning false (because it failed) and then you trying to use that `false` (bool) value like it's an object. — ADyson, Sep 07 '22 at 10:31
Please edit your question when you have code to share! But `$conn` is what? PDO object or something? — ADyson, Sep 07 '22 at 10:32
Add some [PDO error handling](https://stackoverflow.com/questions/32648371/why-does-this-pdo-statement-silently-fail) then you can get the SQL error — ADyson, Sep 07 '22 at 10:34
Good. What exactly was the issue in the end? You can always write your solution below as an Answer if you think it might be useful to others with a similar problem. — ADyson, Sep 08 '22 at 09:00
Please add it as a proper Answer below then others can benefit too, and you can get upvotes :-) — ADyson, Sep 09 '22 at 07:13

Chirag Dixit · Answer 1 · 2022-09-12T04:59:54.333

1

Not using quotes like (`` & '') for table name & attributes. Here is the correct code:

<?php 
  $sql = "SELECT * FROM `task_list` where task_id in (SELECT task_id FROM `task_assignees` where employee_id = '{$_SESSION['employee_id']}') AND (DATE(`date`)=DATE('now')) order by strftime('%s',date_created) desc";
$qry = $conn->query($sql); 
$i = 1; 
while($row = $qry->fetchArray()): ?>

edited Sep 12 '22 at 04:59

answered Sep 12 '22 at 04:53

Chirag Dixit

17
8

How can I fetch the task assigned to a particular user having deadline today?

1 Answers1