...

View Full Version : Help constructing query



adammc
01-30-2007, 03:43 AM
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:


// 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



// 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))
{

martialtiger
01-30-2007, 04:17 AM
Why not just combine both queries like so:


$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!

adammc
01-30-2007, 04:22 AM
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:

postings p WHERE

martialtiger
01-30-2007, 04:26 AM
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.'.

adammc
01-30-2007, 04:35 AM
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?

adammc
01-30-2007, 04:37 AM
Ooops, I forgot to ask...
What do I reference the posting_id as for later use?

I was using: $row[posting_id]

martialtiger
01-30-2007, 04:39 AM
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.

adammc
01-30-2007, 04:48 AM
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?

martialtiger
01-30-2007, 04:57 AM
Basically you don't need to do $row['p.posting_id'] or $row['b.buyers_id'].

adammc
01-30-2007, 06:34 AM
So I can just use $row[posting_id]

martialtiger
01-30-2007, 06:39 AM
Yes, that's right.

adammc
01-30-2007, 06:54 AM
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 :)

adammc
01-31-2007, 12:54 AM
Thanks guys..
I got it sorted out using:



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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum