Blog2Works

This is the blog of freelance website designer and developer Ben Johnson, with thoughts on design, programming, frameworks, jquery and the latest technology.
Viewing entries tagged with 'mysql'
  • Common MySQL data types

    Author: Ben Johnson

    Tags: , ,

    When designing a new database, each time I have to re-think what types should be used for one or the other columns. So I decided to make my own mainly just for reference but somebody may find this useful. Column Data Type Comment id INT AUTO_INCREMENT, UNSIGNED page name VARCHAR(64)   meta title VARCHAR(150)   meta description VARCHAR(160)   meta keyword VARCHAR(255)   status enum('ENABLED','DISABLED') DEFAULT "DISABLED" featured TINYINT(1) DEFAULT "0" content TEXT username VARCHAR(32) password VARCHAR(64) email address VARCHAR(100) phone/mobile VARCHAR(20) firstname/company VARCHAR(32) address_1 VARCHAR(128) postcode VARCHAR(10) file VARCHAR(255)   price DECIMAL (7,2)  UNSIGNED order INT(11)   ip VARCHAR(15) DEFAULT "0" modified TIMESTAMP on update CURRENT_TIMESTAMP create DATETIME DEFAULT "0000-00-00 00:00:00" forien_key INT(11)  


  • Install PHP 5 + Apache 2 + MySQL 5 on Mac OS X via MacPorts

    Author: Ben Johnson

    Tags: , , , , , , , ,

    At Web2Works when developing websites on a local environment. This guide is specifically for Mac, we use MacPorts to make installing and upgrading packages straight forward via the command promt. On successfully completing the full install with the following: Apache 2.2.* Virtual Hosts PHP 5.3.* phpMyAdmin PHP Curl PHP Mcrypt MySQL 5.1.* Memcached Let get started If you haven’t already done so, make sure you turn off Apple’s "Personal Web Sharing" in the System Preferences so that the default Apache server is not running. Install MacPorts: First make sure you install Xcode, a Apple developer toolkit and library that MacPort uses. Follow the installation guide at http://www.macports.org/install.php by downloading the dmg from http://distfiles.macports.org/MacPorts/ (make sure you choose the correct OS X) followed by a simple bummies "Next" Install process. sudo port selfupdatesudo port upgrade outdated Install Apache 2 sudo port install apache2This may take a while installing the apache module and the dependencies. Time to make a coffee..... After install has completed ensure that apache has been added to the auto load: sudo port load apache2 To verify Apache is now running, point your browser to  http://localhost/. You should see a page that says "It works!" Virtual HostsUncomment Virtual Hosts in Apache settingsThis is an example of what my Virtual Host file looks like. With example NameVirtualHost *:80<VirtualHost *:80>    ServerAdmin webmaster@localhost    ServerName 127.0.0.1    ServerAlias localhost    DocumentRoot /Users/john/Sites    <Directory />        Options FollowSymLinks        AllowOverride None    </Directory>    <Directory /Users/john/Sites>        Options Indexes FollowSymLinks MultiViews        AllowOverride All        Order allow,deny        allow from all    </Directory>    ErrorLog /opt/local/apache2/logs/error.log    # Possible values include: debug, info, notice, warn, error, crit,    # alert, emerg.    LogLevel warn    CustomLog /opt/local/apache2/logs/access.log combined</VirtualHost><VirtualHost *:80>    ServerAdmin webmaster@web2works.co.uk    DocumentRoot "/Users/ben/sites/website_project"    ServerName website_project.local    ServerAlias www.website_project.local    ErrorLog "/opt/local/apache2/logs/website_project-error_log"    CustomLog "/opt/local/apache2/logs/website_project-access_log" common</VirtualHost> Install MySQL sudo port install mysql5-serverOnce this has install set up the database:sudo -u mysql mysql_install_db5 sudo chown -R mysql:mysql /opt/local/var/db/mysql5/ sudo chown -R mysql:mysql /opt/local/var/run/mysql5/ sudo chown -R mysql:mysql /opt/local/var/log/mysql5/Activate your MySQL server installation so that it autostarts when you boot your machine:sudo port load mysql5-serverYou can verify it is running by:ps -ax | grep mysqlSet the MySQL root password, you will be prompted for your existing password ("Enter password:"); since it’s empty, just press Return:mysqladmin5 -u root -p password <new-password> Test everything by logging in to the server.mysql5 -u root -pRun the interactiev program to secure the MySQL server./opt/local/bin/mysql_secure_installation5 Install PHPNext install the latest PHP and Apache sudo port install php5 +apache2 +pearsudo port install php5-mysqlRegister PHP with Apache:cd /opt/local/apache2/modulessudo /opt/local/apache2/bin/apxs -a -e -n "php5" libphp5.soUpdate Apache’s httpd.conf file to enhance the "DirectoryIndex" directive to include additional "index" files. Search for:DirectoryIndex index.php index.htmlAlso, at the end of the httpd.conf file, add the following lines so that Apache includes the mod_php "AddType" configurations# Include PHP configurationsInclude conf/extra/mod_php.confUncomment Virtual hostsInclude conf/extra/httpd-vhosts.confSet up your PHP configuration files:cd /opt/local/etc/php5sudo cp php.ini-development php.ini Configure PHP.ini set mysql.default_socket=/opt/local/var/run/mysql5/mysqld.sockmysqli.default_socket=/opt/local/var/run/mysql5/mysqld.sockpdo_mysql.default_socket=/opt/local/var/run/mysql5/mysqld.sockdate.timezone = Europe/Londondate.default_latitude = 53.826597date.default_longitude = -1.592889 Stopping and Starting ApacheAdding aliases to your shell environment will make it easier to start and stop Apache. Edit your .profile or .bash_profile: alias apache2ctl='sudo /opt/local/apache2/bin/apachectl'This will mean to restart apache you only have to type: apache2ctl restartRun the following to refresh the profile. . ~/.profileOr try:sudo /opt/local/etc/LaunchDaemons/org.macports.apache2/apache2.wrapper start Install phpMyAdmin sudo port install phpmyadminAdd the follow line to the apache htpd.conf file # Local access to phpmyadmin installationInclude conf/extra/httpd-phpmyadmin.confThen create this file in /opt/local/apache2/conf/extra/httpd-phpmyadmin.conf containing this text:AliasMatch ^/phpmyadmin(?:/)?(/.*)?$ "/opt/local/www/phpmyadmin$1"<Directory "/opt/local/www/phpmyadmin">Options -IndexesAllowOverride NoneOrder allow,denyAllow from allLanguagePriority en de es fr ja ko pt-br ru ForceLanguagePriority Prefer Fallback</Directory>Finally, you need to set up the phpMyAdmin configuration to access mySQL. cd /opt/local/www/phpmyadmin/sudo cp config.sample.inc.php config.inc.php$cfg['Servers'][$i]['auth_type'] = 'config'; // Authentication method (config, http or cookie based)?$cfg['Servers'][$i]['user'] = 'root'; // MySQL user$cfg['Servers'][$i]['password'] = ''; // MySQL password (only needed with 'config' auth_type) And that is it one final Restart Apache so that your changes take effect,  a painless server install/upgrade.  


  • SQL INSERT, UPDATE and DELETE queries Zend_Db

    Author: Ben Johnson

    Tags: , ,

    A list of common SQL comands using MYSQL and Zend Framework - Zend_Db   INSERT, UPDATE, DELETE statements with Zend_Db DML (Data Manipulation Language) statements are statements that change data values in database tables. There are 3 primary DML statements: INSERT – Inserting new rows into database tables. UPDATE – Updating existing rows in database tables . DELETE – Deleting existing rows from database tables. INSERT INSERT INTO user(email, password, firstName, lastName, active)VALUES ('$email', '$password', '$firstName', '$lastName', 1); The above SQL INSERT statement in Zend_Db as follow: $data = array( 'email' => $email,'password' => $password,'firstName' => $firstName,'lastName' => $lastName,'active' => '1');$db->insert('user', $data); UPDATE UPDATE userSET password = '$password',       firstName = '$firstName',       lastName = '$lastName',       accountUpdate = (accountUpdate +1)WHERE id = '$id' The above SQL UPDATE statemnet in Zend_Db as follow: $data = array('password' => $password,'firstName' => $firstName,'lastName' => $vlastname,'accountUpdate' => new Zend_Db_Expr('accountUpdate+1'));$db->update('user', $data, 'id = '.$id); DELETE DELETE FROM user WHERE id = '$id' The above SQL DELETE statemnet in Zend_Db as follow: $db->delete('user', 'id = '.$id);


  • SQL SELECT queries Zend_Db

    Author: Ben Johnson

    Tags: , ,

    Zend_Db and its related classes provide a simple SQL database interface for Zend Framework. To connect to MySql database using Pdo_Mysql adapter: $db = Zend_Db::factory('Pdo_Mysql', $dbConnect);    SELECT query – WHERE clause First one is simple, the second one use INNER JOIN keyword SELECT a.id, a.name, b.order_id  FROM users AS a, orders AS bWHERE a.id = b.user_idAND a.id = {$userId}    SELECT `a`.`id`, `a`.`name`, `b`.`order_id`FROM `users` AS `a` INNER JOIN `orders` AS `b` ON a.id = b.user_idWHERE (a.id = '{$userId}')  The above querys in Zend_Db style:   $select = $db->select()->from(array('a'=>'users'),array('a.id', 'a.name'))->join(array('b'=>'orders'), 'a.id = b.user_id', array('b.order_id'))->where('a.id = ?', $userId)  To select any column from the second table, the 3rd parameter of join() method should be an empty string SELECT a.id, a.nameFROM users AS a, orders AS bWHERE a.id = b.user_idAND a.id = {$userId}    $select = $db->select()->from(array('a'=>'users'),array('a.id', 'a.name'))->join(array('b'=>'orders'), 'a.id = b.user_id', '')->where('a.id = ?', $userId)    SELECT a.id, a.name, b.*FROM users AS a, orders AS bWHERE a.id = b.user_idAND a.id = {$user_id}    $select = $db->select()->from(array('a'=>'users'),array('a.id', 'a.name'))->join(array('b'=>'orders'), 'a.id = b.user_id')->where('a.id = ?', $userId)    SELECT query – WHERE IN clause SELECT id FROM users WHERE aff_id IN ('1','2','3')   $select = $db->select()->from('users', array('id'))->where('aff_id IN (?)', array(1,2,3)); Echo your query to visualize it: echo $select->__toString();exit;