Inserting and Updating Records Using JDatabase in Joomla

When your doing simple single table inserts and updates you can use the JDatabase methods to do so. The advantages of using the JDatabase methods is it saves you time from hand coding your sql and will escape and correctly quote your inputs for you.

The following will insert a new record into a table:


$db = JFactory::getDBO();

//Create data object
$row = new JObject();
$row->title = 'The Title';
$row->author = 'Bob Smith';

//Insert new record into jos_book table.
$ret = $db->insertObject('jos_book', $row);
 
//Get the new record id
$new_id = (int)$db->insertid();

This will update an existing record:


$db = JFactory::getDBO();
//Create data object
$row = new JObject();
//Record to update
$row->rec_id = 200;
$row->title = 'The Title';
$row->author = 'Bob Smith';

//Update the record. Third parameter is table id field that will be used to update.
$ret = $db->updateObject('jos_book', $row,'rec_id');

You can also use JTable to insert and update records, but requires more initial setup since you have to create a new JTable class for each table you want to modify. Using JTable is preferred if table has many fields to update from a form submit. JTable will automatically bind the form fields to corresponding table fields using the bind() method.

Here is more info on using JTable.

Leave a comment

8 Comments.

  1. There is a way to use this code from an other script than module?

  2. Thank you for this info .. was looking for a while how to update object list…
    Kind regards

  3. Thanks for the helpful post. When updating a record, what if I don’t know what the record ID is? What could I add to your code to make that work?

    Thx.

  4. How to know, how many records got updated!?

  5. Would have been better if you could mention where to write this code? which methods to implement.
    Without this info this code is simply unusable.
    Cheers

  6. Hello, but whit this method how can I send a SQL instruction like “NOW()”, I was trying to insert it but that method insert it escaped…

  7. (Script called on form display)

    $error = JRequest::getVar(‘error’, “”);
    $error = base64_decode(strtr($error, ‘-_,’, ‘+/=’));
    //$error=urldecode($error);
    //if ($error==’1′) print_r(‘Error decelee :’.$error);
    //die();
    $duplicateForm = preg_replace(“##i”, “”, $formLayout);
    $user =& JFactory::getUser();
    $userid = $user->get(‘id’);
    // Get a db connection.
    $db = JFactory::getDbo();
    // Create a new query object.
    $query = $db->getQuery(true);

    $query->select (‘date_cotation’);
    $query->from (‘#__brvm_cotation_mp as a’);
    $query->order(‘date_cotation DESC’);
    $db->setQuery($query,0,1);
    $date = $db->loadResult();
    //print_r(‘requête 1 :’.$date);
    //die();

    $query = $db->getQuery(true);
    //Select all records from the user profile table where key begins with “custom.”.
    //Order it by the ordering field.
    $query->select (array(‘a.date_cotation’, ‘a.matiere_premiere’, ‘a.cours’, ‘a.commentaires’, ‘b.matiere_premiere as sticker’, ‘b.ordering as orders’));
    $query->from (‘#__brvm_cotation_mp as a’);
    $query->join (‘INNER’, ‘#__brvm_mp as b on (a.matiere_premiere=b.id)’);
    $query->where ($db->quoteName(‘a.date_cotation’).’=’.$db->quote($date));
    //$query->where ($db->quoteName(‘b.matiere_premiere’).’=’.$db->quote(’1′));
    $query->order(‘orders’);
    $query->order(‘a.matiere_premiere ASC’);

    //Reset the query using our newly populated query object.
    $db->setQuery($query);

    //print_r(‘requête 1 :’.$query);
    //die();

    //echo $query;
    //Load the results as a list of stdClass objects
    $results = $db->loadObjectList();
    ?>
    Gestion Cotation de Matière Première

    &nbsp

    Entrée la date:<input type="date" align="right" name="date" value="” maxlength=”20″ style=”width:135px;” required />

    Matière première
    Cours
    Commentaires

    <?php
    $i=1;
    foreach ($results as $row):
    echo '’;
    echo ”.$row->sticker.”;
    echo ‘cours.’” maxlength=”10″ style=”width:40px;” required />’;
    echo ‘commentaires.’” maxlength=”800″ style=”width:500px;” />’;
    echo ‘matiere_premiere.’” maxlength=”100″ style=”width:60px;”/>’;
    echo ”;

    $i=$i+1;
    endforeach;
    echo ”;
    echo ”;
    echo ”;

    (Script called on form process)

    $x=1;
    $nb=$_POST['nb'];
    $date1=”";
    $date2= date(“Y-m-d”, strtotime($_POST['date']));
    $user= $_POST['user'];
    //print_r(‘user ‘.$user);
    //die();

    // Get a db connection.
    $db = JFactory::getDbo();
    // Create a new query object.
    $query = $db->getQuery(true);

    $query->select (‘date_cotation’);
    $query->from (‘#__brvm_cotation_mp as a’);
    $query->join (‘INNER’, ‘#__brvm_mp as b on (a.matiere_premiere=b.id)’);
    //$query->where ($db->quoteName(‘b.matiere_premiere’).’=’.$db->quote(’1′));
    $query->where ($db->quoteName(‘a.date_cotation’).’=’.$db->quote($date2));
    $query->order(‘date_cotation DESC’);
    $db->setQuery($query,0,1);
    $date1 = $db->loadResult();
    //print_r(‘date a considerer’.$date2);
    //die();

    if ($date1 == “”)
    {
    $y=2;
    for ($n=1; $ngetQuery(true);
    // Insert columns.

    $columns = array(‘date_cotation’, ‘matiere_premiere’, ‘cours’, ‘commentaires’,'date_crea’, ‘user_id_crea’);

    // Insert values.

    $values = array($db->quote($date_cotation2), $db->quote($sticker), $db->quote($cours), $db->quote($commentaires),$db->quote($date_crea), $db->quote($user));

    // Prepare the insert query.
    $query
    ->insert($db->quoteName(‘#__brvm_cotation_mp’))
    ->columns($db->quoteName($columns))
    ->values(implode(‘,’, $values));

    // Reset the query using our newly populated query object.
    $db->setQuery($query);
    $querylong.=”requete ‘.$n.’ est ‘.$query.’”;
    //$querylong.=$querylong.$querylong1;
    //print_r(‘requête est ‘.$query);
    //die();
    $db->query();

    $x++;
    }
    }
    else
    {

    //$application = JFactory::getApplication();
    $errortext=”Echec… Ces données ont été dèjà saisies dans le système. Vous pouvez cependant les modifier.”;

    header(‘Location: index.php?option=com_rsform&formId=211&error=’.strtr(base64_encode($errortext), ‘+/=’, ‘-_,’));
    exit();
    }
    /*if($y==’2′){
    print_r($querylong);
    die();
    }*/

  8. insert data in database