Elxis CMS Forum
Support => Elxis 4.x/5.x DEV => Topic started by: speck on October 03, 2012, 16:38:51
-
Queries like these
$query = "SELECT title, bla, bla FROM ".$db->quoteId('#__content')." (new elxis understand)
."\n bla bla bla bla bla bla bla";
???????;
in old elxis i had
$database->setQuery($query);
$rows = $database->loadObjectList();
$rcounter = count( $rows );
----------------------------------------------------------
$query = "SELECT title, bla, bla FROM ".$db->quoteId('#__content')." (new elxis understand)
."\n bla bla bla bla bla bla bla";
????????;
in old elxis i had
$database->setQuery( $query, '#__', $this->nitems, 0 );
$rows = $database->loadObjectList();
how are changed in new elxis???
i'm tried to get a look in some files but i'm very confused
-
Elxis 4 uses native PHP's PDO (http://php.net/manual/en/book.pdo.php) database layer. We have created a PDO extension class in order to manage database better but the whole consept is still pure PDO.
$db = eFactory::getDB(); //fetch the Elxis database library
A simple sql select query:
$sql = "SELECT id, title FROM #__content"; (#__ will be replaced by your database prefix, by default elx_)
Elxis allows you to apply quotes on field and table names in order to avoid errors due to names used as database functions/constants, etc.
So, the above statement should better be written as:
$sql = "SELECT ".$db->quoteId('id').", ".$db->quoteId('title')." FROM ".$db->quoteId('#__content');
Now create the PDO prepared statement.
//if you want to fetch all results:
$stmt = $db->prepare($sql);
//if you want to fetch only the first 10 results:
$stmt = $db->prepareLimit($sql, 0, 10);
//execute the query:
$stmt->execute();
//fetch results as objects
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
//fetch results as an associated array
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
If your query uses a WHERE claus you must bind parameters into the statement.
Example
$catid = 2; //integer, fetch only articles from category with catid=2
$sql = "SELECT ".$db->quoteId('id').", ".$db->quoteId('title')." FROM ".$db->quoteId('#__content')." WHERE ".$db->quoteId('catid')." = :cat";
$stmt = $db->prepareLimit($sql, 0, 10);
$stmt->bindParam(':cat', $catid, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_OBJ);
Keep your queries as SQL native as possible. Avoid complex queries with sub-selects and joins as possible.
These things will be presented in details in the developers documentation we currently write.
-
Thanks a lot :)