?
Solved

Update database in PHP

Posted on 2011-02-21
4
Medium Priority
?
526 Views
Last Modified: 2012-05-11
I'm using Joomla and want to add an item to my update statement. My PHP isn't great and I'm having some trouble figuring out the added Joomla code. I have a field in my DB called "post_date" and when the rest of the fields are changed I want to update this field to the current date. Can someone show me how to alter the below statement to do this? The entire page code is below which contains some other other helpful info if needed.

Thanks



Want to update field post_date with current date:

function save($job) {
      $tag_var = 'tag abcd';
        $db =& $this->getDBO();
            $this->_query = "UPDATE ".$db->nameQuote('#__jobboard_jobs')."
                     SET ".$db->nameQuote('job_title')."=".$db->Quote($job->job_title)."
                     , ".$db->nameQuote('expiry_date')."='".$job->expiry_date."'
                     , ".$db->nameQuote('job_type')." =".$db->Quote($job->job_type)."
                     , ".$db->nameQuote('category')." =".$db->Quote($job->category)."
                     , ".$db->nameQuote('career_level')." =".$db->Quote($job->career_level)."
                     , ".$db->nameQuote('education')." =".$db->Quote($job->education_level)."
                     , ".$db->nameQuote('positions')." =".$db->Quote($job->positions)."
                     , ".$db->nameQuote('country')." =".$db->Quote($job->country_name)."
                     , ".$db->nameQuote('department')." =".$db->Quote($job->department)."
                     , ".$db->nameQuote('published')." =".$db->Quote($job->published)."
                     , ".$db->nameQuote('city')." =".$db->Quote($job->city)."
                     , ".$db->nameQuote('salary')." =".$db->Quote($job->salary)."
                     , ".$db->nameQuote('description')." =".$db->Quote($job->job_description)."
                     , ".$db->nameQuote('duties')." =".$db->Quote($job->duties)."
                     , ".$db->nameQuote('job_tags')." =".$db->Quote($job->job_tags)."

                 WHERE  ".$db->nameQuote('id')."=".intval($job->id);
        $db->setQuery($this->_query);
        return $db->query();
    }
<?php
/**
 * @package JobBoard
 * @copyright Copyright (c)2010 Tandolin
 * @license GNU General Public License version 2, or later
 */

defined('_JEXEC') or die('Restricted access');
jimport('joomla.application.component.model');

class JobboardModelJob extends JModel
{
	var $_total;
    var $_id;
	var $_query;
	var $_data;

	function __construct()
	{
		parent::__construct();

	    $cid = JRequest::getVar('cid', false, 'DEFAULT', 'array');
        if($cid){
          $id = $cid[0];
        }
        else $id = JRequest::getInt('id', 0);
        $this->setId($id);
	}

    function setId($id=0)
    {
      $this->_id = $id;
      $this->_query = null;
      $this->_data = null;
      $this->_total = null;
    }

	function getJob()
	{

		if(empty($this->_data))
		{
            $db = JFactory::getDBO();
			$this->_query = 'SELECT j.post_date
					  , j.expiry_date
                      , j.job_title
                      , j.job_type
                      , j.country
                      , j.salary
                      , c.id AS catid
                      , c.type AS category
                      , jc.country_name
                      , jc.country_region
                      , cl.description AS job_level
                      , j.id AS job_id
                      , j.description
                      , j.positions
                      , j.city
                      , j.num_applications
                      , j.hits
                      , e.level AS education
                  FROM
                      #__jobboard_jobs AS j
                      INNER JOIN #__jobboard_categories  AS c
                          ON (j.category = c.id)
                      INNER JOIN #__jobboard_career_levels AS cl
                          ON (j.career_level = cl.id)
                      INNER JOIN #__jobboard_education AS e
                          ON (e.id = j.education)
                      INNER JOIN #__jobboard_countries AS jc
                          ON (j.country = jc.country_id)
                      WHERE j.id = ' . $this->_id;
            $db->setQuery($this->_query);
            $this->_data = $db->loadObject();
		}

		return $this->_data;
	}

