This is down to changes that were made for new versions of ZPE.
The SQL that has been shown over the last few articles is pretty generic and has no limitations on what is selected. This article will focus on conditions.
A condition is something that must be met. In SQL, if a condition is not met on a certain record (row of a table), that record will not be included in the result of the query.
Queries will almost always use the
WHERE keyword. This informs the parser that a condition is about to follow. As
with almost all SQL, because of the way the
WHERE keyword is positioned toward the end of a query, any statement
which includes the
WHERE keyword will read in a very English like manner.
The following conditions will use this table called 'fruits':
For instance, assuming a query which is to select the record which contains the information on apples, a pseudo-query could look like:
Select all fruits where type is apple
And then the corresponding SQL query would look very similar:
SELECT * FROM `fruits` WHERE type="Apple"
In terms of efficiency, using a
WHERE clause is more efficient than traversing the data with a
server side language such as PHP after receiving all the data.
Conditions can be combined using logical conjunction and logical disjunction. These are better known as AND and OR.
For the previous table, assuming a query is needed to select apples and pears, the
OR keyword is used as
SELECT * FROM `fruits` WHERE type="Apple" OR type="Pear"
The following query will find all apples, and pears where the quantity is less than 9.
SELECT * FROM `fruits` WHERE type="Apple" OR type="Pear" AND quantity < 9
But wait, the table returns apples? This is because of the fact that the AND only keyword only check if the pears have a quantity less than 9.
Selections can be bracketed as with mathematics, so for the previous query to work properly where both apples and pears are checked for a quantity of less than 9, a bracketed expression is needed:
SELECT * FROM `fruits` WHERE (type="Apple" OR type="Pear") AND quantity < 9
In this case, the resulting table is empty, which is what is expected.
The reason that the problem occured originally is because the
AND keyword represents the logical AND (∧).
The ∧ has precedence over the logical OR (∨), meaning it is evaluated before it.
Bracketing expressions is very important, and knowing where to do this is a very important task when developing more complex queries.
S1s and S2s really enjoyed the lesson on cybercrime using the lesson on How to Rob A Bank on… https://t.co/MxjDYE2JFu
7 months ago
S2s doing really well in hardware test! Also really enjoying doing tests electronically rather than on paper.… https://t.co/ritdfcW6Mw
7 months ago
S1 and S2 pupils listing input and output devices and giving answers such as keyboard and mouse but there were also… https://t.co/dGxDkYqFxo
8 months ago