Once you are connected to the database you can start executing SQL queries. This section explains how to directly query the database. For prepared statements, please see the corresponding section.
If you are not using the application framework, you can replace weeApp()->db by your database object for the examples in this chapter.
You can query a database in various ways using Web:Extend. The following diagram summarizes the various methods discussed in this section:
Database query methods
You can write queries by using the query method of the weeDatabase object.
If we wanted to fetch the last 5 articles from the table articles, that is how we would do it:
<?php $oResults = weeApp()->db->query('SELECT * FROM articles ORDER BY art_date DESC LIMIT 5');
It's pretty straightforward. query lets you write any SQL queries without restriction.
You could write standard SQL or write database-dependent SQL without having the framework complain.
But let's face it, most queries need parameters.
There are two methods to bind parameters to query automatically.
You can use either question marks (?) or named parameters placeholders in your query,
and give the parameters directly to the query method.
Using each of these two methods your parameters are escaped automatically without you
having to worry about SQL Injections.
You can of course build a query with parameters manually. However it is not recommended, and there's a few things to know when doing this that we will describe. The preferred solution is using named parameters.
Using named parameters is easy.
You just have to use names preceded by a colon, like :id, :parameter,
:art_date, or even numbers like :3 in the query,
and then pass an associative array as the second argument to the query method.
The array will contain the names without the colon as keys, like id,
parameter, art_date, 3, and their respective associated values.
For example let's fetch the article number 3 from our articles table:
<?php $oResults = weeApp()->db->query('SELECT * FROM wee_articles WHERE art_id=:id', array('id' => 3));
Or if we assume that your frame is waiting for an id parameter using GET,
you can simply type the following line without bothering whether the id parameter exists
or whether there's other values in the $_GET array.
The `query` method will just look for the id value, and if absent trigger an exception.
But even if doing this is possible, it is recommended to filter the input
before passing the $_GET array to the query method.
<?php // Note: It is highly recommended to filter the input first $oResults = weeApp()->db->query('SELECT * FROM wee_articles WHERE art_id=:id', $_GET);
Another method is to use question marks instead of named parameters.
This might come in handy when you do simple queries with one or two parameters only.
Instead of passing an associative array to the query method,
you will give values directly after the query. Let's see with an example:
<?php $oResults = weeApp()->db->query('SELECT * FROM wee_articles WHERE art_id=? AND art_date=?', 3, '2008-02-15');
The first parameter will replace the first placeholder, the second parameter will replace the second placeholder, and so on.
Beware of using this method: you must give a parameter two times if it appears in two places in the query. If that happens or if the query uses a lot of parameters, please use the named parameters method.
You can also use the old way of building queries: by concatening yourself strings and values.
This method is dangerous because the framework can't protect your queries from SQL injections.
You should be very careful while doing this, and ensure that you always call the escape method for each parameter.
Here's an example:
<?php // Note: It is highly recommended to filter the input first $sQuery = 'SELECT * FROM wee_articles WHERE art_id=' . weeApp()->db->escape($_GET['id']); $oResults = weeApp()->db->query($sQuery);
One more time, you shouldn't do this unless you have no other choices. If you stumble upon a case where you must do this, please submit a ticket and we'll try to enhance the framework so you won't have to do it next time.
Sometimes you run a query only to retrieve a unique row which has only one column, for example to retrieve the count of a table.
queryValue is a convenience method to facilitate using these values.
<?php // Retrieve the number of rows in a table $iCount = weeApp()->db->queryValue('SELECT COUNT(*) FROM example_table');
After executing an INSERT query, you might need to retrieve an identifier for the row you just inserted.
You can do so by calling the getPKId method.
Depending on the driver this method will require a parameter: the name of the sequence for this primary key.
This is an example for MySQL:
<?php // Note: Always filter the data first // Insert a random row $oResults = weeApp()->db->query('INSERT INTO wee_articles (art_contents) VALUES (:art_contents)', $_POST); // Retrieve the id and display it echo weeApp()->db->getPKId();
And the same example for PostgreSQL:
<?php // Note: Always filter the data first // Insert a random row $oResults = weeApp()->db->query('INSERT INTO wee_articles (art_contents) VALUES (:art_contents)', $_POST); // Retrieve the id and display it echo weeApp()->db->getPKId('art_id_seq');
With art_id_seq the name of the sequence associated with this column.
You might also want to check the number of affected rows after executing
an INSERT, UPDATE or DELETE statement.
Reasons might be because you want to check your query was executed successfully.
You can call the numAffectedRows method to retrieve this number.
<?php // Note: Always filter the data first // Insert a random row $oResults = weeApp()->db->query('INSERT INTO wee_articles (art_contents) VALUES (:art_contents)', $_POST); // Retrieve the number of affected rows and display it echo weeApp()->db->numAffectedRows();