| |||||||||||||||||||||||||||||||||||||||||||||||||
Sunday, October 25, 2009
Lab 5.3 Nested IF Statements
Section 9.3. Using PEAR
9.3. Using PEARPEAR is a framework and distribution system for reusable PHP components. Actually, PEAR is a collection of add-on functionality for PHP development. There are many modules available to handle everything from session management to shopping cart functionality. Modules that are currently available are listed in Table 9-1.
Our list is not complete. Visit http://pear.php.net to find out all of the modules that are available for download. 9.3.1. InstallingPEAR uses a Package Manager to manage which PEAR features you install. Whether you need to install the Package Manager depends on which version of PHP you installed. If you're running PHP 4.3.0 or newer, it's already installed. If you're running PHP 5.0, PEAR has been split out into a separate package. The DB package that you're interested in is also installed by default with the Package Manager. So if you have the Package Manger, you're all set. 9.3.1.1. UnixYou can install the Package Manager on a Unix system by executing the following from the shell (command-line) prompt:
This takes the output of the go-pear.org site (which is actually the source PHP code) to install PEAR and passes it along to the php command for execution. 9.3.1.2. WindowsThe PHP 5 installation includes the PEAR installation script as C:\php\go-pear.bat. In case you didn't install all the files in Chapter 2, go ahead and extract all the PHP files to C:/php from the command prompt, and execute the .bat file. Figure 9-5 shows the initial screen after executing the PEAR installer. Figure 9-5. The go-pear.bat install scriptYou'll be asked a set of questions about paths. You can accept the defaults for all of them.
The PEAR installer creates a file called C:\php\PEAR_ENV.reg. You need to double-click to set up the PEAR paths in the registry. This file is contingent on which PEAR version you installed. When the dialog box appears to verify your information, you will add this to the registry and click OK. You may have to edit the php.ini file after running this .bat file to add the PEAR directory to the include path. Line 447 of php.ini now looks like this:
Apache must be restarted before the DB package can be used. 9.3.1.3. Hosted ISPMost ISPs have PEAR DB installed. Ask your ISP to install it if they haven't already. You can tell if PEAR DB has been installed by trying the PHP code in Example 9-7 to see whether the require_once ('DB.php'); line causes an error when the script is executed. 9.3.2. Adding Additional PackagesOnce that's complete, you can access the PEAR Package Manger by entering pear at the command prompt. Adding new modules is as easy as executing pear packagename. You won't need to do anything, since the DB package was installed along with the install by default. However, if you're running Windows XP Home, you'll need to take these steps to install the PEAR DB:
To find out what versions of PEAR packages Figure 9-6. A listing of installed PEAR packages and versionsOnce you've got PEAR installed, you're ready to try it out. 9.3.3. Rewriting the Books Example with PEARWhen using the PEAR DB package, you follow the same steps. However, the function syntax is slightly different. We'll go line by line and explain the differences as they appear in Example 9-7. Example 9-7. Displaying the books table with PEAR DB
Notice that Figure 9-7 is identical to the output in Figure 9-4. Line 3 includes your database login information and remains unchanged:
|
Line 4 has a new require statement:
require_once( "DB.php" );
This requires the file DB.php, which provides the PEAR DB functions. The require_once function errors out if the DB.php file is not found. It also will not include the file if it has been incorporated already. And, this would cause an error.
|
9.3.3.1. Creating a connect instance
The DB.php file defines a class of type DB. Refer to Chapter 5 for more information on working with classes and objects. We'll principally be calling the methods in the class. The DB class has a connect method, which we'll use instead of our old connect function mysql_connect. The double colons (::) indicate that we're calling that function from the class in line 4:
connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
When you call the connect function, it creates a new database connection that is stored in the variable $connection. The connect function attempts to connect to the database based on the connect string you passed to it.
9.3.3.2. Connect string
The connect string uses this new format to represent the login information that you already supplied in separate fields:
dbtype://username:password@host/database
This format may look familiar to you, as it's very similar to the connect string for a Windows share. The first part of the string is what really sets the PEAR functions apart from the plain PHP. The phptype field specifies the type of database to connect. Supported databases include ibase, msql, mssql, mysql, oci8, odbc, pgsql, and sybase. All that's required for your PHP page to work with a different type of database is changing the phptype!
The username, password, host, and database should be familiar from the basic PHP connect. Only the type of connection is required. However, you'll usually want to specify all fields.
After the values from dblogin.php are included, the connect string looks like the following:
"mysql://test:test@localhost/test"
If the connect method on line 6 was successful, a $DB object is created. It contains the methods to access the database as well as all of the information about the state of that database connection.
9.3.3.3. Querying
One of the methods it contains is called query. The query method works just like PHP's query function in that it takes a SQL statement. The difference is the hyphen and greater-than syntax (->) is used to call it from the object. It also returns the results as another object instead of a result set.
$query = "SELECT * FROM `books`"
$result = $connection->query($query);
Based on the SQL query, this code calls the query function from the connection object and returns a result object named $result.
9.3.3.4. Fetching
Line 22 uses the result object to call the fetchRow method. It returns the rows one at a time, similar to mysql_fetch_row.
while ($result_row = $result->fetchRow()) {
echo 'Title: '.$result_row[1] . '<br />';
echo 'Author: '.$result_row[4] . '<br /> ';
echo 'Pages: '.$result_row[2] . '<br /><br />';
}
You use another while loop to go through each row from fetchRow until it returns FALSE. The code in the loop hasn't changed from the non-PEAR
example.
9.3.3.5. Closing
In line 30, you're finished with the database connection, so you close it using the object method disconnect:
$connection->disconnect();
9.3.3.6. PEAR error reporting
The function DB::isError will check to see whether the result that's been returned to you is an error or not. If it is an error, you can use DB::errorMessage to return a text description of the error that was generated. You need to pass DB::errorMessage the return value from your function as an argument.
Here you rewrite the PEAR code to use error checking:
<?php
if ( DB::isError( $demoResult = $db->query( $sql)))
{
echo DB::errorMessage($demoResult);
} else {
while ($demoRow = $demoResult->fetchRow()) {
echo $demoRow[2] . '<br />';
}
}
?>
Now that you have a good handle on connecting to the database and the various functions of PEAR, we're going to talk about forms. Forms provide a way to send substantial data from the user to the server where it can be processed.
Section 10.2. Subqueries
10.2. Subqueries
A subquery is a SELECT statement nested within another SQL statement. This feature became
available as of version 4.1 of MySQL. Although the same results can be
accomplished by using the JOIN clause or
UNION, depending on the situation, subqueries are a
cleaner approach that is sometimes easier to read. They make a complex
query more modular, which makes it easier to create and to troubleshoot.
Here is a simple example of a subquery:
SELECT *
FROM
(SELECT col1, col2
FROM table1
WHERE col_id = 1000) AS derived1
ORDER BY col2;
In this example, the subquery or inner query
is a SELECT statement specifying two column names. The other query is called the main or
outer query. It doesn't have to be a
SELECT. It can be an INSERT, a
DELETE, a DO, an
UPDATE, or even a SET statement. The
outer query generally can't select data or modify data from the same table
as an inner query, but this doesn't apply if the subquery is part of a
FROM clause. A subquery can return a value (a scalar),
a field, multiple fields containing values, or a full results set that
serves as a derived table.
You can encounter performance problems with subqueries if they are
not well constructed. One problem occurs when a subquery is placed within
an IN( ) clause as part of a
WHERE clause. It's generally better to use the
= operator for each value, along with
AND for each parameter/value pair.
When you see a performance problem with a subquery, try
reconstructing the SQL statement with JOIN and compare
the differences using the BENCHMARK( ) function.
If the performance is better without a subquery, don't give up on
subqueries. Only in some situations is performance poorer. For those
situations where there is a performance drain, MySQL AB is working on
improving MySQL subqueries. So performance problems you experience now may
be resolved in future versions. You may just need to upgrade to the
current release or watch for improvements in future releases.
10.2.1. Single Field Subqueries
The most basic subquery is one that returns a scalar or single
value. This type of subquery is particularly useful in a WHERE clause in conjunction with an
= operator, or in other instances where a single
value from an expression is permitted.
As an example of this situation, suppose that at our fictitious
college one of the music teachers, Sonia Oram, has called us saying that
she wants a list of students for one of her classes so that she can call
them to invite them to a concert. She wants the names and telephone
numbers for only the students in her first period Monday morning
class.
The way most databases store this data, the course number would be
a unique key and would make it easy to retrieve the other data without a
subquery. But Sonia doesn't know the course number, so we enter an SQL
statement like this:
SELECT CONCAT(name_first, ' ', name_last) AS student,
phone_home, phone_dorm
FROM students
JOIN course_rosters USING (student_id)
WHERE course_id =
(SELECT course_id
FROM course_schedule
JOIN teachers USING (teacher_id)
WHERE semester_code = '2007AU'
AND class_time = 'monday_01'
AND name_first = 'Sonia'
AND name_last = 'Oram');
Notice in the subquery that we're joining the
course_schedule table with
teachers so we can give the teacher's first and last
name in the WHERE clause of the subquery. We're also
indicating in the WHERE clause a specific semester
(Autumn 2007) and time slot (Monday, first period). The results of these
specifics should be one course identification number because a teacher
won't teach more than one class during a particular class period. That
single course number will be used by the WHERE clause
of the main query to return the list of students on the class roster for
the course, along with their telephone numbers.
If by chance more than one value is returned by the subquery in
the previous example, MySQL will
return an error:
ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = "Subquery returns more than 1 row"
Despite our supposition, it is possible that a teacher might teach
more than one class at a time: perhaps the teacher is teaching one
course in violin and another in viola, but each class had so few
students that the department head put them together. In such a
situation, the teacher would want the data for both course numbers. To
use multiple fields derived from a subquery in a
WHERE clause like this, we would have to use
something other than the = operator, such as
IN. For this kind of situation, see the next section
on Section 10.2.2."
10.2.2. Multiple Fields Subqueries
In the previous section, we discussed instances where one scalar
value was obtained from a subquery in a WHERE clause.
However, there are times when you may want to match multiple values. For
those situations you will need to use the subquery in conjunction with
an operator or a clause: ALL, ANY,
EXISTS, IN, or
SOME.
As an example of a multiple fields subquery—and specifically of a
subquery using IN (or using ANY or
SOME)—let's adapt the example from the previous
section to a situation where the teacher wants the contact information
for students in all of her classes. To do this, we can enter the
following SQL statement:
SELECT CONCAT(name_first, ' ', name_last) AS student,
phone_home, phone_dorm
FROM students
JOIN course_rosters USING (student_id)
WHERE course_id IN
(SELECT course_id
FROM course_schedule
JOIN teachers USING (teacher_id)
WHERE semester_code = '2007AU'
AND name_first = 'Sonia'
AND name_last = 'Oram');
In this example, notice that the subquery is contained within the
parentheses of the IN clause. Subqueries are executed
first, so the results will be available before the
WHERE clause is executed. Although a comma-separated
list isn't returned, MySQL still accepts the results so that they may be
used by the outer query. The criteria of the WHERE
clause here does not specify a specific time slot as the earlier example
did, so multiple values are much more likely to be returned.
Instead of IN, you can use ANY or SOME
to obtain the same results by the same methods. (ANY
and SOME are synonymous.) These two keywords must be
preceded by a comparison operator (e.g., =,
<, >). For example, we could
replace the IN in the SQL previous statement with
= ANY or with = SOME and the same
results will be returned. IN can be preceded with
NOT for negative comparisons: NOT
IN(...). This is the same as != ANY (...)
and != SOME (...).
Let's look at another subquery returning multiple values but using
the ALL operator. The ALL operator must be preceded by a
comparison operator (e.g., =,
<, >). As an example of this
usage, suppose one of the piano teachers provides weekend seminars for
students. Suppose also that he heard a few students are enrolled in all
of the seminars he has scheduled for the semester and he wants a list of
their names and telephone numbers in advance. We should be able to get
that data by entering an SQL statement like the following (though
currently it doesn't work, for reasons to be explained shortly):
SELECT DISTINCT student_id,
CONCAT(name_first, ' ', name_last) AS student
FROM students
JOIN seminar_rosters USING (student_id)
WHERE seminar_id = ALL
(SELECT seminar_id
FROM seminar_schedule
JOIN teachers ON (instructor_id = teacher_id)
WHERE semester_code = '2007AU'
AND name_first = 'Sam'
AND name_last = 'Oram');
In this example, a couple of the tables have different column
names for the ID we want, and we have to join one of them with
ON instead of USING, but that has
nothing to do with the subquery. What's significant is that this
subquery returns a list of seminar identification numbers and is used in
the WHERE clause of the main query with =
ALL. Unfortunately, although this statement is constructed
correctly, it doesn't work with MySQL at the time of this writing and
just returns an empty set. However, it should work in future releases of
MySQL, so I've included it for future reference. For now, we would have
to reorganize the SQL statement like so:
Code View:
SELECT student_id, student
FROM
(SELECT student_id, COUNT(*)
AS nbr_seminars_registered,
CONCAT(name_first, ' ', name_last)
AS student
FROM students
JOIN seminar_rosters USING (student_id)
WHERE seminar_id IN
(SELECT seminar_id
FROM seminar_schedule
JOIN teachers
ON (instructor_id = teacher_id)
WHERE semester_code = '2007AU'
AND name_first = 'Sam'
AND name_last = 'Oram')
GROUP BY student_id) AS students_registered
WHERE nbr_seminars_registered =
(SELECT COUNT(*) AS nbr_seminars
FROM seminar_schedule
JOIN teachers
ON (instructor_id = teacher_id)
WHERE semester_code = '2007AU'
AND name_first = 'Sam'
AND name_last = 'Oram');
This is much more involved, but it does work with the latest
release of MySQL.
The first subquery is used to get the student's name. This
subquery's WHERE clause uses another subquery to
retrieve the list of seminars taught by the professor for the semester,
to determine the results set from which the main query will draw its
ultimate data. The third subquery
counts the number of seminars that the same professor is teaching for
the semester. This single value is used with the
WHERE clause of the main query. In essence, we're
determining the number of seminars the professor is teaching and which
students are registered for all of them.
The last possible method for using multiple fields in a subquery
uses EXISTS. With
EXISTS, in order for it to return meaningful or
desired results, you need to stipulate in the WHERE
clauses of the subquery a point in which it is joined to the outer
query. Using the example from the previous section involving the teacher
Sonia Oram, let's suppose that we want to retrieve a list of courses
that she teaches:
SELECT DISTINCT course_id, course_name
FROM courses
WHERE EXISTS
(SELECT course_id
FROM course_schedule
JOIN teachers USING (teacher_id)
WHERE semester_code = '2007AU'
AND name_first = 'Sonia'
AND name_last = 'Oram'
AND courses.course_id = course_schedule.course_id);
As you can see here, we've added EXISTS to the
WHERE clause with the subquery in parentheses,
similar to using IN. The significant difference is
that we added courses.course_id =
course_schedule.course_id to the end. Without it, a list of
all courses would be returned regardless of the criteria of the
WHERE clause in the subquery. Incidentally, if we
specified NOT EXISTS instead, we would get all
courses except for the ones taught by the teacher
given.
10.2.3. Results Set Subqueries
A subquery can be used to generate a results set, which is a table
from which an outer query can select data. That is, a subquery can be
used in a FROM clause as if it were another table in
a database. It is a derived table. Along these
lines, each derived table must be named. This is done with
AS following the parentheses containing the subquery.
A subquery contained in a FROM clause generally
cannot be a correlated subquery—that is, it cannot reference the same
table as the outer query. The exception is if it's constructed with a
JOIN.
In the following example, let's consider the subquery separately
as though it were a plain query and not a subquery. It will generate a
results set containing the student's ID and the student's average exam
score for a specific course taught during a specific semester. The query
uses AVG( ), which requires a
GROUP BY clause. The problem with GROUP BY is that it will order
data only by the columns by which it's given to group data. In this
case, it will order the data by student_id and not
list the results by any other, more useful column. If we want to order
the data so that the highest student average is first, descending in
order to the lowest student average, we have to turn our query into a
subquery and have the outer query re-sort the results:
SELECT CONCAT(name_first, ' ', name_last) AS student,
student_id, avg_grade
FROM students
JOIN
(SELECT student_id,
AVG(exam_grade) AS avg_grade
FROM exams
WHERE semester_code = '2007AU'
AND course_id = 1489
GROUP BY student_id) AS grade_averages
USING(student_id)
ORDER BY avg_grade DESC;
The results set (the derived table generated by the subquery in
the FROM clause) is named
grade_averages. Notice that although the column
student_id exists in the derived table, in the table
from which it gets its data (i.e., exams) and in the
primary table used in the main query (i.e.,
students), there is no ambiguity. No error is
generated. However, if we wanted to specify that the data be taken from
the derived table, we could put
grade_averages.student_id in the
SELECT of the outer query.
This subquery is a correlated subquery, which is generally not permitted in a
FROM clause. It's allowed in this example because we
are using a JOIN to join the results set to the table
referenced in the outer query.
Section 2.3. Use Case Overview Diagrams
2.3. Use Case Overview DiagramsWhen you are trying to understand a system, it is sometimes useful to get a glimpse of the context within which it sits. For this purpose, UML provides the Use Case Overview diagram. Use Case Overview diagrams give you an opportunity to paint a broad picture of your system's context or domain (see Figure 2-16 for an example). Figure 2-16. The CMS's context as shown on a Use Case Overview diagramUnfortunately, Use Case Overviews are badly named as they don't usually contain any use cases. The use cases are not shown because the overview is designed to provide a context to your system; the system's internalscaptured by use casesare not normally visible. Use Case Overviews are a useful place to show any extra snippets of information when understanding your system's place within the world. Those snippets often include relationships and communication lines between actors. These contextual pieces of information do not usually contain a great deal of detail, they are more a placeholder and starting point to for the rest of your model's detail. |
Section 9.8. Pseudo File Systems
9.8. Pseudo File SystemsA number of file systems fall under the category of Pseudo File Systems in the kernel-configuration menu. Together they provide a range of facilities useful in a wide range of applications. For additional information, especially on the proc file system, spend an afternoon poking around this useful system facility. Where appropriate, references to additional reading material can be found in Section 9.11.1, at the end of this chapter. 9.8.1. Proc File SystemThe /proc file system took its name from its original purpose, an interface that allows the kernel to communicate information about each running process on a Linux system. Over the course of time, it has grown and matured to provide much more than process information. We introduce the highlights here; a complete tour of the /proc file system is left as an exercise for the reader. The /proc file system has become a virtual necessity for all but the simplest of Linux systems, even embedded ones. Many user-level functions rely on the contents of the /proc file system to do their job. For example, the mount command, issued without any parameters, lists all the currently active mount points on a running system, from the information delivered by /proc/mounts. If the /proc file system is not available, the mount command silently returns. Listing 9-14 illustrates this on the ADI Engineering Coyote board. Listing 9-14. Mount Dependency on /proc
Notice in Listing 9-14 that /proc itself is listed as a mounted file system, as type proc mounted on /proc. This is not doublespeak; your system must have a mount point called /proc at the top-level directory tree as a destination for the /proc file system to be mounted on.[6] To mount the /proc file system, use the mount command as with any other file system:
$ mount -t proc /proc /procThe general form of the mount command, from the man page, is mount [-t fstype] something somewhere. In the previous invocation, we could have substituted none for /proc, as follows: $ mount -t proc none /procThis looks somewhat less like doublespeak. The something parameter is not strictly necessary because /proc is a pseudo file system and not a real physical device. However, specifying /proc as in the earlier example helps remind us that we are mounting the /proc file system on the /proc directory (or, more appropriately, on the /proc mount point). Of course, by this time, it might be obvious that to get /proc file system functionality, it must be enabled in the kernel configuration. This kernel-configuration option can be found in the File Systems submenu under the category Pseudo File Systems. Each user process running in the kernel is represented by an entry in the /proc file system. For example, the init process introduced in Chapter 6 is always assigned the process id (PID) of 1. Processes in the /proc file system are represented by a directory that is given the PID number as its name. For example, the init process with a PID of 1 would be represented by a /proc/1 directory. Listing 9-15 shows the contents of this directory on our embedded Coyote board. Listing 9-15. init Process /proc EnTRies
These entries, which are present in the /proc file system for each running process, contain much useful information, especially for analyzing and debugging a process. For example, the cmdline entry contains the complete command line used to invoke the process, including any arguments. The cwd and root directories contain the processes' view of the current working directory and the current root directory. One of the more useful entries for system debugging is the maps entry. This contains a list of each virtual memory segment assigned to the program, along with attributes about each. Listing 9-16 is the output from /proc/1/maps in our example of the init process. Listing 9-16. init Process Memory Segments from /proc
The usefulness of this information is readily apparent. You can see the program segments of the init process itself in the first two entries. You can also see the memory segments used by the shared library objects being used by the init process. The format is as follows: vmstart-vmend attr pgoffset devname inode filename Here, vmstart and vmend are the starting and ending virtual memory addresses, respectively; attr indicates memory region attributes, such as read, write, and execute, and tells whether this region is shareable; pgoffset is the page offset of the region (a kernel virtual memory parameter); and devname, displayed as xx:xx, is a kernel representation of the device ID associated with this memory region. The memory regions that are not associated with a file are also not associated with a device, thus the 00:00. The final two entries are the inode and file associated with the given memory region. Of course, if there is no file, there is no inode associated with it, and it displays with a zero. These are usually data segments. Other useful entries are listed for each process. The status entry contains useful status information about the running process, including items such as the parent PID, user and group IDs, virtual memory usage stats, signals, and capabilities. More details can be obtained from the references at the end of the chapter. Some frequently used /proc enTRies are cpuinfo, meminfo, and version. The cpuinfo enTRy lists attributes that the kernel discovers about the processor(s) running on the system. The meminfo enTRy provides statistics on the total system memory. The version entry mirrors the Linux kernel version string, together with information on what compiler and machine were used to build the kernel. Many more useful /proc entries are provided by the kernel; we have only scratched the surface of this useful subsystem. Many utilities have been designed for extracting and reporting information contained with the /proc file system. Two popular examples are top and ps, which every embedded Linux developer should be intimately familiar with. These are introduced in Chapter 13. Other utilities useful for interfacing with the /proc file system include free, pkill, pmap, and uptime. See the procps package for more details. 9.8.2. sysfsLike the /proc file system, sysfs is not representative of an actual physical device. Instead, sysfs models specific kernel objects such as physical devices and provides a way to associate devices with device drivers. Some agents in a typical Linux distribution depend on the information on sysfs. We can get some idea of what kinds of objects are exported by looking directly at the directory structure exported by sysfs. Listing 9-17 shows the top-level /sys directory on our Coyote board. Listing 9-17. Top-Level /sys Directory Contents
As you can see, sysfs provides a subdirectory for each major class of system device, including the system buses. For example, under the block subdirectory, each block device is represented by a subdirectory entry. The same holds true for the other directories at the top level. Most of the information stored by sysfs is in a format more suitable for machines than humans to read. For example, to discover the devices on the PCI bus, one could look directly at the /sys/bus/pci subdirectory. On our Coyote board, which has a single PCI device attached (an Ethernet card), the directory looks like this: # ls /sys/bus/pci/devices/ This entry is actually a symbolic link pointing to another node in the sysfs directory tree. We have formatted the output of ls here to illustrate this, while still fitting in a single line. The name of the symbolic link is the kernel's representation of the PCI bus, and it points to a devices subdirectory called pci0000:00 (the PCI bus representation), which contains a number of subdirectories and files representing attributes of this specific PCI device. As you can see, the data is rather difficult to discover and parse. A useful utility exists to browse the sysfs file system directory structure. Called systool, it comes from the sysfsutils package found on sourceforge.net. Here is how systool would display the PCI bus from the previous discussion: $ systool -b pci Again we see the kernel's representation of the bus and device (0f), but this time the tool displays the vendor ID (8086 = Intel) and device ID (1229 = eepro100 Ethernet card) obtained from the /sys/devices/pci0000:00 branch of /sys where these attributes are kept. Executed with no parameters, systool displays the top-level system hierarchy. Listing 9-18 is an example from our Coyote board. Listing 9-18. Output from systool
You can see from this listing the variety of system information available from sysfs. Many utilities use this information to determine the characteristics of system devices or to enforce system policies, such as power management and hot-plug capability. |
2.5 Issuing Queries and Retrieving Results
| I l@ve RuBoard |
2.5 Issuing Queries and Retrieving Results2.5.1 ProblemYou 2.5.2 SolutionSome statements only return a status code, others return a result set 2.5.3 DiscussionThis section is the longest of the chapter because there are two In Chapter 1, we created a table named CREATE TABLE profile The profile table reflects that the things that
To create the table, use the profile.sql script % mysql cookbook < profile.sql Another way to create the table is to issue the
The initial contents of the profile table loaded mysql> SELECT * FROM profile; Most of the columns in the profile table allow 2.5.4 SQL Statement CategoriesSQL
The first step in processing a query is to send it to the MySQL After sending the query to the server, the next step is to check
Now we're ready to see how to issue queries in each 2.5.5 PerlThe my $count = $dbh->do ("UPDATE profile SET cats = cats+1 WHERE name = 'Fred'");If the query executes successfully but affects no rows, do( my $count = $dbh->do ("UPDATE profile SET color = color WHERE name = 'Fred'");If my $count = $dbh->do ("UPDATE profile SET color = color WHERE name = 'Fred'");To process
The following example illustrates these steps, using my $sth = $dbh->prepare ("SELECT id, name, cats FROM profile");The row-fetching loop just shown is followed by a call to The example illustrates that if you want to know how many rows a DBI has several functions that can be used to obtain a row at a time fetchrow_arrayref( my $sth = $dbh->prepare ("SELECT id, name, cats FROM profile");fetchrow_hashref( my $sth = $dbh->prepare ("SELECT id, name, cats FROM profile");The elements of the hash are accessed using the names of the columns SELECT id, id FROM profile To avoid this problem, you can use SELECT id, id AS id2 FROM profile Admittedly, this query is pretty silly, but if In addition to the methods for performing the query execution process
Most of these methods return a reference. The exception is my @val = $dbh->selectrow_array ( When selectrow_array( ) is called in array my $ncols = @val; You can also invoke selectrow_array( ) in scalar my $buddy_count = $dbh->selectrow_array ("SELECT COUNT(*) FROM profile");If a query returns no result, selectrow_array( ) selectrow_arrayref( my $ref = $dbh->selectrow_arrayref ($query); With selectcol_arrayref( my $ref = $dbh->selectcol_arrayref ($query); selectall_arrayref( my $ref = $dbh->selectall_arrayref ($query); selectall_hashref( my $ref = $dbh->selectall_hashref ("SELECT * FROM profile", "id");Then access rows using the keys of the hash. For example, if one of my @keys = (defined ($ref) ? keys (%{$ref}) : ( ));The selectall_XXX( Take care when using the high-level methods if you have 2.5.6 PHPPHP $result_id = mysql_query ($query, $conn_id); If the query fails, $result_id will be If $result_id is not FALSE, the $result_id = mysql_query ("DELETE FROM profile WHERE cats = 0", $conn_id);mysql_affected_rows( ) takes the connection For queries that return a result set, mysql_query( Here's an example that shows how to run a $result_id = mysql_query ("SELECT id, name, cats FROM profile", $conn_id);The example demonstrates that you obtain the rows in the result set Each PHP row-fetching function returns the next row of the result set $result_id = mysql_query ("SELECT id, name, cats FROM profile", $conn_id);Despite what you might expect, mysql_fetch_array( The previous example does not quote the non-numeric element names printf ("id: %s, name: %s, cats: %s\n",mysql_fetch_object( $result_id = mysql_query ("SELECT id, name, cats FROM profile", $conn_id);PHP 4.0.3 adds a fourth row-fetching function,
2.5.7 PythonThe
try: If the query does return a result set, fetch its rows and close the try: As you can see from the preceding example, the Another row-fetching method, fetchall( try: Like DBI, DB-API doesn't provide any way to rewind a To access row values by column name, specify the try: 2.5.8 JavaThe To issue a query, the first step is to get a Statement s = conn.createStatement ( ); Then use the Statement object to send the query to The executeUpdate( ) method sends a query that try For statements that return a result set, use executeQuery( try The ResultSet object returned by the Column values are accessed while (rs.next ( )) // loop through rows of result set You can retrieve a given column value using any String id = rs.getString ("id");Or you can use getObject( Object id = rs.getObject ("id");To find out how many columns are in each row, access the result try The third JDBC query-executing method, execute( try
|
| I l@ve RuBoard |