Diferencias

Muestra las diferencias entre dos versiones de la página.

Enlace a la vista de comparación

Ambos lados, revisión anterior Revisión previa
Próxima revisión 		Revisión previa
jdatabase [2010/09/27 14:39]
10.12.12.87 		jdatabase [2017/03/27 17:44] (actual)
Línea 1: Línea 1:
 Joomla provides a sophisticated database abstraction layer to simplify the usage for third party developers. This guide will help you use this layer. Joomla provides a sophisticated database abstraction layer to simplify the usage for third party developers. This guide will help you use this layer.
  
-=Why should I use the Joomla database class?=+=== Why should I use the Joomla database class? ​===
 Joomla can use different kinds of SQL database systems and run in a variety of environments with different table-prefixes. In addition to these functions, the class automatically creates the database connection. Besides instantiating the object you need just two lines of code to get a result from the database in a variety of formats. Using the Joomla database layer ensures a maximum of compatibility and flexibility for your extension. Joomla can use different kinds of SQL database systems and run in a variety of environments with different table-prefixes. In addition to these functions, the class automatically creates the database connection. Besides instantiating the object you need just two lines of code to get a result from the database in a variety of formats. Using the Joomla database layer ensures a maximum of compatibility and flexibility for your extension.
  
-==Preparing the query==+=== Preparing the query ===
 <PHP> <PHP>
 // Get a database object // Get a database object
Línea 41: Línea 41:
 setQuery() also takes three other parameters: $offset, $limit (both used in list pagination) and $prefix, an alternative table prefix. All three variables have default values set and can usually be ignored. setQuery() also takes three other parameters: $offset, $limit (both used in list pagination) and $prefix, an alternative table prefix. All three variables have default values set and can usually be ignored.
  
-==Executing the Query==+=== Executing the Query ===
 To execute the query, Joomla provides several functions, which differ in their return value. To execute the query, Joomla provides several functions, which differ in their return value.
  
-=== Basic Query Execution === +==== Basic Query Execution ​==== 
-==== query() ====+===== query() ​=====
 The ''​query()''​ method is the the basic tool for executing SQL queries on a database. In Joomla it is most often used for updating or administering the database simply because the various load methods detailed on this page have the query step built into them. The ''​query()''​ method is the the basic tool for executing SQL queries on a database. In Joomla it is most often used for updating or administering the database simply because the various load methods detailed on this page have the query step built into them.
  
Línea 55: Línea 55:
 Note: $query() returns an appropriate database resource if successful, or FALSE if not. Note: $query() returns an appropriate database resource if successful, or FALSE if not.
  
-=== Query Execution Information ===+==== Query Execution Information ​====
 * getAffectedRows() * getAffectedRows()
 * explain() * explain()
 * insertid() * insertid()
  
-=== Insert Query Execution ===+==== Insert Query Execution ​====
 * insertObject() * insertObject()
  
-==Query Results ==+=== Query Results ​===
 The database class contains many methods for working with a query'​s result set. The database class contains many methods for working with a query'​s result set.
  
-=== Single Value Result === +==== Single Value Result ​==== 
-==== loadResult() ====+===== loadResult() ​=====
 Use '''​loadResult()'''​ when you expect just a single value back from your database query. ​ Use '''​loadResult()'''​ when you expect just a single value back from your database query. ​
  
Línea 102: Línea 102:
 $db->​setQuery($query);​ $db->​setQuery($query);​
 $result = $db->​loadResult();​ $result = $db->​loadResult();​
-<PHP>+</PHP>
  
-===Single Row Results ===+====Single Row Results ​====
 Each of these results functions will return a single record from the database even though there may be several records that meet the criteria that you have set. To get more records you need to call the function again. Each of these results functions will return a single record from the database even though there may be several records that meet the criteria that you have set. To get more records you need to call the function again.
 {| class="​wikitable"​ style="​text-align:​center"​ {| class="​wikitable"​ style="​text-align:​center"​
Línea 117: Línea 117:
 |} |}
  
