SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    it seems the prob is with the mysql and not php, you should head over to the mysql forum with this question those guys rock. I believe you want to do a LEFT JOIN but I could be wrong
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  3. #3
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Wheeler,
    Niced to see a fellow Aussie

    I have just posted this Q in the SQl forum, thanks

    However, i just had someone on another forum suggest using this:

    PHP Code:
    $query "SELECT * FROM postings WHERE NOT EXISTS (SELECT buyers_id FROM buyers_hide_requests WHERE postings.buyers_id=buyers_hide_requests.buyers_id)  ORDER by expired ASC"
    However I couldnt quite work out how to filter it further using:
    buyers_id=$_SESSION[buyers_id] AND status!='closed'

  4. #4
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its cool there seems to be tonnes of aussies on sitepoint, maybe its because sitepoint.com.au seems to be a melbourne based company - is that where it all started?

    good luck in the sql forums, those guys always come up with something that I didn't even know was possible...

    I don't know if its advisable to put $_SESSION straight into the query, I would probably make it a variable first above the query.

    I assume you could add those things by:
    PHP Code:
    $sid $_SESSION['buyer_id'];
    if (!
    is_numeric($sid)) die('session id is dodgy');

    $query mysql_query("SELECT * FROM postings WHERE buyer_id = $sid AND status != 'closed' AND NOT EXISTS (SELECT buyers_id FROM buyers_hide_requests WHERE postings.buyers_id=buyers_hide_requests.buyers_id)  ORDER by expired ASC") or die('Query Failed: '.mysql_error()); 
    that is just a guess I guess u would just have to run it and find out
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development


Bookmarks

Posting Permissions

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