By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,526 Members | 2,189 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,526 IT Pros & Developers. It's quick & easy.

A little help with POST and DATE fields

P: 2
Hi,
I have a php form that I want to use to pass a user specified "start date" and "end date" to a mysql database to retrieve reservations.

I want the number of days up to and including the "end date" to be displayed for both of these scenarios:
1) The item is still on loan as of the "end date"
2) The item has already been returned as of the "end date"

Right now I have it working by specifying a "start date", but the "end date" is actually set to the "current date". This would be fine, but I might need to go back and select all reservations in July, which would mean a whole lot of extra days being tallied up to today.

So, here is my code. The problem I am having is that the user input is a string, which I must convert before subtracting it from the start date. How do I do this?

Thanks in advance!

Expand|Select|Wrap|Line Numbers
  1. // Get start date and format properly for query string
  2. $v_startdate = $_POST['sdate'];
  3. $x_startdate = "'" . $v_startdate . "'" ;
  4.  
  5. // Get end date and format properly for query string
  6. $v_enddate = $_POST['edate'];
  7. $x_enddate = "'" . $v_enddate . "'" ;
  8.  
  9.  
  10.  
  11. // make connection to database
  12. mysql_connect($hostName, $userName, $password) or die("Unable to connect to host $hostName");
  13.  
  14. mysql_select_db($dbName) or die( "Unable to select database $dbName");
  15.  
  16. // 1st part - If the reservation is greater than or equal to the start date but ends before current date
  17. // 2nd part - If the reservation is greater than or equal to the start date but ends after current date, I want to see up to current date
  18. $query = "SELECT li.fname Name, li.lname Last_Name, rs.name Equipment, rv.summary Project, (TO_DAYS( from_unixtime( rv.end_date ) ) - TO_DAYS( from_unixtime( rv.start_date ) )) + 1 Days_Out , rs.notes ID_Cost
  19. FROM reservations rv, resources rs, reservation_users ru, login li
  20. WHERE rs.machid = rv.machid
  21. AND ru.resid = rv.resid
  22. AND li.memberid = ru.memberid
  23. AND from_unixtime( rv.end_date ) <= CURRENT_DATE() 
  24. AND from_unixtime(rv.start_date) >= $x_startdate
  25. UNION
  26. SELECT li.fname Name, li.lname Last_Name, rs.name Equipment, rv.summary Project, (TO_DAYS( CURRENT_DATE( ) ) - TO_DAYS( from_unixtime( rv.start_date ))) + 1 Days_Out,  rs.notes ID_Cost
  27. FROM reservations rv, resources rs, reservation_users ru, login li
  28. WHERE rs.machid = rv.machid
  29. AND ru.resid = rv.resid
  30. AND li.memberid = ru.memberid
  31. AND from_unixtime( rv.end_date ) > CURRENT_DATE() 
  32. AND from_unixtime( rv.start_date ) < CURRENT_DATE()
  33. AND from_unixtime(rv.start_date) >= $x_startdate
  34. ORDER BY 4";
  35.  
  36.  
  37. $result = mysql_query($query);
  38.  
  39. // Determine the number of reservations
  40. $number = mysql_numrows($result);
  41.  
  42. // print the records
  43. print "<h3><font face=Arial>There are $number records between $v_startdate and $v_enddate:</h3>
  44.     <table cellpadding=5>
  45.     <tr bgcolor=black>
  46.       <td><font color=white><b>Name</b></font></td>
  47.       <td><font color=white><b>Equipment</b></font></td>
  48.       <td><font color=white><b>Project</b></font></td>
  49.       <td><font color=white><b>Days_Out</b></font></td>
  50.       <td><font color=white><b>ID_Cost</b></td></font></tr>";
  51.  
  52. for ($i=0; $i<$number; $i++) {
  53.      $Name = mysql_result($result,$i,"Name");
  54.      $Last_Name = mysql_result($result,$i,"Last_Name");
  55.      $Equipment = mysql_result($result,$i,"Equipment");
  56.      $Project = mysql_result($result,$i,"Project");
  57.      $Days_Out = mysql_result($result,$i,"Days_Out");
  58.      $ID_Cost = mysql_result($result,$i,"ID_Cost");   
  59. //     print "$a $b $c $d $e $f $g<br>";
  60.  
  61. if ($i%2 == 0) {
  62.         print "<tr bgcolor=lightgrey>";
  63.         } else {
  64.         print "<tr>";
  65.         }
  66.     print "<td>$Name $Last_Name</td>
  67.     <td>$Equipment</td>
  68.     <td>$Project</td>
  69.     <td align = center>$Days_Out</td>
  70.     <td>$ID_Cost</td></tr>"; 
  71. }
  72. print "</table>";
  73.  
  74. //print " $query " ;
  75.  
  76.  
  77. // $timestampRightNow = time();
  78. // print "$timestampRightNow";
  79. print $CURRENT_DATE;
  80.  
  81. // Close the database connection
  82. mysql_close();
  83. ?>
  84.  
  85. <p>
  86.  
Oct 13 '07 #1
Share this Question
Share on Google+
3 Replies


ak1dnar
Expert 100+
P: 1,584
You can use checkdate() function for validate the date format.
Tip: Better to set up list menus to select dd mm yyyy, instead of a text input box.
Oct 13 '07 #2

P: 2
Thanks, I got it working :o)
Oct 14 '07 #3

ak1dnar
Expert 100+
P: 1,584
Thanks, I got it working :o)
Glad to here you got it working. And If you can post the answer here, It will help for the others, who is having the same problem. Thanks.
Oct 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.