-==== loadRow() ====+===== loadRow() ​=====
 loadRow() returns an indexed array from a single record in the table: ​ loadRow() returns an indexed array from a single record in the table: ​
 <PHP> <PHP>
Línea 126: Línea 126:
 </​PHP>​ </​PHP>​
 will give: will give:
-<pre>Array ( [0] => 1 [1] => John Smith [2] => johnsmith@example.com [3] => johnsmith ) </pre>+<code>Array ( [0] => 1 [1] => John Smith [2] => johnsmith@example.com [3] => johnsmith ) </code>
  
-You can access the individual values by using:<pre>​$row['​index'​] // e.g. $row['​2'​]</​pre>+You can access the individual values by using:<code>​$row['​index'​] // e.g. $row['​2'​]</​code>
  
 Notes: Notes:
Línea 134: Línea 134:
 # Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful. # Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.
  
-==== loadAssoc() ====+===== loadAssoc() ​=====
 loadAssoc() returns an associated array from a single record in the table: ​ loadAssoc() returns an associated array from a single record in the table: ​
 <PHP> <PHP>
Línea 143: Línea 143:
 </​PHP>​ </​PHP>​
 will give: will give:
-<pre>Array ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith )</pre>+<code>Array ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith )</code>
  
-You can access the individual values by using:<pre>​$row['​name'​] // e.g. $row['​name'​]</​pre>+You can access the individual values by using:<code>​$row['​name'​] // e.g. $row['​name'​]</​code>
  
 Notes: Notes:
 # Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful. # Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.
  
-==== loadObject() ====+===== loadObject() ​=====
 loadObject returns a PHP object from a single record in the table: ​ loadObject returns a PHP object from a single record in the table: ​
 <PHP> <PHP>
Línea 159: Línea 159:
 </​PHP>​ </​PHP>​
 will give: will give:
-<pre>​stdClass Object ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith )</pre>+<code>​stdClass Object ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith )</code>
  
-You can access the individual values by using:<pre>​$row->​index // e.g. $row->​email</​pre>+You can access the individual values by using:<code>​$row->​index // e.g. $row->​email</​code>
  
 Notes: Notes:
 # Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful. # Whilst you can repeat the call to get further rows, one of the functions that returns multiple rows might be more useful.
  
-===Single Column Results ===+====Single Column Results ​====
 Each of these results functions will return a single column from the database. ​ Each of these results functions will return a single column from the database. ​
  
Línea 180: Línea 180:
 |} |}
  
-==== loadResultArray() ====+===== loadResultArray() ​=====
 loadResultArray() returns an indexed array from a single column in the table: ​ loadResultArray() returns an indexed array from a single column in the table: ​
 <PHP> <PHP>
Línea 192: Línea 192:
 </​PHP>​ </​PHP>​
 will give: will give:
-<pre>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )</pre>+<code>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )</code>
  
-You can access the individual values by using:<pre>​$column['​index'​] // e.g. $column['​2'​]</​pre>+You can access the individual values by using:<code>​$column['​index'​] // e.g. $column['​2'​]</​code>
  
 Notes: Notes:
Línea 200: Línea 200:
 # loadResultArray() is equivalent to loadResultArray(0). # loadResultArray() is equivalent to loadResultArray(0).
  
-==== loadResultArray($index) ====+===== loadResultArray($index) ​=====
 loadResultArray($index) returns an indexed array from a single column in the table: ​ loadResultArray($index) returns an indexed array from a single column in the table: ​
 <PHP> <PHP>
Línea 212: Línea 212:
 </​PHP>​ </​PHP>​
 will give: will give:
-<pre>Array ( [0] => johnsmith@example.com [1] => magda_h@example.com [2] => ydg@example.com )</pre>+<code>Array ( [0] => johnsmith@example.com [1] => magda_h@example.com [2] => ydg@example.com )</code>
  
-You can access the individual values by using:<pre>​$column['​index'​] // e.g. $column['​2'​]</​pre>+You can access the individual values by using:<code>​$column['​index'​] // e.g. $column['​2'​]</​code>
  
 loadResultArray($index) allows you to iterate through a series of columns in the results loadResultArray($index) allows you to iterate through a series of columns in the results
