If you’re getting the following error in phpmyadmin when creating a new database:

#1006 – Can’t create database

The problem is that /var/lib/mysql is probably owned by root, and ALSO in the root group. It should not be.

ls -ld /var/lib/mysql probably looks like this:

drwxrwxr-x 4 root root 4096 2010-11-01 23:45 /var/lib/mysql

It should be:

drwxrwxr-x 4 root mysql 4096 2010-11-01 23:45 /var/lib/mysql

So, run

chgrp -R mysql /var/lib/mysql

and you should be able to create databases now.

Quick one – steps to password protect your phpmyadmin on Debian.

Good idea also to use a symlink to obfuscate the ‘phpmyadmin’ url too.

Eg. within the docroot of a site I wish to install phpmyadmin on, I would do:

cd /var/www/site

ln -s SECRET_PATH_TO_DB /usr/share/phpmyadmin/

Your phpmyadmin install is then available on site.com/SECRET_PATH_TO_DB

For the htaccess password protection, this is a very handy generator tool. The username and password you choose for the .htpasswd protection should be saved to /usr/share/phpmyadmin/.htpasswd (swap /usr/share for whatever install path you have).

If you ever come across the issue where your ‘root’ user cannot login to mysql on debian, here’s a neat solution that will save you having to re-install mysql.

mysql -u root

ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)

Then you try:

mysql -u root -pxxxxx

ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: YES)

I don’t know how this happened, but on Debian what you can do is:

Go to your /etc/mysql/debian.cnf file, and copy the password for the debian-sys-maint mysql user.

Typically will look like:

host     = localhost
user     = debian-sys-maint
password = 123456789

Login to mysql as debian-sys-maint:

mysql -u debian-sys-maint -p123456789

Then, switch to mysql database,

use mysql;

Now check your root user is still there, on localhost. If so, just set a new password, i.e.

SET PASSWORD FOR ‘root’@'localhost’ = PASSWORD(‘password-value’);

Note, it will not work if you just do

SET PASSWORD FOR ‘root’ = PASSWORD(‘password-value’);

That’s it, exit, restart mysql and you should be able to log with root again!

Of course if root user doesn’t exist anymore in the user table on mysql, just recreate and ensure you grant all privileges.

One final thing – if you use phpmyadmin, you will need to change the password for root in /etc/phpmyadmin/config.inc.php to match your new one.

For local development I use Xampp, which I find to be very handy. It’s really simple to and quick to setup, but there were just 2 gripes I had when setting it up that took an hour or so to fix – very annoying, so just recording them here for reference.

Firstly, the install is as easy as detailed on the official page, and the best guide I found was this excellent one.

There are a tonne of files in your /Applications/XAMPP folder, but the most usual thing is to setup a Vhost. Edit the /Applications/XAMPP/etc/extra/httpd-vhosts.conf file for that.

BUT, the first catch is you need to ensure that the following line is uncommented in the /Applications/XAMPP/etc/httpd.conf file:

Include etc/extra/httpd-xampp.conf

Gaaahhh!!!

Second gripe, your htaccess or mod_rewrite won’t work probably, so you need to make sure that AllowOverride None is changed to AllowOverride All in /Applications/XAMPP/etc/httpd.conf, e.g.

<Directory />
Options FollowSymLinks
AllowOverride All

Apart from those two minor annoyances, it was plain sailing!

When using Zend Form, and you have a password field and confirm password field, it is very handy to use Validation Context. It is described in the Zend documentation, just search for “My_Validate_PasswordConfirmation”. As is typical in Zend docs, the example is shit, they just give a class definition. So, here’s a brief example of I used it.

Basically what you need to do is attach the validator to both the password and the confirm password field. 

So firstly, add the class:

class My_Validate_PasswordConfirmation extends Zend_Validate_Abstract
{
const NOT_MATCH = 'notMatch';

protected $_messageTemplates = array(
self::NOT_MATCH => 'Password confirmation does not match'
);

public function isValid($value, $context = null)
{
$value = (string) $value;
$this->_setValue($value);

if (is_array($context)) {
if (isset($context['password_confirm'])
&& ($value == $context['password_confirm']))
{
return true;
}
} elseif (is_string($context) && ($value == $context)) {
return true;
}

$this->_error(self::NOT_MATCH);
return false;
}
}

Then, in your form file, use it something like follows:

class RegisterForm extends Zend_Dojo_Form

