Removing Data and Retrieving Data

Jun 17, 2009 Author: LinuxAdmin

Removing Data:

In a dynamic application, data never remains constant. It always changes—and, sometimes, it becomes superfluous and needs to be deleted. SQL database engines implement the DELETE statement for this purpose: DELETE FROM book; This simple statement will remove all records from the book table, leaving behind an empty table. At times, it is necessary to remove all records from tables, but most of the time, you will want to provide parameters limiting the deletion to specific records. Again, a WHERE clause achieves this:

DELETE FROM book WHERE isbn = ’0812550706’;

Retrieving Data

As we mentioned earlier, relational database are biased toward read operations; therefore, it follows that the most common SQL statement is designed to extract data from a database. To retrieve data from any SQL database engine, you must use a SELECT statement; SELECT statements range from very simple to incredibly complex, depending on your needs. Its most basic form, however, is simple and easy to use:

SELECT * FROM book;

The statement begins with the verb or action keyword SELECT, followed by a commaseparated list of columns to include in the dataset retrieved. In this case, we use the special identifier *, which is equivalent to extracting all of the columns available in the dataset. Following the list of columns is the keyword FROM,which is itself followed by a comma-separated list of tables. This statement retrieves data from only one table, the book table.

IMPORTANT: The format in which the dataset is returned to PHP by the database system depends largely on the system itself and on the extension you are using to access it; for example, the “traditional” MySQL library returns datasets as resources from which you can extract individual rows in the form of arrays. Newer libraries, on the other hand, tend to encapsulate result sets in objects.

You will rarely need to gain access to all of the records in a table—after all, relational databases are all about organizing data and making it easily searchable. Therefore, you will most often find yourself limiting the rows returned by a SELECT statement using a WHERE clause. For example, for the book table, you may wish to retrieve all books written by a specific author. This is possible using WHERE.

SELECT * FROM book WHERE author = ’Ray Bradbury’;

The recordset returned by this SELECT statement will contain all books written by the author specified in the WHERE clause (assuming, of course, that your naming convention is consistent). You may also list more than one parameter in a WHERE clause to further limit or broaden the results, using a number of logical conjunctions:

SELECT * FROM book 
WHERE author = ’Ray Bradbury’ OR author = ’George Orwell’;
SELECT * FROM book 
WHERE author = ’Ray Bradbury’AND publisher LIKE ’%Del Ray’;

The first example statement contains an OR clause and, thus, broadens the results to return all books by each author, while the second statement further restricts the results with an AND clause to all books by the author that were also published by a specific publisher. Note, here, the use of the LIKE operator, which provides a caseinsensitive match and allows the use of the % wild character to indicate an arbitrary number of characters. Thus, the expression AND publisher LIKE ’%Del Ray’ will match any publisher that ends in the string del ray, regardless of case.


views 2384
  1. Add New Comment