Línea 226: Línea 226:
 </​PHP>​ </​PHP>​
 will give: will give:
-<pre>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )+<code>Array ( [0] => John Smith [1] => Magda Hellman [2] => Yvonne de Gaulle )
 Array ( [0] => johnsmith@example.com [1] => magda_h@example.com [2] => ydg@example.com ) Array ( [0] => johnsmith@example.com [1] => magda_h@example.com [2] => ydg@example.com )
-Array ( [0] => johnsmith [1] => magdah [2] => ydegaulle )</pre> +Array ( [0] => johnsmith [1] => magdah [2] => ydegaulle )</code> 
-</pre>+</code>
  
 Notes: Notes:
 # The array indices are numeric starting from zero. # The array indices are numeric starting from zero.
  
-=== Multi-Row Results ===+==== Multi-Row Results ​====
 Each of these results functions will return multiple records from the database. ​ Each of these results functions will return multiple records from the database. ​
  
Línea 248: Línea 248:
 |} |}
  
-==== loadRowList() ====+===== loadRowList() ​=====
 loadRowList() returns an indexed array of indexed arrays from the table records returned by the query: ​ loadRowList() returns an indexed array of indexed arrays from the table records returned by the query: ​
 <PHP> <PHP>
Línea 257: Línea 257:
 </​PHP>​ </​PHP>​
 will give (with line breaks added for clarity): will give (with line breaks added for clarity):
-<pre>Array ( +<code>Array ( 
 [0] => Array ( [0] => 1 [1] => John Smith [2] => johnsmith@example.com [3] => johnsmith )  [0] => Array ( [0] => 1 [1] => John Smith [2] => johnsmith@example.com [3] => johnsmith ) 
 [1] => Array ( [0] => 2 [1] => Magda Hellman [2] => magda_h@example.com [3] => magdah )  [1] => Array ( [0] => 2 [1] => Magda Hellman [2] => magda_h@example.com [3] => magdah ) 
 [2] => Array ( [0] => 3 [1] => Yvonne de Gaulle [2] => ydg@example.com [3] => ydegaulle )  [2] => Array ( [0] => 3 [1] => Yvonne de Gaulle [2] => ydg@example.com [3] => ydegaulle ) 
-)</pre>+)</code>
  
-You can access the individual rows by using:<pre>​$row['​index'​] // e.g. $row['​2'​]</​pre> +You can access the individual rows by using:<code>​$row['​index'​] // e.g. $row['​2'​]</​code> 
-and you can access the individual values by using:<pre>​$row['​index'​]['​index'​] // e.g. $row['​2'​]['​3'​]</​pre>+and you can access the individual values by using:<code>​$row['​index'​]['​index'​] // e.g. $row['​2'​]['​3'​]</​code>
  
 Notes: Notes:
 # The array indices are numeric starting from zero. # The array indices are numeric starting from zero.
  
-==== loadAssocList() ====+===== loadAssocList() ​=====
 loadAssocList() returns an indexed array of associated arrays from the table records returned by the query: ​ loadAssocList() returns an indexed array of associated arrays from the table records returned by the query: ​
 <PHP> <PHP>
Línea 278: Línea 278:
 </​PHP>​ </​PHP>​
 will give (with line breaks added for clarity): will give (with line breaks added for clarity):
-<pre>Array ( +<code>Array ( 
 [0] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith )  [0] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith ) 
 [1] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@example.com [username] => magdah )  [1] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@example.com [username] => magdah ) 
 [2] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@example.com [username] => ydegaulle )  [2] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@example.com [username] => ydegaulle ) 
-) </pre>+) </code>
  
