need help with a select statement and dates please

[blue-genie]

I've been at this for a couple of hours now and I'm not winning.

 

i have a table with gameID and startDate and endDate (the date fields are date type)

 

i'm trying to edit a game. but when i edit it i need to check the new start and end dates that it doesn't conflict with any other existing games, ie. on any given day only 1 game instance can exist.

 

 

i'm not a php developer but the next best thing we've got right now so i'm really struggling with this.

could someone please help.

 

<?php

session_start();

include 'config.php';

include 'opendb.php';

 

$newGameName = $_REQUEST['newGameName'];

    $newStartDate = $_REQUEST['newStartDate'];

$newEndDate = $_REQUEST['newEndDate'];

$newTurnsPerDay = $_REQUEST['newTurnsPerDay'];

$newActiveState = $_REQUEST['newActiveState'];

$newNumAttempts = $_REQUEST['newNumAttempts'];

$editGameID = $_REQUEST['editGameID'];

 

// $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')");

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

$num_rows = 0;

//'2010-5-26' BETWEEN startDate AND endDate

 

 

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

 

$row = mysql_fetch_array($result, MYSQL_ASSOC);

 

$num_rows = $row[count];

 

if ($num_rows > 0)

{

echo '<?xml version="1.0"?>';

echo '<dataxml>';

echo '<row type="error">';

echo "<errorMsg>Cannot update game: ".$newGameName." \nThe new dates conflict with an existing game.</errorMsg>";

echo "</row>";

echo '</dataxml>';

} else {

$result2 = mysql_query("UPDATE gamedetails SET gameName = '".$newGameName."', startDate = '".$newStartDate."', endDate = '".$newEndDate."', turnsPerDay = ".$newTurnsPerDay.", numAttempts =".$newNumAttempts.", gameIsActive = '".$newActiveState."' WHERE gameID = '".$editGameID."'");

 

echo '<?xml version="1.0"?>';

echo '<dataxml>';

echo '<row type="success">';

echo '<result>success</result>'; 

echo '</row>';

echo '</dataxml>';

}

 

 

 

?>