Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Help constructing query

    Hi folks,

    I'm having trouble working out how to construct this query.
    Any help would be GREATLY appreciated

    Please excuse the code, I dont know how to do table joins so its quite messy and outdated

    What I need to do is, add an additional query to only extract the posts that 'HAVENT" been entered into the 'buyers_hide_requests' table.
    This table is a new feature that I have just created, it stores posting_id | buyers_id.

    It is currently returning all results 'WHERE buyers_id=$_SESSION[buyers_id] AND status!='closed' ORDER by expired ASC'.

    I am hoping to not show posts that appear in the 'buyers_hide_requests' table.



    I assume I need to do something like this:

    PHP Code:
    // make this query to get the posts that the buyer has chosen not to show
    $query0 "SELECT posting_id, buyers_id FROM buyers_hide_requests WHERE buyers_id=$_SESSION[buyers_id]"
    // run the query
    $result0 = @mysql_query ($query0) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " $query0 "<br />\nError: (" mysql_errno() . ") " mysql_error()); 
     
    while(
    $row0=mysql_fetch_array($result0)) 
       { 

    This is the 2nd part of the query that would need to be modified


    PHP Code:
    // make the query to get the postings
    $query "SELECT posting_id, vehicle_make, vehicle_model, year, expired, status, buyers_city, buyers_state, condition, category, DATE_FORMAT(expiry_date, '%e-%m-%y, %h:%i %p') as e_date FROM postings WHERE buyers_id=$_SESSION[buyers_id] AND status!='closed' ORDER by expired ASC"

    // run the query
    $result = @mysql_query ($query) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " $query "<br />\nError: (" mysql_errno() . ") " mysql_error()); 
     
     
    while(
    $row=mysql_fetch_array($result)) 
       { 

  • #2
    New Coder
    Join Date
    Jul 2006
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Why not just combine both queries like so:
    PHP Code:
    $qry "SELECT b.posting_id, b.buyers_id, p.posting_id, p.vehicle_make, p.vehicle_model, p.year, p.expired, p.status, p.buyers_city, p.buyers_state, p.condition, p.category, DATE_FORMAT(p.expiry_date, '%e-%m-%y, %h:%i %p') as e_date FROM buyers_hide_requests b, postings p WHERE b.buyers_id = p.buyers_id AND b.buyers_id=$_SESSION[buyers_id] AND p.status!='closed' ORDER by expired ASC"
    Good luck!

  • #3
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi martialtiger,
    thanks for the reply

    I havent really done any joins before, thats why I didnt attempt it.
    SELECT b.posting_id, b.buyers_id, p.posting_id, p.vehicle_make, p.vehicle_model, p.year, p.expired, p.status, p.buyers_city, p.buyers_state, p.condition, p.category, DATE_FORMAT(p.expiry_date, '%e-%m-%y, %h:%i %p') as e_date FROM
    This would have to be selected from the 'postings' table as the 'buyers_hide_requests' table only holds 'buyers_id' & 'posting_id'

    What does this part of your code mean:
    PHP Code:
    postings p WHERE 

  • #4
    New Coder
    Join Date
    Jul 2006
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What I'm basically doing is selecting from both tables. The section you highlighted simply says select from postings table too and we can just reference postings as p instead of having to type 'postings.' we can just use 'p.'.

  • #5
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the explanation
    So 'b' is a reference to the 'buyers_hide_requests' table?

    How many 'AND' statements are you able to use in this type of query?

  • #6
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ooops, I forgot to ask...
    What do I reference the posting_id as for later use?

    I was using: $row[posting_id]

  • #7
    New Coder
    Join Date
    Jul 2006
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I believe you can use as many AND statements as necessary. As for how you can reference posting_id, it should remain the same. You would not need to include the table abbreviation in your array.

  • #8
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question

    Great! Thanks again for the great advice

    You would not need to include the table abbreviation in your array.
    Sorry, what exactly do you mean?

  • #9
    New Coder
    Join Date
    Jul 2006
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Basically you don't need to do $row['p.posting_id'] or $row['b.buyers_id'].

  • #10
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So I can just use $row[posting_id]

  • #11
    New Coder
    Join Date
    Jul 2006
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, that's right.

  • #12
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hmmm strange!

    I ran the code and the data in the 'buyers_hide_requests' table wasnt filtered out of the results?

    Example data in 'buyers_hide_requests' table:
    (this table is used to store the 'postings' that members dont want displayed in a list.
    posting_id - 1
    buyers_id - 5

    Example data in postings' table:
    (this table stores all the postings)
    posting_id - 1
    buyers_id - 5

    Any help would be greatly appreciated

  • #13
    New Coder
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys..
    I got it sorted out using:


    PHP Code:
    SELECT P.posting_id
         
    P.vehicle_make
         
    P.vehicle_model
         
    P.year
         
    P.expired
         
    P.status
         
    P.buyers_city
         
    P.buyers_state
         
    P.condition
         
    P.category
         
    DATE_FORMAT(P.expiry_date
              
    '%e-%m-%y, %h:%i %p') as e_date 
      FROM postings 
    as P
    LEFT OUTER
      JOIN buyers_hide_requests 
    as H
        on H
    .posting_id P.posting_id   
     WHERE P
    .buyers_id $_SESSION[buyers_id
       AND 
    P.status <> 'closed' 
       
    AND H.posting_id IS NULL
    ORDER 
        by P
    .expired ASC 


  •  

    Posting Permissions

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