Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL query to show different levels from a table.

Posted on 2016-10-30
5
Medium Priority
?
89 Views
Last Modified: 2016-11-14
I have a database which is for task management.
The main table currently shows all jobs by newest first.
What I want to do is show (for our techs) is the Tech signed in at the top with his open jobs then the any staff categorised jobs underneath.

Something like:
Task date, Task Priority, Customer, Assigned to (tech logged in),
*
*
*
*
These jobs will be listed in date order decending for that tech, then under that it will show jobs assigned in date order decending for anyone.
Task date, Task Priority, Customer, Assigned to Anyone,

The current query is:
SELECT * FROM database.Tasks WHERE `Complete` = '0' ORDER BY `EntryDate` DESC

Open in new window

The Complete=0 just means that jobs are currently open.
Fields involved would be similar to `TechID`='4'

I hope this makes sense.
0
Comment
Question by:kiwistag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 1000 total points
ID: 41866195
Something like this should do it, assuming that the current tech's id is stored (sanitised, with single quotes escaped) in variable $current_user:
<?php $sql = "SELECT *, IF( assigned_to = '$current_user', 1, 0 ) AS current_tech
FROM database.Tasks 
WHERE `Complete` = '0' 
ORDER BY current_tech DESC,`EntryDate` DESC";

Open in new window

0
 
LVL 32

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41866400
I think you can also use case..try.

--

  SELECT *, CASE `assigned_to` 
  WHEN 'variable_Value_you_want_to_check' THEN 1  
  ELSE 0
  END as 'current_tech_user'
  FROM database.Tasks WHERE `Complete` = '0' ORDER BY `EntryDate` DESC
 
--

Open in new window

0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41871705
Hi kiwistag,
Any feedback on this?

Regards,
Pawan
0
 
LVL 6

Author Comment

by:kiwistag
ID: 41873093
Hi Pawan.
Have been away. Trying out the suggestions today.
Cheers
0
 
LVL 6

Author Closing Comment

by:kiwistag
ID: 41885970
Terry's solution worked best in my case. Thank you both for your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The viewer will learn how to count occurrences of each item in an array.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question