Actions

Talk

Difference between revisions of "Accessing the database using JDatabase"

From Joomla! Documentation

(Joomla Versions and Joomla DB)
(Undo revision 117014 by Faridv (talk))
Line 1: Line 1:
 +
Can anyone provide more info regarding insertObject()?
 +
I just have no idea of how to insert a row into a table. I tried to do the following but it just doesn't work:
 +
 +
$db = & JFactory::getDBO();
 +
$query =  "insert into #__my_table(field1, field2) values ('".$value1."','".$value2."')";
 +
echo $query;
 +
$db->setQuery( $query );
 +
return($db->Query());
 +
 +
Any ideas?
 +
 +
Thanks!
 +
 +
 +
 +
<source lang="php">
 +
/**
 +
*
 +
* $object->fieldname1 = 'value1';
 +
* $object->fieldname2 = 'value2';
 +
* $object->fieldname3 = 'value3';
 +
* $object->fieldname4 = 'value4';
 +
* $object->fieldname5 = 'value5';
 +
*
 +
* $table = '#__tablename';
 +
*
 +
*/
 +
 +
$db = JFactory::getDBO();
 +
 +
$db->insertObject($table, $object, 'id');
 +
 +
/**
 +
*
 +
* The query will be generated:
 +
*
 +
* INSERT INTO `#__tablename` ( 'fieldname1', 'fieldname2', 'fieldname3', 'fieldname4', 'fieldname5' ) VALUES ( 'value1', 'value2', 'value3', 'value4', 'value5' );
 +
*
 +
* and executed
 +
*
 +
*/
 +
 +
echo $object->id; // will output the last insert ID
 +
</source>
 +
 +
If you wanna use "your" syntax:
 +
 +
<source lang="php">
 +
$db = JFactory::getDOB();
 +
 +
$query = "INSERT INTO `#__tablename` ( 'field1', 'field2' ) VALUES ( '".$value1."', '".$value2."')";
 +
 +
/**
 +
*
 +
* $db->setQuery($query);
 +
*
 +
* if ($dump) {
 +
*    echo $db->getQuery();
 +
* }
 +
*
 +
* $db->Query();
 +
*
 +
*/
 +
 +
$db->Execute($query);
 +
 +
</source>
 +
 +
But it's recommend to use a controller/model/table construct:
 +
 +
<source lang="php">
 +
 +
// controller
 +
 +
$model = $this->getModel('modelname')
 +
$model->store($data);
 +
 +
// Model
 +
 +
public function store($data) {
 +
 +
    [...]
 +
 +
    $row = JTable::getInstance('KomponentNameTableName');
 +
 +
    //save it in the db
 +
    if (!$row->save($data)) {
 +
        JError::raiseError( 500, $row->_db->getError() );
 +
        return false;
 +
    }
 +
 +
    [...]
 +
 +
}
 +
 +
// table/komponentnametablename.php
 +
 +
class JTableKomponentNameTableName extends JTable {
 +
 +
 +
    /**
 +
    * Primary Key
 +
    * @var int
 +
    */
 +
    var $id = null;
 +
    /** @var int */
 +
    var $field1 = null;
 +
    /** @var char */
 +
    var $field2 = null;
 +
 +
    public function __construct(& $db) {
 +
        parent::__construct('#__tablename', 'id', $db);
 +
    }
 +
 +
    public function check () {
 +
 +
        // Check all fields $this->fieldname
 +
        return true;
 +
 +
    }
 +
 +
}
 +
</source>
 +
-- [[User:Bembelimen|Bembelimen]] 12:34, 16 March 2009 (UTC)
 +
 +
 +
----
 +
 +
I think, you can '''not''' repeat the call to loadAssoc to get further rows, because
 +
loadAssoc runs the query unconditionally, I got a infinite recursion using it this way:
 +
<source lang="php">
 +
    while($row = $db->loadAssoc()){
 +
    // do something...
 +
    }
 +
</source>
 +
