www.webdeveloper.com
Results 1 to 3 of 3

Thread: all conditions in a single query

  1. #1
    Join Date
    Aug 2014
    Posts
    3

    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~!!

  2. #2
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    877
    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.

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,616
    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?
    PHP Code:
    $pdo = new PDO($dsn$dbUser$dbPass);
    $sql "
    SELECT
      details.*,
      approval.approval_date,
      disapproval.disapproval_date
    FROM details
    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);
    $stmt->bindParam(':staff_id'trim($_POST['searchid']));
    $stmt->execute();
    // 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

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles