Learning PHP and MySQL
Monday, August 07, 2006
Example 08-12 Using DAYNAME to get the day of the week as a name
SELECT DAYNAME('1964-10-12');
Example 08-13 Finding days since the start of the year
SELECT DAYOFYEAR('2006-1-1'), DAYOFYEAR('2006-12-24');
Example 08-14 Using MONTH and MONTHNAME on the purchases table
SELECT `day`,MONTH(`day`),MONTHNAME(`day`) FROM `purchases`;
Example 08-15 Using HOUR and MINUTE on a time
SELECT CONCAT_WS(':',hour('4:46:45'),MINUTE('4:46:45'));
Example 08-16 Using DATE_SUB to subtract days
SELECT DATE_SUB(NOW(), INTERVAL 12 DAY);
Posted by krautgrrl on 08/07 at 05:17 PM
Chapter 8 Code • (26) Comments • (1) Trackbacks • Permalink
Chapter 8 Code • (26) Comments • (1) Trackbacks • Permalink
Example 08-17 Using the minus operator on a date
SELECT NOW()-INTERVAL 12 DAY;
Example 08-18 Using DATE_FORMAT with a string to place colons between the segments
SELECT DATE_FORMAT('2006-12-24 09:09:23', '%h:%i:%s');
Example 09-01 PHP file format
<?php
$db_host='hostname of database server';
$db_database='database name';
$db_username='username';
$db_password='password';
?>
Posted by krautgrrl on 08/07 at 05:20 PM
Chapter 9 Code • (0) Comments • (248) Trackbacks • Permalink
Chapter 9 Code • (0) Comments • (248) Trackbacks • Permalink
Example 09-02 The db_login.php file with values filled in2
<?php
$db_host='localhost';
$db_database='test';
$db_username='test';
$db_password='yourpass';
?>
Example 09-03 The SQL to recreate the test objects8
--
-- Table structure for table `authors`
--
DROP TABLE IF EXISTS `authors`;
CREATE TABLE `authors` ( `author_id` int(11) NOT NULL auto_increment,
`title_id` int(11) NOT NULL default '0',
`author` varchar(125) default NULL,
PRIMARY KEY (`author_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `authors`
--
INSERT INTO `authors` VALUES (1,1,'Ellen Siever'),(2,1,'Aaron Weber'),
(3,2,'Arnold Robbins'),(4,2,'Nelson H.F. Beebe');
--
-- Table structure for table `books`
--
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` ( `title_id` int(11) NOT NULL auto_increment,
`title` varchar(150) default NULL,
`pages` int(11) default NULL,
PRIMARY KEY (`title_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `books`
--
INSERT INTO `books` VALUES (1,'Linux in a Nutshell',476),
(2,'Classic Shell Scripting',256);
--
-- Table structure for table `purchases`
--
DROP TABLE IF EXISTS `purchases`;
CREATE TABLE `purchases` ( `id` int(11) NOT NULL auto_increment,
`user` varchar(10) default NULL,
`title` varchar(150) default NULL,
`day` date default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `purchases`
--
LOCK TABLES `purchases` WRITE;
INSERT INTO `purchases` VALUES (1,'Mdavis','Regular Expression Pocket Reference','2005-02-15'),
(2,'Mdavis','JavaScript & DHTML Cookbook','2005-02-10');
Example 09-04 Including the connection values and calling mysql_connect
<?php include('db_login.php');
$connection = mysql_connect($db_host, $db_username, $db_password);
if (!$connection){
die ("Could not connect to the database: <br />". mysql_error());
}
?>
Example 09-05 Displaying the books and authors
<?php
// Include our login information include('db_login.php');
// Connect
$connection = mysql_connect( $db_host, $db_username, $db_password );
if (!$connection)
{
die ("Could not connect to the database: <br />". mysql_error());
}
// Select the database
$db_select=mysql_select_db($db_database);
if (!$db_select)
{
die ("Could not select the database: <br />". mysql_error());
}
// Assign the query
$query = "SELECT * FROM `books` NATURAL JOIN `authors`";
// Execute the query
$result = mysql_query( $query );
if (!$result)
{
die ("Could not query the database: <br />". mysql_error());
}
// Fetch and display the results
while ($result_row = mysql_fetch_row(($result)))
{
echo 'Title: '.$result_row[1] . '<br />';
echo 'Author: '.$result_row[4] . '<br /> ';
echo 'Pages: '.$result_row[2] . '<br /><br />';
}
/ /Close the connection mysql_close($connection);
?>
Posted by krautgrrl on 08/07 at 05:24 PM
Chapter 9 Code • (0) Comments • (108) Trackbacks • Permalink
Chapter 9 Code • (0) Comments • (108) Trackbacks • Permalink
Example 09-06 Displaying the results of a query in an HTML table
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html401/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Displaying in an HTML table</title>
</head>
<body>
<table border="1">
<tr>
<th>Title</th>
<th>Author</th>
<th>Pages</th>
</tr>
<?php
//Include our login information include('db_login.php');
// Connect
$connection = mysql_connect($db_host, $db_username, $db_password);
if (!$connection){
die("Could not connect to the database: <br />". mysql_error());
}
// Select the database
$db_select = mysql_select_db($db_database);
if (!$db_select){
die ("Could not select the database: <br />". mysql_error());
}
// Assign the query
$query = "SELECT * FROM `books` NATURAL JOIN `authors`";
// Execute the query
$result = mysql_query($query);
if (!$result){
die ("Could not query the database: <br />". mysql_error());
}
// Fetch and display the results
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
$title = $row["title"];
$author = $row["author"];
$pages = $row["pages"];
echo "<tr>";
echo "<td>$title</td>"; echo "<td>$author</td>"; echo "<td>$pages</td>"; echo "</tr>";
}
// Close the connection mysql_close($connection);
?>
</table>
</body>
</html>
Example 09-07 Displaying the books table with PEAR DB
<?php
include('db_login.php');
require_once('DB.php');
$connection = DB::connect("mysql://$db_username:$db_password@$db_host/$db_database");
if (DB::isError($connection)){
die("Could not connect to the database: <br />".DB::errorMessage($connection));
}
$query = "SELECT * FROM `books` NATURAL JOIN `authors`";
$result = $connection->query($query);
if (DB::isError($result)){
die("Could not query the database:<br />".$query." ".DB::errorMessage($result));
}
echo('<table border="1">');
echo '<tr><th>Title</th><th>Author</th><th>Pages</th></tr>';
while ($result_row = $result->fetchRow()) {
echo "<tr><td>";
echo $result_row[1] . '</td><td>';
echo $result_row[4] . '</td><td>';
echo $result_row[2] . '</td></tr>';
}
echo("</table>");
$connection->disconnect();
?>
Posted by krautgrrl on 08/07 at 05:25 PM
Chapter 9 Code • (0) Comments • (1125) Trackbacks • Permalink
Chapter 9 Code • (0) Comments • (1125) Trackbacks • Permalink
Friday, August 18, 2006
Example 10-01 A simple form example
Put this code in a file named, simple.php
<html>
<head>
<title>Building a Form</title>
</head>
<body>
<form action="<?php echo($_SERVER['PHP_SELF']); ?>"
method="get">
<label>
Search: <input type="text" name="search" />
</label>
<input type="submit" value="Go!" />
</form>
</body>
</html>
Posted by krautgrrl on 08/18 at 10:50 AM
Chapter 10 Code • (0) Comments • (23) Trackbacks • Permalink
Chapter 10 Code • (0) Comments • (23) Trackbacks • Permalink
Statistics
This page has been viewed 375182 times
Page rendered in 0.2484 seconds
Total Entries: 224
Total Comments: 16
Total Trackbacks: 307338
Most Recent Entry: 09/27/2006 12:39 pm
Most Recent Comment on: 10/26/2007 10:00 am
Total Members: 2
Total Logged in members: 0
Total guests: 11
Total anonymous users: 0
Most Recent Visitor on: 02/10/2012 09:20 am
The most visitors ever was 1103 on 11/20/2007 12:50 pm
