Need help with a query

[skallsen]

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

[sanfly]

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]

[skallsen]

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;

[ober]

Just FYI, your database is very poorly designed. You need to look up normalization and split that stuff into multiple tables.