Web Development with PHP and MySQL

If you're looking for MySQL data to experiment with, download the Sakila schema.

The following MySQL database schema is for an address book, or contacts database, and was exported from MS Outlook™ to MS Excel™ and then converted to MySQL™ using Intelligent Converters. Utilizing the underscore character instead of using CaMelCase names for columns is more desirable. In other words, a column named as first_name will be less problematic to work with than FirstName, plus there are times when using PHP to replace the underscore character with a space is quite useful. Depending on your system configuration, FirstName may very well be quietly converted by MySQL™ to lowercase firstname when the schema is dumped into MySQL™. Better to stick to lower case column names consisting only of alphanumeric characters and the underscore character (MySQL™ column names cannot begin with a digit 0-9). This will make your MySQL™ project more portable across operating systems and system configurations. Even if it is legal to use the hyphen character in a MySQL™ database name or table name, avoid doing so as it will eventually cause problems (if a PHP script treats it as a minus sign when processing code, and you end up wasting 8 hours trying to figure out what's wrong).

--
-- Table structure for table `vs_outlook`
--

CREATE TABLE `vs_outlook` (
  `CID` mediumint(6) unsigned NOT NULL auto_increment,
  `ContactCat` tinyint(3) unsigned NOT NULL default '0',
  `Status` tinyint(3) unsigned NOT NULL default '0',
  `LeadCat` tinyint(3) unsigned NOT NULL default '0',
  `Title` varchar(255) default '',
  `FirstName` varchar(255) default '',
  `MiddleName` varchar(255) default '',
  `LastName` varchar(255) default '',
  `Suffix` varchar(255) default '',
  `Company` varchar(255) default '',
  `Department` varchar(255) default '',
  `JobTitle` varchar(255) default '',
  `BusinessStreet` varchar(255) default '',
  `BusinessStreet2` varchar(255) default '',
  `BusinessStreet3` varchar(255) default '',
  `BusinessCity` varchar(255) default '',
  `BusinessState` varchar(255) default '',
  `BusinessPostalCode` varchar(255) default '',
  `BusinessCountry` varchar(255) default '',
  `HomeStreet` varchar(255) default '',
  `HomeStreet2` varchar(255) default '',
  `HomeStreet3` varchar(255) default '',
  `HomeCity` varchar(255) default '',
  `HomeState` varchar(255) default '',
  `HomePostalCode` varchar(255) default '',
  `HomeCountry` varchar(255) default '',
  `OtherStreet` varchar(255) default '',
  `OtherStreet2` varchar(255) default '',
  `OtherStreet3` varchar(255) default '',
  `OtherCity` varchar(255) default '',
  `OtherState` varchar(255) default '',
  `OtherPostalCode` varchar(255) default '',
  `OtherCountry` varchar(255) default '',
  `AssistantsPhone` varchar(255) default '',
  `BusinessFax` varchar(255) default '',
  `BusinessPhone` varchar(255) default '',
  `BusinessPhone2` varchar(255) default '',
  `Callback` varchar(255) default '',
  `CarPhone` varchar(255) default '',
  `CompanyMainPhone` varchar(255) default '',
  `HomeFax` varchar(255) default '',
  `HomePhone` varchar(255) default '',
  `HomePhone2` varchar(255) default '',
  `ISDN` varchar(255) default '',
  `MobilePhone` varchar(255) default '',
  `OtherFax` varchar(255) default '',
  `OtherPhone` varchar(255) default '',
  `Pager` varchar(255) default '',
  `PrimaryPhone` varchar(255) default '',
  `RadioPhone` varchar(255) default '',
  `TTYTDDPhone` varchar(255) default '',
  `Telex` varchar(255) default '',
  `Account` varchar(255) default '',
  `Anniversary` varchar(255) default '',
  `AssistantsName` varchar(255) default '',
  `BillingInformation` varchar(255) default '',
  `Birthday` varchar(255) default '',
  `Categories` varchar(255) default '',
  `Children` varchar(255) default '',
  `DirectoryServer` varchar(255) default '',
  `EmailAddress` varchar(255) default '',
  `EmailDisplayName` varchar(255) default '',
  `Email2Address` varchar(255) default '',
  `Email2DisplayName` varchar(255) default '',
  `Email3Address` varchar(255) default '',
  `Email3DisplayName` varchar(255) default '',
  `Gender` varchar(255) default '',
  `GovernmentIDNumber` varchar(255) default '',
  `Hobby` varchar(255) default '',
  `Initials` varchar(255) default '',
  `InternetFreeBusy` varchar(255) default '',
  `Keywords` varchar(255) default '',
  `Language1` varchar(255) default '',
  `Location` varchar(255) default '',
  `ManagersName` varchar(255) default '',
  `Mileage` varchar(255) default '',
  `Notes` longtext,
  `OfficeLocation` varchar(255) default '',
  `OrganizationalIDNumber` varchar(255) default '',
  `POBox` varchar(255) default '',
  `Priority` varchar(255) default '',
  `Private` tinyint(1) unsigned NOT NULL default '0',
  `Profession` varchar(255) default '',
  `ReferredBy` varchar(255) default '',
  `Sensitivity` varchar(255) default '',
  `Spouse` varchar(255) default '',
  `User1` varchar(255) default '',
  `User2` varchar(255) default '',
  `User3` varchar(255) default '',
  `User4` varchar(255) default '',
  `WebPage` varchar(255) default '',
  `RecordOwnerID` mediumint(6) unsigned NOT NULL default '0',
  `LastUpdatedByID` mediumint(6) unsigned NOT NULL default '0',
  `DateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `PublicRecord` tinyint(1) unsigned NOT NULL default '0',
  `DateModified` timestamp(14) NULL,
  PRIMARY KEY (`CID`),
  KEY `Company` (`Company`),
  KEY `LastName` (`LastName`)
) TYPE=MyISAM;

Extract MySQL schema from an existing table using PHP

function mysql_tb_schema($tb) {
   if(!$res = mysql_query("SHOW CREATE TABLE `$tb`")){
      return @mysql_error();
   }
   if($schema = mysql_result($res, 0, 1)){
      return $schema.';';
   }
   return 'Table description not found';
};

echo '<pre>'.mysql_tb_schema($tb).'</pre>';

Form Generator / Code Generator Resources

• PHP Form Generator for phpMyEdit
• php-form-generator.com home
• phpMyEdit.org

Lost and Found

The human brain is really powerful, but there are lots of times when one simply can't remember where to locate a particular PHP script, MySQL™ database schema, or other piece of code. I began dumping certain code clips into a searchable database for easy retrieval in the future. You might consider doing the same thing.

--
-- Table structure for table `code_library`
--

CREATE TABLE `code_library` (
  `id` mediumint(6) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL default '',
  `purpose` varchar(255) NOT NULL default '',
  `clip` text NOT NULL,
  `date_entered` date NOT NULL default '0000-00-00',
  `time_entered` time NOT NULL default '00:00:00',
  `PRIMARY KEY (`id`),
  `UNIQUE KEY `name_2` (`name`)
) TYPE=MyISAM;

MySQL Cheat Sheet