JRowan JRowan - 2 months ago 9
MySQL Question

querying dates in mysql returning nothing?

I am trying to query a database table that has values like so

id = 1
tablename = 1 (bigint)
timestampstart = 2011-08-06 13:54:17 (timestamp)
timestampend = 2011-08-06 14:54:17 (timestamp)


so I'm using this function to try to return the database name that the timestamp I provide is inbetween start and end timestamps

function getDatabase($timestamp){
$link = new MySQLi($host, $root, $password, $database);
$name = '';

$result = $link->query("SELECT `tablename` FROM `datas` WHERE `timestampstart` =< '$timestamp' AND `timestampend` >= '$timestamp'");
if($link->error){
return $link->error;
}
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
$name .= $row['tablename'];
}

return $name;
}else{
return 'no table';
}
}


so I call it like this

echo getDatabase('2011-08-06 13:54:50');


and all I get is 'no table' back
there is only that one row at the top where I defined the structure of my table, any help will be greatly appreciated thank you for your time.

Answer

Make use of UNIX_TIMESTAMP() function in your SELECT query to compare the timestamps. Your query should be like this:

SELECT `tablename` 
FROM `datas` 
WHERE UNIX_TIMESTAMP(timestampstart) <= UNIX_TIMESTAMP('$timestamp') 
AND UNIX_TIMESTAMP(timestampend) >= UNIX_TIMESTAMP('$timestamp')

Or, (a shorter query)

SELECT `tablename` 
FROM `datas` 
WHERE UNIX_TIMESTAMP('$timestamp') BETWEEN UNIX_TIMESTAMP(timestampstart) AND UNIX_TIMESTAMP(timestampend)

Apart from that, I can also see scope issue because $host, $root, $password and $database are not available in the scope of your function. Either use global or pass the database credentials to your getDatabase() function as function parameters.