Member Avatar for McLaren

Hi,

I have one simple problem.

PHP (CodeIgniter) code:

$query = $this->db->where('filename',$filename)->select('valid_until')->get('jos_reklama_banners');

$row = $query->row();
$valid_until = $row->valid_until;


if($valid_until > time())
{
	$sql = "UPDATE `jos_reklama_banners` SET valid_until = valid_until + ? WHERE `id` = ?";
	$query = $this->db->query($sql, array($days*86400 ,$filename));		//viena diena 86400 sekundziu
	//echo 'daugiau';
}
else  
{
	$sql = "UPDATE `jos_reklama_banners` SET valid_until = ? WHERE `filename` = ?";
	$query = $this->db->query($sql, array(time() + $days*86400 ,$filename));		//viena diena 86400 sekundziu
}
echo $this->db->last_query();

the table:

CREATE TABLE IF NOT EXISTS `jos_reklama_banners` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `filename` varchar(20) NOT NULL,
  `url` varchar(1000) NOT NULL,
  `clicks` int(11) NOT NULL,
  `valid_until` datetime NOT NULL COMMENT 'Iki kada rodomas baneris',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=29 ;

so the php generates this update code:

UPDATE `jos_reklama_banners` SET valid_until = 1300464965 WHERE `filename` = 'file.jpg'

but this code does not update the data. Probably datetime type in database does not understand unix time or something like that, the value in table is 0000-00-00 00:00:00

I tried at first timestamp datatype but also there is all zeros after update.

Edited by McLaren because: reduce php code
  • 1 Contributor
  • 1 Reply
  • 158 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by McLaren
Member Avatar for McLaren

finally made it, but for me code does not look nice, convertions from one format to another.. I think there should be a simpler way. But at least works.

$query = $this->db->where('filename',$filename)->select('valid_until')->get('jos_reklama_banners');
				
$row = $query->row();
$valid_until = $row->valid_until;

//echo $valid_until;

$this->load->helper('date');

if(mysql_to_unix($valid_until) > time())
{
	$sql = "UPDATE `jos_reklama_banners` SET valid_until = ? WHERE `filename` = ?";
	$query = $this->db->query($sql, array($this->unixToMySQL( mysql_to_unix($valid_until) + $days*86400 ) ,$filename));		//viena diena 86400 sekundziu
	//echo 'daugiau';
}
else  
{
	$sql = "UPDATE `jos_reklama_banners` SET valid_until = ? WHERE `filename` = ?";
	$query = $this->db->query($sql, array($this->unixToMySQL(time() + $days*86400) ,$filename));		//viena diena 86400 sekundziu
}
echo $this->db->last_query();

if($query)	//jei pavyko pratesimas atminusuojam kreditus
{
	
	$sql = "UPDATE `jos_reklama_credits` SET credit = ? WHERE `user_id` = ?"; 
	$credits  = $credits - $bannerPrice;
	$this->db->query($sql, array($credits ,$user->id));
	$return = 'c'.$credits; 
}
function unixToMySQL($timestamp)
	{
	    return date('Y-m-d H:i:s', $timestamp);
	}

and in database table I used type timestamp.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.