-You can access the individual rows by using:<pre>​$row['​index'​] // e.g. $row['​2'​]</​pre> +You can access the individual rows by using:<code>​$row['​index'​] // e.g. $row['​2'​]</​code> 
-and you can access the individual values by using:<pre>​$row['​index'​]['​column_name'​] // e.g. $row['​2'​]['​email'​]</​pre>+and you can access the individual values by using:<code>​$row['​index'​]['​column_name'​] // e.g. $row['​2'​]['​email'​]</​code>
  
-==== loadAssocList($key) ====+===== loadAssocList($key) ​=====
 loadAssocList('​key'​) returns an associated array - indexed on '​key'​ - of associated arrays from the table records returned by the query: ​ loadAssocList('​key'​) returns an associated array - indexed on '​key'​ - of associated arrays from the table records returned by the query: ​
 <PHP> <PHP>
Línea 296: Línea 296:
 </​PHP>​ </​PHP>​
 will give (with line breaks added for clarity): will give (with line breaks added for clarity):
-<pre>Array ( +<code>Array ( 
 [johnsmith] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith )  [johnsmith] => Array ( [id] => 1 [name] => John Smith [email] => johnsmith@example.com [username] => johnsmith ) 
 [magdah] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@example.com [username] => magdah )  [magdah] => Array ( [id] => 2 [name] => Magda Hellman [email] => magda_h@example.com [username] => magdah ) 
 [ydegaulle] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@example.com [username] => ydegaulle )  [ydegaulle] => Array ( [id] => 3 [name] => Yvonne de Gaulle [email] => ydg@example.com [username] => ydegaulle ) 
-)</pre>+)</code>
  
-You can access the individual rows by using:<pre>​$row['​key_value'​] // e.g. $row['​johnsmith'​]</​pre> +You can access the individual rows by using:<code>​$row['​key_value'​] // e.g. $row['​johnsmith'​]</​code> 
-and you can access the individual values by using:<pre>​$row['​key_value'​]['​column_name'​] // e.g. $row['​johnsmith'​]['​email'​]</​pre>+and you can access the individual values by using:<code>​$row['​key_value'​]['​column_name'​] // e.g. $row['​johnsmith'​]['​email'​]</​code>
  
 Note: Key must be a valid column name from the table; it does not have to be an Index or a Primary Key. But if it does not have a unique value you may not be able to retrieve results reliably. Note: Key must be a valid column name from the table; it does not have to be an Index or a Primary Key. But if it does not have a unique value you may not be able to retrieve results reliably.
  
-==== loadObjectList() ====+===== loadObjectList() ​=====
 loadObjectList() returns an indexed array of PHP objects from the table records returned by the query: ​ loadObjectList() returns an indexed array of PHP objects from the table records returned by the query: ​
 <PHP> <PHP>
Línea 316: Línea 316:
 </​PHP>​ </​PHP>​
 will give (with line breaks added for clarity): will give (with line breaks added for clarity):
-<pre>Array ( +<code>Array ( 
 [0] => stdClass Object ( [id] => 1 [name] => John Smith  [0] => stdClass Object ( [id] => 1 [name] => John Smith 
     [email] => johnsmith@example.com [username] => johnsmith )      [email] => johnsmith@example.com [username] => johnsmith ) 
Línea 323: Línea 323:
 [2] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle ​ [2] => stdClass Object ( [id] => 3 [name] => Yvonne de Gaulle ​
     [email] => ydg@example.com [username] => ydegaulle )      [email] => ydg@example.com [username] => ydegaulle ) 
-)</pre>+)</code>
  
-You can access the individual rows by using:<pre>​$row['​index'​] // e.g. $row['​2'​]</​pre> +You can access the individual rows by using:<code>​$row['​index'​] // e.g. $row['​2'​]</​code> 
-and you can access the individual values by using:<pre>​$row['​index'​]->​name // e.g. $row['​2'​]->​email</​pre>+and you can access the individual values by using:<code>​$row['​index'​]->​name // e.g. $row['​2'​]->​email</​code>
  
-==== loadObjectList('​key'​) ====+===== loadObjectList('​key'​) ​=====
 loadObjectList($key) returns an associated array - indexed on '​key'​ - of objects from the table records returned by the query: ​ loadObjectList($key) returns an associated array - indexed on '​key'​ - of objects from the table records returned by the query: ​
 <PHP> <PHP>
