Help constructing query

[adammc]

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]// 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))
{[/PHP]

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

[PHP]// 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))
{[/PHP]

[cameraman]

I boiled this down to its simplest:
SELECT * FROM postings WHERE NOT EXISTS (SELECT buyers_id FROM buyers_hide_requests WHERE postings.buyers_id=buyers_hide_requests.buyers_id)

This will show you all records in postings who don't have a record with same buyers_id in buyers_hide_requests. You could filter it further with your $_SESSION['buyers_id'] right after the first WHERE, and AND the not exists part.

[adammc]

thanks for the reply cameraman :)

I cant really see how I can add:
buyers_id=$_SESSION[buyers_id] AND status!='closed'

This just doesnt look right:
$query = "SELECT * FROM postings WHERE buyers_id=$_SESSION[buyers_id] AND status!='closed' NOT EXISTS (SELECT buyers_id FROM buyers_hide_requests WHERE postings.buyers_id=buyers_hide_requests.buyers_id) ORDER by expired ASC";

[cameraman]

You just need one more AND. I tend to parenthesize everything as well:
$query = "SELECT * FROM postings WHERE ((buyers_id=$_SESSION[buyers_id]) 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";

[adammc]

Still no luck :(

I got no results from the query even though it should be showing 5 results.

This entry below that is found in the 'buyers_hide_requests table' is the only one that shouldnt be appearing

buyers_hide_requests table:
id - 1
posting_id - 16
buyers_id - 194

postings table:
id - 1
posting_id - 16
buyers_id - 194
status - open

[cameraman]

Hmm, I tested it on mine before posting and it worked. Try replacing the SESSION variable with a constant (like the 194 there) to see what happens. Just for diagnostics you might at the same time show non-closed entries in postings for 194 and entries for 194 in hide-requests. Also just for grins temporarily remove the ORDER BY clause.

[adammc]

No luck :(

I echoed the query and got this:
query - SELECT * FROM postings WHERE ((buyers_id=16) 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

[cameraman]

HAH got it. I hope you have a unique record identifer (primary key) in each table? Put that where I've got 'rec_id'

SELECT * FROM postings WHERE ((buyers_id = 2) AND (NOT EXISTS (SELECT buyers_id FROM buyers_hide_requests WHERE ((postings.buyers_id=buyers_hide_requests.buyers_id) AND (postings.rec_id=buyers_hide_requests.rec_id))))) ORDER BY expired ASC

[adammc]

postings table:
$SQL = $SQL . " PRIMARY KEY(posting_id) );";

buyers_hide-requests table:
$SQL = $SQL . " PRIMARY KEY(id) );";

[cameraman]

The last version I posted will work if postings.posting_id is synchronized with buyers_hide_requests.id.

If those two aren't, then you could use a different field, but it has to be the one that synchronizes those two tables.
The first version I posted didn't work because once it found a buyers_id with the one you were looking for in hide_requests, it EXISTed so it wouldn't match any records. So the AND in the second half (the sub-query) specifies that it's this buyer AND this particular record.

Is that understandable? I don't think I stated it very well.

[adammc]

Cameraman,
thank you for your assistance :)

Thanks guys..
I got it sorted out using something a bit different:

[PHP]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[/PHP]