See: [http://api.joomla.org/__filesource/fsource_Joomla-Framework_Database_joomladatabasedatabasemysql.php.html#a390]
 +
line: 392
 +
 +
== Merging the 2 suggested articles ==
 +
 +
* Article 1:[[How to use the database classes in your script]]
 +
* Article 2:[[Tutorial:How to use the database classes in your script]]
 +
Discuss here ([[User:E-builds|E-builds]] 17:38, 11 January 2010 (UTC))
 +
 +
While they have the same name one is 'supposed' to be a reference and the other a 'how to' tutorial. Given that I think that the two should be complementary and linked but not merged. [[User:GreyHead|GreyHead]]
 +
 +
:I went ahead with the merge while emptying the Tutorial namespace. This article's contents were a superset of the tutorial's, and the style was exactly the same. The page [[API16:JDatabase]] can serve as a reference. [[User:Mvangeest|Mvangeest]] 14:07, 15 January 2011 (UTC)
 +
 +
== Subqueries ==
 +
 +
Ancient versions of MySQL are rare and becoming rarer. Please don't encourage people to break subqueries into two separate queries - it adds load to database servers! Signed, every DB admin on the planet. [[User:Rosuav|Rosuav]] 23:22, 6 February 2011 (UTC)
 +
 
== Joomla Versions and Joomla DB ==
 
== Joomla Versions and Joomla DB ==
  
 
I'm wondering what does Joomla DB means in ''Supported Storage Connectors''?
 
I'm wondering what does Joomla DB means in ''Supported Storage Connectors''?
 
Does Joomla 3.x supports Oracle DBMS or not?
 
Does Joomla 3.x supports Oracle DBMS or not?
--[[User:Faridv|Faridv]] ([[User talk:Faridv|talk]]) 15:04, 28 March 2014 (CDT)
 

Revision as of 15:05, 28 March 2014

Can anyone provide more info regarding insertObject()? I just have no idea of how to insert a row into a table. I tried to do the following but it just doesn't work:

$db = & JFactory::getDBO(); $query = "insert into #__my_table(field1, field2) values ('".$value1."','".$value2."')"; echo $query; $db->setQuery( $query ); return($db->Query());

Any ideas?

Thanks!


/**
*
* $object->fieldname1 = 'value1';
* $object->fieldname2 = 'value2';
* $object->fieldname3 = 'value3';
* $object->fieldname4 = 'value4';
* $object->fieldname5 = 'value5';
*
* $table = '#__tablename';
*
*/
 
$db = JFactory::getDBO();
 
$db->insertObject($table, $object, 'id');
 
/**
*
* The query will be generated:
* 
* INSERT INTO `#__tablename` ( 'fieldname1', 'fieldname2', 'fieldname3', 'fieldname4', 'fieldname5' ) VALUES ( 'value1', 'value2', 'value3', 'value4', 'value5' );
*
* and executed
*
*/
 
echo $object->id; // will output the last insert ID

If you wanna use "your" syntax:

$db = JFactory::getDOB();
 
$query = "INSERT INTO `#__tablename` ( 'field1', 'field2' ) VALUES ( '".$value1."', '".$value2."')";
 
/**
*
* $db->setQuery($query);
*
* if ($dump) {
*    echo $db->getQuery();
* }
*
* $db->Query();
*
*/
 
$db->Execute($query);

But it's recommend to use a controller/model/table construct:

// controller
 
$model = $this->getModel('modelname')
$model->store($data);
 
// Model
 
public function store($data) {
 
    [...]
 
    $row = JTable::getInstance('KomponentNameTableName');
 
    //save it in the db
    if (!$row->save($data)) {
        JError::raiseError( 500, $row->_db->getError() );
        return false;
    }
 
    [...]
 
}
 
// table/komponentnametablename.php
 
class JTableKomponentNameTableName extends JTable {
 
 
    /**
     * Primary Key
     * @var int
     */
    var $id = null;
    /** @var int */
    var $field1 = null;
    /** @var char */
    var $field2 = null;
 
    public function __construct(& $db) {
        parent::__construct('#__tablename', 'id', $db);
    }
 
    public function check () {
 
        // Check all fields $this->fieldname
        return true;
 
    }
 
}

-- Bembelimen 12:34, 16 March 2009 (UTC)



I think, you can not repeat the call to loadAssoc to get further rows, because loadAssoc runs the query unconditionally, I got a infinite recursion using it this way:

    while($row = $db->loadAssoc()){
    // do something...
    }

See: [1] line: 392

Merging the 2 suggested articles

Discuss here (E-builds 17:38, 11 January 2010 (UTC))

While they have the same name one is 'supposed' to be a reference and the other a 'how to' tutorial. Given that I think that the two should be complementary and linked but not merged. GreyHead

I went ahead with the merge while emptying the Tutorial namespace. This article's contents were a superset of the tutorial's, and the style was exactly the same. The page API16:JDatabase can serve as a reference. Mvangeest 14:07, 15 January 2011 (UTC)

Subqueries

Ancient versions of MySQL are rare and becoming rarer. Please don't encourage people to break subqueries into two separate queries - it adds load to database servers! Signed, every DB admin on the planet. Rosuav 23:22, 6 February 2011 (UTC)

Joomla Versions and Joomla DB

I'm wondering what does Joomla DB means in Supported Storage Connectors? Does Joomla 3.x supports Oracle DBMS or not?