Difference between revisions of "Accessing the database using JDatabase"
From Joomla! Documentation
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? | ||
− |
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[edit]
- 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 (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[edit]
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[edit]
I'm wondering what does Joomla DB means in Supported Storage Connectors? Does Joomla 3.x supports Oracle DBMS or not?