Jump to content

Need help with a query


skallsen

Recommended Posts

I have this query almost working...I just don't know how to do this final comparison.

My query:

$query = "SELECT
a.SaleHeadline,
a.SaleCounty,
a.StateID,
a.SaleCity,
a.SaleAddress,
a.Day1Date,
a.Day1Start,
a.Time1Start,
a.Day1End,
a.Time1End,
a.Day2Date,
a.Day2Start,
a.Time2Start,
a.Day2End,
a.Time2End,
a.Day3Date,
a.Day3Start,
a.Time3Start,
a.Day3End,
a.Time3End,
a.Day4Date,
a.Day4Start,
a.Time4Start,
a.Day4End,
a.Time4End,
a.SaleInfo,
b.CityID,
b.CityName
FROM SaleListing a, Cities b
WHERE a.SaleCity=b.CityID
AND b.CityID=".$SaleCity."
AND Day1Date = '".$searchdate."'";

At the end of the query I am searching for a match in Day1Date. I also need to search these columns for a match: Day2Date, Day3Date, Day4Date. the match can be in any of them or just one - if it matches any of those four fields then that row needs to be displayed. I've tried OR (OR Day2Date...OR Day3Date) but that didn't work, gave me repeats of rows where the dates overlapped.

Help please! :)

TIA

Link to comment
Share on other sites

Not sure if the below will help, but give it a go. For reference, i have used LEFT JOIN and UNION, you can check them out the mysql manual. Check my spelling etc of your field/table names.

[a href=\"http://dev.mysql.com/doc/refman/5.0/en/union.html\" target=\"_blank\"]UNION SYNTAX[/a]

[a href=\"http://dev.mysql.com/doc/refman/5.0/en/join.html\" target=\"_blank\"]JOIN SYNTAX[/a]

[code]$q = "(SELECT * FROM SaleListing LEFT JOIN SaleListing.SaleCity ON Cities.CityID WHERE Day1Date = '$searchdate') UNION
(SELECT * FROM SaleListing LEFT JOIN SaleListing.SaleCity ON Cities.CityID WHERE Day2Date = '$searchdate') UNION
(SELECT * FROM SaleListing LEFT JOIN SaleListing.SaleCity ON Cities.CityID WHERE Day3Date = '$searchdate') UNION
(SELECT * FROM SaleListing LEFT JOIN SaleListing.SaleCity ON Cities.CityID WHERE Day4Date = '$searchdate')";
$r = mysql_query($q) or die(mysql_error());[/code]
Link to comment
Share on other sites

Thanks Sanfly! I didn't know about UNION - I messed with it a bit and it ended up working like this:

$query = "SELECT * FROM SaleListing l LEFT JOIN Cities c ON l.SaleCity=c.CityID WHERE l.Day1Date = '".$searchdate."' AND c.CityID=".$SaleCity."
UNION

SELECT * FROM SaleListing l LEFT JOIN Cities c ON l.SaleCity=c.CityID WHERE l.Day2Date = '".$searchdate."' AND c.CityID=".$SaleCity."

UNION

SELECT * FROM SaleListing l LEFT JOIN Cities c ON l.SaleCity=c.CityID WHERE l.Day3Date = '".$searchdate."' AND c.CityID=".$SaleCity."

UNION

SELECT * FROM SaleListing l LEFT JOIN Cities c ON l.SaleCity=c.CityID WHERE l.Day4Date = '".$searchdate."' AND c.CityID=".$SaleCity;
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.