Jump to content

Archived

This topic is now archived and is closed to further replies.

skallsen

Need help with a query

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

Share this post


Link to post
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]

Share this post


Link to post
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;

Share this post


Link to post
Share on other sites
Just FYI, your database is very poorly designed. You need to look up normalization and split that stuff into multiple tables.

Share this post


Link to post
Share on other sites

×

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.