Forum Moderators: open
I think I may need to perform a join to do the following, can anyone please help as I haven't performed joins before?
My original query was:
// make the query to get the postings
$query = "SELECT posting_id, vehicle_make, vehicle_model, category FROM postings WHERE buyers_id=$_SESSION[buyers_id] AND status='closed' ORDER by expiry_date";
// 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))
{
// make the 2nd query to get the number of bids
$query2 = "SELECT bid_id, posting_id, sellers_id, price, status, paid, DATE_FORMAT(date_bid_accepted, '%e-%m-%y, %h:%i %p') as bid_accepted FROM bids WHERE posting_id='$row[posting_id]'";
// run the query
$result2 = @mysql_query ($query2) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " . $query2 . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());
while($row2=mysql_fetch_array($result2))
{
I now need to add an aditional check to first see if there isn't an entry for the result in the 'buyers_hide_bids' table.
So in other words, perform the query above only if buyers_id & bid_id don't exist as an entry in the buyers_hide_bids table.
I assume I need to do something like this for the first part of the query:
$sid = $_SESSION['buyers_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_bids WHERE postings.buyers_id=buyers_hide_bids.buyers_id AND postings.posting_id=buyers_hide_bids.posting_id) ORDER by expired ASC") or die('Query Failed: '.mysql_error());
I'm not sure how to add in the 2nd part of the query (make the 2nd query to get the number of bids)
Can anyone possibly help?
SELECT p.posting_id
, p.vehicle_make
, p.vehicle_model
, p.category
, b.bid_id
, b.sellers_id
, b.price
, b.status
, b.paid
, DATE_FORMAT(b.date_bid_accepted,'%e-%m-%y, %h:%i %p')
as bid_accepted
FROM postings as p
INNER
JOIN bids as b
ON b.posting_id = p.posting_id
WHERE p.buyers_id = $_SESSION[buyers_id]
AND p.status = 'closed'
AND NOT EXISTS
( SELECT *
FROM buyers_hide_bids
WHERE buyers_id = p.buyers_id
AND posting_id = p.posting_id )
ORDER
BY p.expiry_date
