Results 1 to 3 of 3

Thread: all conditions in a single query

  1. #1
    Join Date
    Aug 2014

    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
    Voorheesville NY USA
    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:

    ini_set('display_errors', '1');

    at the top of ALL php code while you develop it!

  3. #3
    Join Date
    Aug 2004
    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 "
    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);
    // loop on $stmt->fetch() to get all matches 
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

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