newbie que: date functions

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!

Moderator: General Moderators

Post Reply
phoggy
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2003 2:03 pm

newbie que: date functions

Post 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)&#123;
echo $nsn_number . '<br />' . $description . '<br />' ; 
$count++;
$sql = mysql_query("UPDATE users SET count='$count', last_search=now() WHERE username='$username' AND password='$password'"); 
&#125;
else&#123;
if($time_remaining < 86400)&#123;
echo 'There are no more searches allowed for your account';
echo 'You have '.$time_remaining.' until you can search the database again.';&#125;
else &#123;
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'"); &#125;
&#125; 
&#125; 
mysql_free_result($result) or die(mysql_error()); 
&#125; else &#123; 
echo 'Sorry, your search: ' . $q . ' returned zero results'; 
&#125;
Last edited by phoggy on Wed Dec 10, 2003 2:58 pm, edited 1 time in total.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
phoggy
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2003 2:03 pm

Post 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!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
phoggy
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2003 2:03 pm

Post by phoggy »

THank you, thank you sooo much. The time works perfect now. :D
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!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
phoggy
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2003 2:03 pm

Post by phoggy »

THAAAAANK YOU! You ARE the best!
phoggy
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2003 2:03 pm

Post by phoggy »

Okay I'm back. Still a problem. It updated the database once and then no more. What is wrong now??
THX
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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]
phoggy
Forum Newbie
Posts: 20
Joined: Tue Sep 16, 2003 2:03 pm

Post 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!
Post Reply