PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
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.
$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';
}
Last edited by phoggy on Wed Dec 10, 2003 2:58 pm, edited 1 time in total.
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]
//$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.
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!
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.
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!
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.
$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:
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!