Database Reference
In-Depth Information
$sth
=
$dbh
->
prepare
(
"SELECT srcuser,
DATE_FORMAT(t,'%M %e, %Y') AS date_sent
FROM mail"
);
$sth
->
execute
();
while
(
my
$ref
=
$sth
->
fetchrow_hashref
())
{
printf
"user: %s, date sent: %s\n"
,
$ref
->
{
srcuser
},
$ref
->
{
date_sent
};
}
In Java, you'd do something like this, where the argument to
getString()
names the
column to access:
Statement
s
=
conn
.
createStatement
();
s
.
executeQuery
(
"SELECT srcuser,"
+
" DATE_FORMAT(t,'%M %e, %Y') AS date_sent"
+
" FROM mail"
);
ResultSet
rs
=
s
.
getResultSet
();
while
(
rs
.
next
())
// loop through rows of result set
{
String
name
=
rs
.
getString
(
"srcuser"
);
String
dateSent
=
rs
.
getString
(
"date_sent"
);
System
.
out
.
println
(
"user: "
+
name
+
", date sent: "
+
dateSent
);
}
rs
.
close
();
s
.
close
();
Recipe 2.4
shows for each of our programming languages how to fetch rows into data
structures that permit access to column values by name. The
select
directory of the
recipes
distribution has examples that show how to do this for the
mail
table.
You cannot refer to column aliases in a
WHERE
clause. Thus, the following statement is
illegal:
mysql>
SELECT t, srcuser, dstuser, size/1024 AS kilobytes
->
FROM mail WHERE kilobytes > 500;
ERROR 1054 (42S22): Unknown column 'kilobytes' in 'where clause'
The error occurs because an alias names an
output
column, whereas a
WHERE
clause
operates on
input
columns to determine which rows to select for output. To make the
statement legal, replace the alias in the
WHERE
clause with the same column or expression
that the alias represents:
mysql>
SELECT t, srcuser, dstuser, size/1024 AS kilobytes
->
FROM mail WHERE size/1024 > 500;
+---------------------+---------+---------+-----------+
| t | srcuser | dstuser | kilobytes |
+---------------------+---------+---------+-----------+
| 2014-05-14 17:03:01 | tricia | phil | 2338.3613 |
| 2014-05-15 10:25:52 | gene | tricia | 975.1289 |
+---------------------+---------+---------+-----------+
