PHP - Subtracting Dates With Dates Stored As Datetime In Mysql?

Hi,
Whenever it comes to dates I go blank and just hear/read "blah blah blah".  I'm sure there's a name for a condition like that but it's causing me a problem right now.

I am making a RSS feed aggregator, inserting RSS feed posts into a mysql db and want to be able to extract those posts in order by date. (so that I can create a new feed of the last posts of multiple rss feeds)

My problem is that the pubDate format for feeds is Fri, 29 Oct 2010 11:22:58 +0000 and that doesn't work with mysql.

Apart from having to reformat the date I have to take into consideration the +0000 part could be different depending on where in the world the feed was published.

Any pointers would be gratefully received!

Hi,

I'm trying to get the records that are between two dates to show on my screen, right now nothing comes up my code I'm using is below

Code: [Select]
$today = date('Y-m-d');
$lastweek = date('Y-m-d',strtotime('-7 days'));
$sql = "SELECT * from `memos` WHERE `date` BETWEEN '$today' AND '$lastweek' AND `delete` !='1'";
$result = mysql_query($sql);
echo "<ol>";
while ($row = mysql_fetch_array($result)) {
echo "<li>".$row['memo']."</li>" ;
}
echo "</ol>";

I am trying to create a simple calendar where a user can add/remove their availability date. This part works so far. Now what I am having trouble with is highlighting the already selected dates that are fetched from a MySQL database.

 

Here's my code.

// HTML
<div id="datetimepicker1"></div>

// Jquery/Ajax

<script>
$(document).ready(function () {

  $('#datetimepicker1').datepicker({
    dateFormat: "yy-mm-dd",
    multidate: true,
    onSelect: function () {
        var getDate = $("#datetimepicker1").val();

        $.ajax({
             type: "POST", //or GET. Whichever floats your boat.
             url: "snippets/adapter-set.php",
             data: { date: getDate },
             success: function(data) {
              // alert(data);
             },
             error: function() {
                 alert("Error.");
             }
        });
    }
  });
});
</script>

// PHP
// adapter-set.php

$post_date = $_POST['date'];

$find_query = $db->prepare("SELECT user_id FROM user_dates WHERE date_available = :date_available");
$find_query->bindParam(':date_available', $post_date);
$find_query->execute();
$result_find = $find_query->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find) > 0) {
  foreach($result_find as $row) {
    $user_id =	$row['user_id'];
  }
  $delete_query = $db->prepare("DELETE FROM user_dates WHERE user_id = :user_id");
  $delete_query->bindParam(':user_id', $user_id);
  $delete_query->execute();
  $result_delete = $delete_query->execute();
  if($result_delete == false) {
   echo 'delete false';
  } else {
   echo 'delete success';
  }
} else {
  $insert_query = $db->prepare("INSERT INTO user_dates(date_available) VALUES(:date_available)");
  $insert_query->bindParam(':date_available', $post_date);
  $result_insert = $insert_query->execute();
  if($result_insert == false) {
   echo 'insert false';
  } else {
   echo 'insert success';
  }

}

 

The above code works fine. It inserts and deletes a date row in MySQL database table based on a click.

Now what I would like to do is to highlight all the "available" dates that are already inserted into the database; so that the user knows which dates he has already selected. This is my code for that. But it doesn't seem to be working. No errors. It's just not highlighting the inserted dates. Can you tell me what I'm doing wrong?

// JQUERY
<script>
    $(document).ready(function() {

        $.post('snippets/adapter-fetch.php', {}, function(data){
            $("#datetimepicker1").datepicker({
              datesEnabled : data.datesEnabled
            });
        }, 'json');

    });
</script>

// PHP
// adapter-fetch.php

$global_user_id = 5;

$find_query = $db->prepare("SELECT date_available FROM user_dates WHERE user_id = :user_id");
$find_query->bindParam(':user_id', $global_user_id);
$find_query->execute();
$result_find = $find_query->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find) > 0) {
  foreach($result_find as $row) {
    $date_available =	$row['date_available'];

    echo $date_available;
  }
} else {
  echo 'not dates available';
}

 

I just have a general question about when other programmers remove rows older than say a year old....

Do most of you:
1) set up a CRON job to run daily at 3:00am
2) Add a routine at login for each user
3) Add a routine when a user moves into a certain portion of the application
4) A maintenance routine that an administrator manually has to envoke

Or is there another way...I have done some research but most of the things google has showed me is the how not when to do this...

guys, im having a problem here...

i tried too many ways to convert dates in american format, to brazilian format, but no one is working...

how can i do that, to convert it to dd/mm/yyyy (it comes from my db in mysql)...
 

Hey, I have just recently coded a forum and my topics are ordered by date. This means if there is a topic at 4pm and then there is another topic made at 4:01pm the topic which was posted at 4:01pm will be listed at the top.

I know how to add timezones but there is bit of a problem,  if  person from the UK posts a topic and his/hers timezone is set to:

date_default_timezone_set('Europe/London');()

