Talk:Accessing the database using JDatabase

From Joomla! Documentation
(Difference between revisions)
Jump to: navigation, search
(New page: 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::get...)
 
m (moved Talk:How to use the database classes in your script to Talk:Accessing the database using JDatabase: Adapted page title to the continuous tense used in most tutorial titles)
 
(7 intermediate revisions by 6 users not shown)
Line 11: Line 11:
  
 
Thanks!
 
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)

Latest revision as of 15:16, 7 February 2012

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

[edit] 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)

[edit] 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)

Retrieved from "http://docs.joomla.org/index.php?title=Talk:Accessing_the_database_using_JDatabase&oldid=64797"
Personal tools
Namespaces

Variants
Actions
Navigation
Joomla! Sites
Toolbox