mysql syntax query

[blue-genie]

I have the following situation.

I have a bunch of instances of a game in the database. The games can not overlap date wise.

with the insert I've managed to check if the date range is clear do the insert, however I now need to introduce an edit function, and I'm hitting a bit of a hurdle.

 

assume i have an entry call var1 whose startDate is 2010-07-01 and endDate is 2010-07-30

i have another entry var2 whose startDate is 2010-06-10 and endDate is 2010-06-15

 

i now get the var2 back to edit it and I want to change the endDate to 2010-06-30 which in theory is fine because no other entry is within that time range, however my current query

 

$result = mysql_query("select count(*)  as count FROM gameDetails WHERE startDate<=Date('".$newStartDate."') AND endDate>=DATE(' ".$newStartDate." ') OR startDate<=DATE(' ".$newEndDate." ') AND endDate>=DATE(' ".$newEndDate." ') " );

 

will return 1 because it counts itself right (var2)

 

so my question is this - I do a quick hack and check that if($row[count]>1) or is there a better way to write this script?

 

blu

 

[blue-genie]

i know the ID of the item i'm editing so i tried this

 

$result = mysql_query("select count(*)  as count FROM gameDetails WHERE gameID <> $editGameID AND startDate<=Date('".$newStartDate."') AND endDate>=DATE(' ".$newStartDate." ') OR startDate<=DATE(' ".$newEndDate." ') AND endDate>=DATE(' ".$newEndDate." ') " );

 

is this the best practice way of going about this?

[JustLikeIcarus]

Since you will have the primary key of the record your editing you could just add a section to your select excluding that record from your count.

[blue-genie]

@JustLikeIcarus thanks I did that (see my second post) and it does seem to work, but I just wanted to confirm if there was a tidier way of doing it.

thanks.

blu