Page 1 of 1
newbie que: date functions
Posted: Wed Dec 10, 2003 11:31 am
by phoggy
Hi all,
I have a simple keyword search that is limited to 3 searches every 24 hours. Have no idea how to use the date functions to make it work. Not sure if I'm even on the right track here. Can someone help? TIA.
Code: Select all
$query = "SELECT nsn_number, description, count, last_search
FROM nsninfo, users WHERE nsn_number='$q' AND username='$username' AND password='$password'";
$result = mysql_query($query, $conn) or die(mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
extract($row);
$time_remaining = abs( date() - $last_search);
if($count < 3){
echo $nsn_number . '<br />' . $description . '<br />' ;
$count++;
$sql = mysql_query("UPDATE users SET count='$count', last_search=now() WHERE username='$username' AND password='$password'");
}
else{
if($time_remaining < 86400){
echo 'There are no more searches allowed for your account';
echo 'You have '.$time_remaining.' until you can search the database again.';}
else {
echo $nsn_number . '<br />' . $description . '<br />' ;
//if more than 24hrs have passed, reset count and start new 24hr period.
$sql = mysql_query("UPDATE users SET count='1', last_search=now() WHERE username='$username' AND password='$password'"); }
}
}
mysql_free_result($result) or die(mysql_error());
} else {
echo 'Sorry, your search: ' . $q . ' returned zero results';
}
Posted: Wed Dec 10, 2003 1:35 pm
by Weirdan
Code: Select all
$time_remaining = abs( date() - $last_search);
This line needs to be refined. [php_man]date[/php_man] function return string, also does mysql so it doesn't make much sense to substract: "01/01/2003 08:01:02 PM"-"2003-02-02 01:01:01" wouldn't produce any useful result. If you need to get current timestamp in PHP use the [php_man]time[/php_man] function instead of date. To get timestamp from the database use sql function [mysql_man]UNIX_TIMESTAMP[/mysql_man]
I'd suggest to rewrite the following lines:
Code: Select all
//$query = "SELECT nsn_number, description, count, last_search
//FROM nsninfo, users WHERE nsn_number='$q' AND username='$username' AND password='$password'";
$query = "SELECT nsn_number, description, count, UNIX_TIMESTAMP(last_search) as last_search
FROM nsninfo, users WHERE nsn_number='$q' AND username='$username' AND password='$password'";
//...skipped....
//$time_remaining = abs( date() - $last_search);
$time_remaining = abs( time() - $last_search);
//...rest of the script should work fine, I guess.
Posted: Wed Dec 10, 2003 2:13 pm
by phoggy
Hi Weirdan,
That is the line I am having the most problems with. Thank you for your suggestion, the number I am getting for $time_remaining makes a lot more sense now, but the script is still not working correctly. How can I format "'You have '.$time_remaining....' " so that it returns Hours:Minutes:Seconds? Also, my Sql update functions are not updating the database. Any ideas?
THX!
Posted: Wed Dec 10, 2003 2:41 pm
by Weirdan
phoggy wrote:How can I format "'You have '.$time_remaining....' " so that it returns Hours:Minutes:Seconds?
It's a bit tricky. AFAIK there is no such function in PHP, you need to implement it by yourself perhaps. Give it a try:
Code: Select all
function sec2time($secs){
$seconds=$secs%60;
$minutes=round(($secs-$seconds)/60)%60;
$hours=round(($secs-$seconds-$minutes*60)/3600);
return sprintf("%02d:%02d:%02d",$hours,$minutes,$seconds);
}
phoggy wrote:
Also, my Sql update functions are not updating the database. Any ideas?
You can't issue any queries on particular connection before you've fetched all the data from previous query. Either prefetch data into an array or use several connections.
Posted: Wed Dec 10, 2003 2:57 pm
by phoggy
THank you, thank you sooo much. The time works perfect now.
You can't issue any queries on particular connection before you've fetched all the data from previous query. Either prefetch data into an array or use several connections.
I have NO idea how to do that. Could you give me some more hints, code, etc...?
THX!
Posted: Wed Dec 10, 2003 3:05 pm
by Weirdan
Instead of
Code: Select all
while($row = mysql_fetch_assoc($result)) {
use
Code: Select all
while($row = mysql_fetch_assoc($result)) $results[]=$row;
foreach($results as $row){
It's called `prefetch`. You don't need to modify remaining code, just this line. If you want to learn how to use several db connections at once, read the [php_man]mysql[/php_man] pages.
Posted: Wed Dec 10, 2003 3:12 pm
by phoggy
THAAAAANK YOU! You ARE the best!
Posted: Wed Dec 10, 2003 3:23 pm
by phoggy
Okay I'm back. Still a problem. It updated the database once and then no more. What is wrong now??
THX
Posted: Wed Dec 10, 2003 3:33 pm
by Weirdan
Code: Select all
$query = "SELECT nsn_number, description, count, last_search
FROM nsninfo, users WHERE nsn_number='$q' AND username='$username' AND password='$password'";
$result = mysql_query($query, $conn) or die(mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) $results[]=$row
foreach($results as $row){
extract($row);
$time_remaining = abs( date() - $last_search);
if($count < 3){
echo $nsn_number . '<br />' . $description . '<br />' ;
$count++;
$sql = mysql_query("UPDATE users SET count='$count', last_search=now() WHERE username='$username' AND password='$password'");
}else{
if($time_remaining < 86400){
echo 'There are no more searches allowed for your account';
echo 'You have '.$time_remaining.' until you can search the database again.';
}else{
echo $nsn_number . '<br />' . $description . '<br />' ;
//if more than 24hrs have passed, reset count and start new 24hr period.
$sql = mysql_query("UPDATE users SET count='1', last_search=now() WHERE username='$username' AND password='$password'");
}
}
}
mysql_free_result($result) or die(mysql_error());
} else {
echo 'Sorry, your search: ' . $q . ' returned zero results';
}
Just enclosed the code into php tags for eyecandy.
[edit]
Ok, I see the trouble: you're using prefetched $count in your UPDATE queries. Instead of:
Code: Select all
$count++;
$sql = mysql_query("UPDATE users SET count='$count', last_search=now() WHERE username='$username' AND password='$password'");
you need
Code: Select all
$sql = mysql_query("UPDATE users SET count=count+1, last_search=now() WHERE username='$username' AND password='$password'");
[/edit]
Posted: Wed Dec 10, 2003 4:03 pm
by phoggy
Ok, I see the trouble: you're using prefetched $count in your UPDATE queries.
Actually that query updated just fine even the way I had it, so as long as count is less than 3 it executes the if statement and the first query updating everything correctly until count hits 3 and time to switch to second query, which does not update anything.
THX.
...never mind, just realize it does not get to the second else statement. I think I need to look over the logic in my code first. Thank you.
Okay, sorry about the false alarm. forgot to change the date in my db so it would be more than 24 hrs and execute the second else...duh...Thanks again for your fantastic and fast help!