Learning PHP and MySQL
Chapter 8 Code
Monday, August 07, 2006
Example 08-01 The contents of the my_backup.sql file
-- MySQL dump 10.9
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 4.1.11-Debian_4-log
--
-- 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`
--
/*!40000 ALTER TABLE `authors` DISABLE KEYS */; LOCK TABLES `authors` WRITE;
INSERT INTO `authors` VALUES (1,1,'Ellen Siever'),(2,1,'Aaron Weber'),(3,2,'Arno ld Robbins'),(4,2,'Nelson Beebe');
UNLOCK TABLES;
/*!40000 ALTER TABLE `authors` ENABLE KEYS */;
Posted by krautgrrl on 08/07 at 05:10 PM
Chapter 8 Code • (3) Comments • (256) Trackbacks • Permalink
Chapter 8 Code • (3) Comments • (256) Trackbacks • Permalink
Example 08-02 Book titles in CSV format
1,Linux in a Nutshell,476
2,Classic Shell Scripting,256
Example 08-03 Creating a simple index
CREATE UNIQUE INDEX `authind` ON `authors` (`author`);
Example 08-04 Using CONCAT to put fields together
SELECT CONCAT(`title`,' has ',`pages`,' pages.') FROM `books`;
Example 08-05 Calculating the length of a string
SELECT CONCAT(`title`,' has ', LENGTH(`title`), ' characters.') FROM `books`;
Example 08-06 Changing the case of the title
SELECT UCASE(`title`), LCASE(`title`) from `books`;
Example 08-07 Using the LEADING option to remove zeros
SELECT TRIM(LEADING '0' from '0000Example00000');
Example 08-08 Using TRIM with the TRAILING option
SELECT TRIM(TRAILING '0' from '0000Example00000');
Example 08-09 Looking for the string in our author names
SELECT LOCATE`author`,LOCATE('on',`author`) FROM `authors`;
Example 08-10 Adding the formatting to a phone number using LEFT, RIGHT, and SUBSTR
SELECT CONCAT('(', LEFT('6128238193',3),
')', SUBSTR('6128238193',4,3),
'-',
RIGHT('6128238193', 4));
Posted by krautgrrl on 08/07 at 05:15 PM
Chapter 8 Code • (26) Comments • (1) Trackbacks • Permalink
Chapter 8 Code • (26) Comments • (1) Trackbacks • Permalink
Example 08-11 Using WEEKDAY to get the day of the week
SELECT WEEKDAY('1964-10-12');
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'));
Statistics
This page has been viewed 375189 times
Page rendered in 0.2263 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:23 am
The most visitors ever was 1103 on 11/20/2007 12:50 pm