    function update($data) {
        $db = JFactory::getDBO();
		$this->_query = "UPDATE #__jobboard_applicants
                     SET email ='".$data->email."'
                     , tel ='".$data->tel."'
                 WHERE id=".$data->id;
        $db->setQuery($this->_query);
        return $db->query();
    }

    function save($job) {
	$tag_var = 'tag abcd';
        $db =& $this->getDBO();
		$this->_query = "UPDATE ".$db->nameQuote('#__jobboard_jobs')."
                     SET ".$db->nameQuote('job_title')."=".$db->Quote($job->job_title)."
                     , ".$db->nameQuote('expiry_date')."='".$job->expiry_date."'
                     , ".$db->nameQuote('job_type')." =".$db->Quote($job->job_type)."
                     , ".$db->nameQuote('category')." =".$db->Quote($job->category)."
                     , ".$db->nameQuote('career_level')." =".$db->Quote($job->career_level)."
                     , ".$db->nameQuote('education')." =".$db->Quote($job->education_level)."
                     , ".$db->nameQuote('positions')." =".$db->Quote($job->positions)."
                     , ".$db->nameQuote('country')." =".$db->Quote($job->country_name)."
                     , ".$db->nameQuote('department')." =".$db->Quote($job->department)."
                     , ".$db->nameQuote('published')." =".$db->Quote($job->published)."
                     , ".$db->nameQuote('city')." =".$db->Quote($job->city)."
                     , ".$db->nameQuote('salary')." =".$db->Quote($job->salary)."
                     , ".$db->nameQuote('description')." =".$db->Quote($job->job_description)."
                     , ".$db->nameQuote('duties')." =".$db->Quote($job->duties)."
                     , ".$db->nameQuote('job_tags')." =".$db->Quote($job->job_tags)."

                 WHERE  ".$db->nameQuote('id')."=".intval($job->id);
        $db->setQuery($this->_query);
        return $db->query();
    }
    function savenew($job) {
       $db =& $this->getDBO();
		$this->_query = "INSERT INTO #__jobboard_jobs
                    (post_date, job_title, expiry_date, job_type, category, career_level, education, positions, country, department, published, city, salary, description, duties, job_tags)
                     VALUES (UTC_TIMESTAMP, '".$db->getEscaped($job->job_title, true)."'
                     , '".$db->getEscaped($job->expiry_date, true)."'
                     , '".$job->job_type."'
                     , ".$db->getEscaped($job->category, true)."
                     , ".$db->getEscaped($job->career_level, true)."
                     , ".$db->getEscaped($job->education_level, true)."
                     , ".$db->getEscaped($job->positions, true)."
                     , ".$db->getEscaped($job->country_name, true)."
                     , ".$db->getEscaped($job->department, true)."
                     , ".$db->getEscaped($job->published, true)."
                     , '".$db->getEscaped($job->city, true)."'
                     , '".$db->getEscaped($job->salary, true)."'
                     , '".$db->getEscaped($job->job_description, true)."'
                     , '".$db->getEscaped($job->duties, true)."'
                     , '".$db->getEscaped($job->job_tags, true)."'
                 )";
        $db->setQuery($this->_query);
        return $db->query();
    }

}
?>

Open in new window

0
Comment
Question by:elliottbenzle
  • 4
4 Comments
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 34946704
I believe that you can define your table to have a DATETIME field that always, automatically contains the last updated information.

But that said, you could do something like this...

// GET THE ISO8601 DATE
$post_date = date('c');

... and add a line like this into the constructs posted above...

, ".$db->nameQuote('job_type')." =".$db->Quote($post_date)."
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34946716
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34946721
And my apologies for the typo above.  Change this:

, ".$db->nameQuote('job_type')." =".$db->Quote($post_date)."

into this (with the correct column name)

, ".$db->nameQuote('post_date')." =".$db->Quote($post_date)."
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 34946726
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article discusses how to create an extensible mechanism for linked drop downs.
I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question