this means the time at which the topic was posted will be 20:36pm (Just a randome time example).

However if someone posted a topic from america/los_angeles one hour before the one above his/her time will be 11:36 am. Even though this topic was posted one hour before the UK post. The UK topic will override it because it is 8:36pm. In fact, all UK topics will show up at the top and all the america/los_angeles topics will show bellow.

How do I slove this problem? I just don't get it even tough I looked online.

Please help, thanks.

Hi,
So I only want my users to be able to perform certain tasks each 12 hours.
This is the code I use:
Code: [Select]
function canVote($ip, $vote_id, $updateTimer = true){
$time = date("Y-m-d H:i:s");

$this->CI->db->where('vote_id', $vote_id);
$this->CI->db->where('user_ip', $ip);
$this->CI->db->from('votes_voters');
$count =  $this->CI->db->count_all_results();
/*$count = $this->CI->db
->where('vote_id =', $vote_id)
->where('user_ip =', $ip)
->from('votes_voters')
-count_all_results();*/

$row = $this->CI->db
->where('vote_id =', $vote_id)
->where('user_ip =', $ip)
->get('votes_voters')
->row();

if($count == 0){
$data = array(
   'vote_id' => $vote_id,
   'user_ip' => $ip,
   'last_vote' => $time
);

$this->CI->db->insert('votes_voters', $data);
return true;
}
else{
$last_vote = $row->last_vote;
if($last_vote + strtotime("12 hours") < $time){
return false;
}
else{
if($updateTimer = true){
$data = array(
               'last_vote' => $time,
            );

$this->CI->db->where('vote_id', $vote_id);
$this->CI->db->where('user_ip', $ip);
$this->CI->db->update('votes_voters', $data);
}
return true;
}
}
}
Apparently the failing bit is this:
Code: [Select]
if($last_vote + strtotime("12 hours") < $time){
return false;
}
I believe you can guess what I'm trying to do here, if variable 1 + 12 hours is smaller than variable 2, then return false.
Any help is much appreciated.

i want to created a case like the following need help with the syntax:
Code: [Select]
switch (true)
{
case($fromdate-$todate==17-04-2001-25-04-2011):
echo $finalprice = $result;
        break;
}

Hi guys,

I am trying to create a program which manages campaigns.
each campaign has a start date and and end date.
a user has to enter data releated to the campaign everyday

example
start date: 10-1-2011
end date: 17-1-2011

now all this information is stored in 2 tables
1st table is "campaigns" this stores the campaign name, start date and end date
and 2nd table is "campaign_data" this table stored the data for each campaign and also date of when that data was entered

my question is, if the user did not enter data on 11-1-2011 how will I know this, keeping in mind the month diffrence which can accur if the length of the campign was 2 month long?


table:
campaigns
fields:
id | campaign_name | start_date | end_date

table:
campaign_data
fields:
id | campaign_id | date | page

Thank you

Hey guys,

What I'm trying to do is set 2 dates.  Today's date and then a date 14 days from now.  However, I want it to increase the month if by adding 14 days will bring me to the next month, same with the year.

I have this, but it doesn't increment the month or year.

$week = mktime(11, 59, 59, date("m"), date("d")+14, date("y"));
$date =  date("Y-m-d H:i:s", $week);

i have installed a calendar
i want to extract the available id_item that are not id_state=1(booked)

i want to query all the items that are not booked the dates:
$fromdate=22-03-2011;
$todate=25-03-2011;


Code: [Select]
this is bookings.php
mysql_select_db($database_international, $international);
$query_RsBookingIO = sprintf("SELECT * FROM `bookings` LEFT JOIN bookings_items ON bookings.id_item = bookings_items.id WHERE dateDIFF (2011-03-22, 2011-03-25) and bookings.id_state ='1' LIMIT 0 , 30  ", GetSQLValueString($colname_RsBookingIO, "int"));
$RsBookingIO = mysql_query($query_RsBookingIO, $international) or die(mysql_error());
$row_RsBookingIO = mysql_fetch_assoc($RsBookingIO);

<?php do { echo $row_RsBookingIO['the_date'];  ?>
 
  <?php } while ($row_RsBookingIO = mysql_fetch_assoc($RsBookingIO)); ?>

now when creating the searchfunction.php

<?php
 $data = file_get_contents('http://localhost/bookings.php?fromdate=$fromdate and todate=$todate');
$fromdate = $_GET['fromdate'];
$todate = $_GET['todate'];
$dates = explode(' ',$data);
var_dump($data);
foreach($dates as $aDate) {
echo $aDate;
//$aDate = '\''.$aDate.'\'';
if (!in_array($aDate, $dates)) {
$available = true;
} 
}
return $available;
?>