{

public function init()

{
$form = new Zend_Form();
....
....
....
$custom_pass = new MyValidatePasswordConfirmation();

$password_confirm = $form->createElement('password', 'password_confirm')
->addValidator('StringLength', false, array(1,24))
->setLabel('Confirm your password:')
->addValidator('Alnum')
->addValidator($custom_pass)
->setRequired(true);


It’s fairly straightforward, but of course there’s very few examples I could find, this is probably the best.

Will fill in more info later, but here’s the basics so far: got this amazing free sms Gateway, playSMS. Great install instructions, such as:

playSMS Web Interface:

1.  It is important to meet all minimum requiments above

2.  Setup a system user named ‘playsms’ to manage playSMS

    # adduser playsms

    # passwd playsms

    Note: on some Linux distributions adduser and passwd combined (Debian, Ubuntu and maybe others)

3.  On most Linux distributions actions (2) will create system user and group named ‘playsms’

    with home directory /home/playsms, but you will install playSMS in different directory

4.  Create playSMS web root, spool and log and set ownership to user www-data or web server user

    # mkdir -p /var/www/playsms

    # mkdir -p /var/spool/playsms

    # mkdir -p /var/log/playsms

    # chown -R www-data /var/www/playsms

    # chown -R www-data /var/spool/playsms

    # chown -R www-data /var/log/playsms

5.  Extract playSMS package somewhere (Usually in /usr/local/src)

    # tar -zxvf playsms-x.x.x.tar.gz -C /usr/local/src

    Note: x.x.x may vary according to the package name you’ve download

6.  Copy files and directories inside ‘web’ directory to playSMS web root and set ownership again to 

    user www-data or apache web server user

    # cd /usr/local/src/playsms-x.x.x/web

    # cp -rR * /var/www/playsms

    # chown -R www-data /var/www/playsms

    Note: assumed your web server user is www-data

7.  Setup database (import database)

    # mysqladmin -u root -p create playsms

    # mysql -u root -p playsms < /usr/local/src/playsms-x.x.x/db/playsms.sql

    Note: you dont need to use MySQL root access nor this method to setup playSMS

    database, but this is beyond our scope, you should read MySQL manual’s for custom

    installation method or howto insert SQL statements into existing database

8.  Copy config-dist.php to config.php and edit config.php

    # cd /var/www/playsms

    # cp config-dist.php config.php

    # mcedit config.php

    or 

    # vi config.php

    Note: please read and fill all required fields with coutious

9.  Enter bin directory, copy playsms, playsmsd, playsmsd.php, playsmsd_start to directory default

    # cd /usr/local/src/playsms-x.x.x/bin

    # cp playsmsd playsmsd.php playsmsd_start /usr/local/bin/

    # cp playsms /etc/default/

    Note: please note the different between playsms and playsmsd

10. Look for rc.local on /etc and its subdirectories (usualy /etc, /etc/init.d or /etc/rc.d/init.d)

    Edit rc.local and put: 

    ”/usr/local/bin/playsmsd_start” (without quotes)

    on the bottom of the file (before exit if theres exit command). This way playsmsd_start 

    will start automatically on boot. 

    Note: you need ‘root’ access to do this

11  Browse http://localhost/playsms/ and login using default administrator user

    username: admin

    password: admin

12. At this point you should be able to login to playSMS web interface and manage playSMS

You can override your server’s timezone setting using php’s date_default_timezone_set. It is a really good idea, in my opinion, to use this at the start of your scripts that work with timestamps. Reason being, you can transfer scripts between servers and they will always work, which wouldn’t necessarily be the case if they depend on a servers timezone e.g if you are calculating a unixtime value in your script.

For example, I had an Ajax Calendar app that used Unixtime values to calculate which data to retrieve from a MySQL db. This worked fine on my dev server, whose server timezone was GMT, but when I transferred the code to my production server, which runs on E.T, it was not retrieving the correct data! This was a major pain to debug, as the code itself was fine of course, and was solved with a single line of code:

date_default_timezone_set(“GMT”);

Place this at the start of any script that has a dependency on the server timezone, and you can port your code to any server regardless of its timezone. If you don’t know your server’s timezone, date_default_timezone_get() will give it to you.

Say you are using MySQL datetime as the format for your dates, so when you output it you get 2009-03-21, which isn’t exactly user friendly nice to read. Say you want instead to have March 21st, 2009. There are of course two main ways to do this, convert the datetime field in php using mktime() to Unix Time, and then format with date() from there. Bit of a pain, and heavier processing then the second way, which is to use MySQL’s DATE_FORMAT. Check out that page, seriously – the list of date modifiers is very comprehensive, and listed here for your pleasure at the bottom of the page.

There’s a great forum post here, mentioning both methods – its well worth a look as the examples are simple and clear.

Quick example would be what we mentioned above, i.e. getting your date in the format of March 21st, 2009. Look how quick it is in MySQL compared to PHP:

SELECT DATE_format(olddate, ‘%M %D, %Y’) as newdate FROM table;

‘olddate’ is the name of your date field in datetime format, and ‘newdate’ is the new var representing the nicely and newly formatted date!

Let’s get the ball rolling wit this handy one about inserting csv or other delimited data to mysql dbs using INFILEs. This looks  like a basic php version too.

When getting the annoying error 1075 “Incorrect table definition; There can only be one auto column and it must be defined as a key” when trying to add a new primary key, this page has an awesome solution.

Using sprintf() to zerofill variables, handy like so:

$zeroball1 = $this->escape($result->ball1);
$zeroball2 = sprintf("%02d",$zeroball1);
echo $zeroball2;

Zend Framework and MySQL

For counting across rows, first check out the excellent official mysql page on row counting. Then to apply it to Zend framework, this is probably the best starting place for MySql select queries in Zend FW, and I check it regularly. Basically, to run a mysql query like SELECT `results`.`bonus`, COUNT(*) AS `counter` FROM `results` WHERE (draw = 0) GROUP BY `bonus`; you need the following in your model. Note the ‘counter’ => ‘COUNT(*)’ syntax…

$query = $db->select()  

->where("draw = $draw_val")

->from('results',array('bonus','counter' => 'COUNT(*)'))

->group('bonus');

For further and more detailed quality examples of Zend and MySql, this is good. Check out lars’ comment here on counting across multiple tables.