Línea 337: Línea 337:
 </​PHP>​ </​PHP>​
 will give (with line breaks added for clarity): will give (with line breaks added for clarity):
-<pre>Array ( +<code>Array ( 
 [johnsmith] => stdClass Object ( [id] => 1 [name] => John Smith  [johnsmith] => stdClass Object ( [id] => 1 [name] => John Smith 
     [email] => johnsmith@example.com [username] => johnsmith )      [email] => johnsmith@example.com [username] => johnsmith ) 
Línea 345: Línea 345:
     [email] => ydg@example.com [username] => ydegaulle )      [email] => ydg@example.com [username] => ydegaulle ) 
 ) )
-</pre>+</code>
  
-You can access the individual rows by using:<pre>​$row['​key_value'​] // e.g. $row['​johnsmith'​]</​pre> +You can access the individual rows by using:<code>​$row['​key_value'​] // e.g. $row['​johnsmith'​]</​code> 
-and you can access the individual values by using:<pre>​$row['​key_value'​]->​column_name // e.g. $row['​johnsmith'​]->​email</​pre>+and you can access the individual values by using:<code>​$row['​key_value'​]->​column_name // e.g. $row['​johnsmith'​]->​email</​code>
  
 Note: Key must be a valid column name from the table; it does not have to be an Index or a Primary Key. But if it does not have a unique value you may not be able to retrieve results reliably. Note: Key must be a valid column name from the table; it does not have to be an Index or a Primary Key. But if it does not have a unique value you may not be able to retrieve results reliably.
  
-=== Miscellaneous Result Set Methods === +==== Miscellaneous Result Set Methods ​==== 
-==== getNumRows() ====+===== getNumRows() ​=====
 getNumRows() will return the number of result rows found by the last query and waiting to be read. To get a result from getNumRows() you have to run it '''​after'''​ the query and '''​before'''​ you have retrieved any results.  ​ getNumRows() will return the number of result rows found by the last query and waiting to be read. To get a result from getNumRows() you have to run it '''​after'''​ the query and '''​before'''​ you have retrieved any results.  ​
 <PHP> <PHP>
Línea 363: Línea 363:
 $result = $db->​loadRowList();​ $result = $db->​loadRowList();​
 </​PHP>​ </​PHP>​
-will return <pre>3</pre>+will return <code>3</code>
 Note: if you run getNumRows() after loadRowList() - or any other retrieval method - you may get a PHP Warning: Note: if you run getNumRows() after loadRowList() - or any other retrieval method - you may get a PHP Warning:
-<pre>​Warning:​ mysql_num_rows():​ 80 is not a valid MySQL result resource  +<code>​Warning:​ mysql_num_rows():​ 80 is not a valid MySQL result resource  
-in D:​\xampp\htdocs\joomla1.5a\libraries\joomla\database\database\mysql.php on line 344</pre>+in D:​\xampp\htdocs\joomla1.5a\libraries\joomla\database\database\mysql.php on line 344</code>
  
-==Tips, Tricks & FAQ== +=== Tips, Tricks & FAQ === 
-===Subqueries===+==== Subqueries ​====
 We had a few people lately using subqueries like these: We had a few people lately using subqueries like these:
 <PHP> <PHP>
Línea 380: Línea 380:
 $query = "​SELECT * FROM #__example WHERE id IN (". implode(",",​ $database->​loadResultArray()) ."​)";​ $query = "​SELECT * FROM #__example WHERE id IN (". implode(",",​ $database->​loadResultArray()) ."​)";​
 </​PHP>​ </​PHP>​
-===Developer-Friendly Tips===+==== Developer-Friendly Tips ====
 Here is a quick way to do four developer-friendly things at once: Here is a quick way to do four developer-friendly things at once:
 * Use a simple constant as an SQL seperator (which can probably be used in many queries). * Use a simple constant as an SQL seperator (which can probably be used in many queries).