//tables
CREATE TABLE `bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_item` int(20) NOT NULL DEFAULT '0',
  `the_date` date NOT NULL DEFAULT '0000-00-00',
  `id_state` int(11) NOT NULL DEFAULT '0',
  `id_booking` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id_item` (`id_item`),
  KEY `id_state` (`id_state`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

--
-- Dumping data for table `bookings`
--

INSERT INTO `bookings` VALUES(1, 2, '2011-03-22', 1, 0);
INSERT INTO `bookings` VALUES(3, 2, '2011-03-23', 1, 0);
INSERT INTO `bookings` VALUES(4, 2, '2011-03-24', 1, 0);
INSERT INTO `bookings` VALUES(5, 2, '2011-03-25', 1, 0);
INSERT INTO `bookings` VALUES(6, 2, '2011-03-26', 1, 0);
INSERT INTO `bookings` VALUES(7, 3, '2011-03-23', 1, 0);
INSERT INTO `bookings` VALUES(8, 3, '2011-03-24', 1, 0);
INSERT INTO `bookings` VALUES(9, 3, '2011-03-25', 1, 0);
INSERT INTO `bookings` VALUES(10, 3, '2011-03-26', 1, 0);
INSERT INTO `bookings` VALUES(11, 3, '2011-03-27', 1, 0);


CREATE TABLE `bookings_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL DEFAULT '1',
  `id_ref_external` int(11) NOT NULL COMMENT 'link to external db table',
  `desc_en` varchar(100) NOT NULL DEFAULT '',
  `desc_es` varchar(100) NOT NULL DEFAULT '',
  `list_order` int(11) NOT NULL DEFAULT '0',
  `state` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `id_user` (`id_user`),
  KEY `id_ref_external` (`id_ref_external`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `bookings_items`
--

INSERT INTO `bookings_items` VALUES(1, 1, 4600, 'Suite_A', 'Suite_A', 2, 1);
INSERT INTO `bookings_items` VALUES(2, 1, 4601, 'Suite_B', 'Suite_B', 2, 1);

I have this code that compares date times but im not sure if its correct.

if("2008-08-10 00:00:00.0" > strtotime("now")) {
        echo "here";
    }


The code echo's here, but it shouldn't because the time is less than the current time. Any ideas?

Hello Everyone,

I seem to be going no place fast...

In a PHP file, I should be taking a date from a jquery datpicker, and get it to a format that mysql will read in the yy-mm-dd format with...

if(isset($_POST['choice']))
$choice = (isset($_POST['choice'])) ? date("Y-m-d",strtotime($_POST['choice'])) : date("Y-m-d");

Can't seem to get a single inline(embeded) jquery Datepicker date to post right to the PHP and mysql correctly to return data from the table.. My reading has me to understand it should be right, but it is not. Now the datepicker select does have several POST events to PHP that trigger from the single select... data being requsted in PHP by the day, month, and year as well as PHP to use the date to create a highcharts graph in the PHP file to select data for hours of a day, days of a month, and months of a year.

The Table is very simple... date, time, power

Here is the PHP for dayPower.php that will just collect the sum data for the selected day in the datepicker...

<?
if(isset($_POST['choice']))
$choice = (isset($_POST['choice'])) ? date("Y-m-d",strtotime($_POST['choice'])) : date("Y-m-d");
$con = mysql_connect("localhost","root","mackie1604"); 
if (!$con)  { 
die('Could not connect: ' . mysql_error()); 
} 
mysql_select_db("inverters", $con); 

$sql = 'SELECT sum(power) AS power '        
.'FROM feed '       
.'WHERE date = $choice';
$res = mysql_query($sql) or die('sql='.$sql."\n".mysql_error());
$row = mysql_fetch_assoc($res);
echo $row['power']; 
?>

Here is the javascript that sends the date and how it post the PHP file.

Code: [Select]
$(document).ready(function () {   
      $('#datepicker').datepicker({onSelect: function(dateText,inst) {
            var myDate = $(this).datepicker('getDate');
            $('#apDiv1').html($.datepicker.formatDate('DD, d', myDate));
            $('#apDiv5').html($.datepicker.formatDate('MM', myDate));
            $('#apDiv7').html($.datepicker.formatDate('yy', myDate));
            $.post('dayPower.php', {choice: inst.val()}, function(data) {
                $('#apDiv2').html(data).show();
            });
            $.post('dayGraph.php', {choice: inst.val()}, function(data) {
                  $('#apDiv4').html().show();
});
$.post('monthPower.php', {choice: inst.val()}, function(data) {
                $('#apDiv6').html(data).show();
            });
            $.post('monthGraph', {choice: inst.val()}, function(data) {
                  $('#apDiv9').html().show();
});
$.post('yearPower.php', {choice: inst.val()}, function(data) {
                $('#apDiv8').html(data).show();
            });
            $.post('yearGraph', {choice: inst.val()}, function(data) {
                  $('#apDiv10').html().show();
});
      }});
});

What am I doing wrong

Alan

Hi, I have a created comment system, now what i am after is when the comment is posted the time is posted in hidden format. All is going fine but what i don't know, what should the time be posted as example date(H:m:s) or... the result i want is time in minutes first e.g.
commented 2 mins ago and increasing to hours after that days. Could some one kindly guide me in the right directions.