At this point we can start thinking about the database. Without that our component wouldn't be of much use. As you will see, Joomla!'s database class will be more helpful this time. For now, we are going to create the table that we are going to use. The query is as follows:
CREATE TABLE `jos_tinynews` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `title` VARCHAR( 255 ) NOT NULL , `text` TEXT NOT NULL
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `title` VARCHAR( 255 ) NOT NULL , `text` TEXT NOT NULL
)
As we can see, we have placed the prefix jos_ to our
tinynews table name. That's the prefix that we usually use, but if
you are using another prefix, place it there. Once we have the table created, we can
start experimenting with it. The other two fields are title
and text. Later we will be adding some more fields, but these
will be okay for now.
We are going to see some basic examples of work, such as:
- Inserting data into our table
- Editing data from our table
- Deleting data
- And finally, selecting data
Ready? Let's get started.
This can be done in more than one way; we will be checking some of the methods available, so you can decide later which one to use. Though it's not so usual, we are only going to work with the following two files:
- Our view—components/com_tinynews/views/tinynews/view.html.php
- Our model—components/com_tinynews/models/tinynews.php
That's not the usual way of doing things, but for a demonstration it will
be enough. We will start working on the model, so open the model file. We are
going to add a new method called insertdata to it, as
follows:
function insertdata($title = "default", $text = "default"){
$database =& JFactory::getDBO();
$news = new stdClass;
$news->id = NULL;
$news->title = $title;
$news->text = $text;
if (!$database->insertObject( '#__tinynews', $news, 'id' )) {
echo $database->stderr();
return false;
}
return $news->id;
}
With this tiny piece of code, we will have created our new method. This method
receives two parameters, $title and $text.
We are assigning a default value to these two, just in case no data is passed to
the method.
Our next step is to create an instance of the database object so that all its
methods are available to us. We do this with $database =&
JFactory::getDBO();. After this is done, we are going to create an
object. For this type of database insert, we are going to use the following
object:
$news = new stdClass;
$news->id = NULL;
$news->title = $title;
$news->text = $text;
In order to create this object we are using the class
stdClass. This is an empty class that comes with PHP. It has no
methods or properties, so we can define it to our liking. After we have prepared
our class with the values passed to our method, we will try to insert it and
take care of any possible errors:
if (!$database->insertObject( '#__tinynews', $news, 'id' )) {
echo $database->stderr();
return false;
}
We use the insertObject method, and if things don't go
well, we return false; otherwise we will return the last
inserted ID with return $news->id;.
Once we have our method created in the model, we are going to make use of it
in our view file. Open the
components/com_tinynews/views/tinynews/view.html.php file and add
the highlighted code to it:
function display(){
$model =& $this->getModel();
$news = $model->getallnews();
$this->assignRef('news', $news);
$insertnews = $model->insertdata("Our first news inserted", "This will be the text for our first news");
echo "<br/><br/>Our newly inserted news has id: ".$insertnews."<br/><br/>";
parent::display();
}
First, we need to check if we are correctly loading our model using
$model =& $this->getModel();.
If the model has been loaded, we will be able to make use of its methods. In
our example, we will call our newly created insertdata
method, passing some values to it. We will also assign its returned value to
the $insertnews variable.
This returned value will be then rendered to screen, showing the ID of the
inserted element. For example, if we try to load our site again by calling
the
http://wayofthewebninja.com/index.php?option=com_tinynews URL, we
will see something similar to the following screenshot:
Tip
Note that we have used #__tinynews instead of
jos_tinynews. Joomla! will change #__
to jos_ or any other prefix that is being used in our
Joomla! installation. This way we can install our component in any Joomla!
installation without needing to change the prefix for each
installation.
Now that we have successfully inserted our new record, we are going to see
another way of doing it, just so you can decide which method you like best. We
will be changing the insertdata method of our model, as
follows:
function insertdata($title = "default", $text = "default"){
$database =& JFactory::getDBO();
$query = "INSERT INTO ".$database->nameQuote('#__tinynews'). "(title, text) VALUES (".$database->quote($title).",". $database->quote($text).")";
$database->setQuery($query);
if (!$database->query()) {
echo $database->stderr();
return false;
}
return $database->insertid();
}
Though this is a bit different, we will have the same result. This time we are building the query in a more "classical" way. However, we are still using some Joomla! methods that will help us in our work. Take for example these two:
-
$database->nameQuote -
$database->quote
These two methods are going to be very useful for us. The first
method, nameQuote, will place backticks (` `) around our
table name if we use MySQL. If we were using other database, Joomla! will place
backticks or any other character that is necessary.
The quote method does something similar, but it's used
for values instead of tables or field names. Not only will this method surround
the values in quotes (' '), but it will also escape special characters
that could harm our database, thus helping us prevent SQL injection
attacks.
Tip
Want to know more about SQL injection? You can start here: http://en.wikipedia.org/wiki/SQL_injection.
We have prepared our query and now we need to set it. We do that with
$database->setQuery($query);. This sets the query for
execution, but doesn't execute it. For that we need to use the
$database->query() method.
We can check for errors much in the same way as in the first
insertdata example. However, for taking the last inserted ID, we
need to use the $database->insertid(); method.
And that's all. Any of these two ways will help us in inserting data into the database. We can do this a few more times by just reloading that URL.
Come on! Do it three or four more times so that we have some data to work with. Our next step will be to update data, which will be quite similar.
Updating our data will be necessary from time to time, and it will be quite
similar to our latest insert example. Open the
components/com_tinynews/models/tinynews.php file if you
haven't opened it yet. We are going to remove our
insertdata method and replace it with an
updatedata method as follows:
function updatedata($id = "0", $title = "default", $text = "default"){
$database =& JFactory::getDBO();
$query = "UPDATE ".$database->nameQuote('#__tinynews'). " SET ".$database->nameQuote('title'). "=".$database->quote($title).", ".$database->nameQuote('text'). "=".$database->quote($text)." WHERE ".$database->nameQuote('id'). "=".$database->quote($id);
$database->setQuery($query);
if (!$database->query()) {
echo $database->stderr();
return false;
}
}
As you can see, the only thing that really changes here is the query. But it's not very different from every other update query we have seen in other occasions. Next, I will show you a delete query.
Again, we will be doing this in a similar way to what we could have been
doing without Joomla!. Take a look at the deletedata
method:
function deletedata($id = "0"){
$database =& JFactory::getDBO();
$query = "DELETE FROM ".$database->nameQuote('#__tinynews'). " WHERE ".$database->nameQuote('id')." = ".$database->quote($id);
$database->setQuery($query);
if (!$database->query()) {
echo $database->stderr();
return false;
}
}
Not much to say here. Why don't we go to something more interesting such as reading data? Joomla! has some interesting methods for reading data. Let's get started with them!
Reading data is going to be the most interesting part of our work with the
database, mostly because here Joomla! has more methods to work with. For
example, there is the loadResult method, which we will start
with.
We will continue working on the following two files:
- Our view:
components/com_tinynews/views/tinynews/view.html.php - Our model:
components/com_tinynews/models/tinynews.php
This one is used to get just one value from our database, like a field
from our table. Let's try it; we are going to create a
get_title method in our model:
function get_title($id = 0){
$database =& JFactory::getDBO();
$query = "SELECT ".$database->nameQuote('title'). "FROM ".$database->nameQuote('#__tinynews'). " WHERE ".$database->nameQuote('id')." = ".$database->quote($id);
$query = "SELECT ".$database->nameQuote('title'). "FROM ".$database->nameQuote('#__tinynews'). " WHERE ".$database->nameQuote('id')." = ".$database->quote($id);
$database->setQuery($query);
$result = $database->loadResult();
return $result;
}
This method will receive an ID, which will be used to retrieve the value we want from the database. Our first step, as always, is to get an instance of the database class. Then we prepare the query we are going to use to retrieve the title from the record with the ID equal to the one passed to the method.
The next step is to set the query with the setQuery
method. Then only one thing still needs to be done—calling
the loadResult method. This method, as many others we
are about to see, automatically calls the query method so
that we don't need to call the query method to execute the query. It
will be automatically executed by the loadResult
method.
Once that's done, we return the value. Take a look at the following highlighted code:
function display(){
$model =& $this->getModel();
$news = $model->getallnews();
$this->assignRef('news', $news);
$title = $model->get_title('1'),
echo $title."<br/><br/>";
parent::display();
}
If we add the code to our view display method, we only need to load our
usual
http://wayofthewebninja.com/index.php?option=com_tinynews URL. A
phrase like Our first news inserted will
be shown on our screen. But what if we want more than just one value
returned? Well, there are other methods for that, and we are about to see
them. Take for example the loadObject method.
The loadObject method will return a full record
instead of a single value. We will see this better with an example.
Let's modify the get_title method in our model. Call
it get_record and modify its code as
follows:
function get_record($id = 0){
$database =& JFactory::getDBO();
$query = "SELECT * FROM ".$database->nameQuote('#__tinynews'). " WHERE ".$database->nameQuote('id')." = ".$database->quote($id);
$database->setQuery($query);
$result = $database->loadObject();
return $result;
}
See, we have changed our loadResult method to
our loadObject one, but mostly the method remains
exactly as it is except for the query. We are returning all the fields now.
So our model doesn't need many changes, but what about our view? We will
change the display method of our view as
follows:
$record = $model->get_record('1'),
echo $record->id."<br/><br/>";
echo $record->title."<br/><br/>";
echo $record->text."<br/><br/>";
This will render something like this on our screen:
1 Our first news inserted This will be the text for our first news
So the loadObject method has created an object, each
field of the record being a property of the object. This is my favorite way
of returning and working with a single record. But there are others, such
as:
-
loadRow()—worksin mostly the same way, but instead of returning an object, returns an indexed array (so our previous example would work in a similar manner) -
loadAssoc()—isa method that can be used to return an associative array, instead of an indexed one
At this point, we have only seen the tip of the iceberg. The database class has many, many other methods, which we can use to fulfill all our needs. If you want to check them all, you can go to the following page:
http://docs.joomla.org/How_to_use_the_database_classes_in_your_script.
But before going there, stay with us, as we are going to see one last
method, called loadObjectList.
loadObjectList is probably one of the methods that we
are going to use most of the time. It returns an array of objects. For this
example, we will modify the getallnews method of our
model. It will look as follows:
function getallnews(){
$database =& JFactory::getDBO();
$query = "SELECT * FROM ". $database->nameQuote('#__tinynews').";";
$database->setQuery($query);
$result = $database->loadObjectList();
return $result;
}
Again, there aren't many changes here—only the loading
method and the query. We have removed the WHERE part of
the query, and so we get all the results from the table. The next thing we
need to change is our view file, as follows:
function display(){
$model =& $this->getModel();
$news = $model->getallnews();
$this->assignRef('news', $news);
parent::display();
}
Not many changes here either, but then, this time we need to change our
template
componentscom_tinynewsviews inynews mpldefault.php file,
as follows:
<?php
defined('_JEXEC') or die('Restricted access'),
foreach($this->news as $new){
echo "<p><b>".$new->title."</b></p><br/><br/>";
echo "<p>".$new->text."</p><br/><br/>";
}
?>
This code is almost self-explanatory; for each element of the
$this->news array we create a new variable,
$new. Each one of these variables will be an object, much like
our loadObject example. This code will result in a
screenshot as follows:
With all these methods, we will be able to insert, edit, delete, and retrieve data from our table. All this will not only be necessary when building the admin zone of our component, but also on the frontend of our module so that we can retrieve the data in order to show it to our site visitors.