all conditions in a single query
if i have 3 tables in database and i have a search bar in my web page to search staff id to update details.
The staff id being searched in search bar is assume as $search_id = $_POST['searchid'];
The 3 tables in database are:-
1st table - details
details_id, staff_id, staff_name
2nd table - approval
details_id, staff_id, approve_date
3rd table - disapproval
details_id, staff_id, disapproval_date
how can i write all conditions in a single query to perform action below:-
1) select all the data in DETAILS table WHERE the staff_id equal to the $search_id AND order by DETAILS ID in DESC
2) select the approve date from APPROVAL table WHERE the staff_id equal to the $search_id AND order by DETAILS ID in DESC
3) select the disapproval date from DISAPPROVAL table WHERE the staff_id equal to the $search_id AND order by DETAILS ID in DESC
I only able to write up to:-
SELECT * FROM details WHERE staff_id LIKE '$search_id ORDER BY details_id DESC;
anyone can assist me on combining all these conditions into a single query?
Thank you so much~!!
This is really an SQL question best answered in a different forum. Take a stab at it and THEN ask for help - that's the way to learn!
PS - your posted 'attempt' is close but flawed. Quotes are very important things to keep an eye on.
PPS - if the tables are EXACTLY what you have given us to look at, you have some serious re-configuring of your database to do. Three tables with the same two key fields and ONLY one data field in each? Really? You definitely need to learn about database normalization (look it up!) and start over with your design.
PS - If you're posting here you should be using:
error_reporting(E_ALL | E_NOTICE);
at the top of ALL php code while you develop it!
Sounds like a job for some table joins, probably left joins since I suspect not every entry in details will necessarily have entries in the other two tables?
$pdo = new PDO($dsn, $dbUser, $dbPass);
$sql = "
LEFT JOIN approval USING(details_id)
LEFT JOIN disapproval USING(details_id)
WHERE details.staff_id = :staff_id
ORDER BY details.details_id DESC
$stmt = $pdo->prepare($sql);
// loop on $stmt->fetch() to get all matches
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)