Jump to content


Photo

Need help with a query


  • Please log in to reply
3 replies to this topic

#1 skallsen

skallsen
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 28 April 2006 - 05:52 AM

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



#2 sanfly

sanfly
  • Members
  • PipPipPip
  • Advanced Member
  • 344 posts
  • LocationNew Zealand

Posted 28 April 2006 - 10:02 AM

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]

$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());

If you're not part of the solution, you're part of the precipitate

#3 skallsen

skallsen
  • New Members
  • Pip
  • Newbie
  • 7 posts

Posted 28 April 2006 - 05:11 PM

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;


#4 ober

ober
  • Staff Alumni
  • Advanced Member
  • 5,337 posts
  • LocationEast Coast, USA

Posted 28 April 2006 - 07:52 PM

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

Info: PHP Manual





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users