Ok - I had to install MySQL on my Mac for the first time - using a tar of the binaries none the less. And it has been interesting. These are notes mostly for myself but if others find them helpful - great.

1. instructions I used - http://dev.mysql.com/doc/refman/5.0/en/installing-binary.html
2. to create a group in 10.5 - use dscl. In my case ‘dscl . -create /groups/mysql’
3. to create a user in 10.5 - again use dscl. ‘dscl .-create /Users/mysql’
4. I had to make /usr/local with mkdir -m 775
5. I did not have to decompress things since the Mac did it for me - but I did have to place it in my Library dir and make the ln to it in my /usr/local dir
6. DO NOT FORGET to give your root user a password and either drop the anonymous user or put a password on it!

Other notables (yes I am a server noob):

• start the deamon with ’sudo bin/mysqld_safe –user=mysql &’
• If you forget the ampersand at the end - use cntl+z to escape then use ‘bg’ to put the deamon in the background
• stop the deamon with ‘bin/mysqladmin -u root shutdown’

Other notes to self on having multiple versions available:

1. option 1 - have multiple binary installs in their own folders and just change the ln.. or have multiple ln with individual names. since I do not need to have all of them running at one time - I can start and stop the deamons as I need.
2. option 2 - is provided in the manual at http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html

Yep - I have finally ready for my trip to the MySQL User Conference in Santa Clara. MySQL and Oreilly have been incredibly generous in providing us the opportunity to attend their conference, giving us an exhibition booth and a free pass to all the sessions. With out them - we couldn’t have gone.

Anyway - for those that will be there, make sure to stop by our booth and pick yourself up a PHPWomen sticker (for your laptop, car, bag or anywhere else you can think of). Or if you just want to talk about what we are and what we are doing or the weather - that is great too. Come out and meet me!

I am pleased and proud to say that come Monday - I will be working for MySQL (*sigh* - Ok Sun). I will be working for a Dream Company of mine - doing a Dream Job. Life does not get better then that.

So to everyone who kept telling me I would get the job when I swore there was no way (you know who you are) - feel free to tell me “I told you so” next time we talk - and first beer is on me when we meet.

It has been a really long time since I wrote anything - luckily the folks at phparchitect gave me just the push I needed to get back to my writing. Took me a while to get back in the hang of it again - but I did it - and even finished by my deadline (even if it was turned in a day late).

So if you feel the need to learn a bit more about foreign keys and what they can do for you the coder - be sure to check out my article on them at c7y

Just found out about the Orlando BarCamp from another member of the JaxPHP UG. I had no idea there was a BarCamp out this way.

It will be held on April 5 and 6 at Wall St Plaza. The 5th will be dedicated to Developers and the 6th to various media (film, photo, etc.). For more information on it be sure to check out their site - http://www.barcamporlando.org/ Registration is now open for any who want to go.

Unfortunately I will not be able to attend - niece will be participating in a gymnastics state championship that weekend in Tampa. Family comes before playing.

While talking with Dynom and Davey I learned something really cool today - how to get the declared definition of a column all by itself with MySQL (I used version 5.0.45). Yes there are lots of other ways to get the info - not the least of which is using the information_schema - but this just struck me as being way cool.

You can even use wildcards in the column name. From a different database (note you have to have the perms to access the alternate database):


DESCRIBE Db.table column;


Example from my test db:

mysql> describe lig_test.student guardianship;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| guardianship | char(1) | NO   |     |         |       |
+--------------+---------+------+-----+---------+-------+
1 row in set (0.08 sec)

mysql> describe lig_test.student 'guard%';
+----------------+----------+------+-----+---------+-------+
| Field          | Type     | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| guardian_photo | longblob | YES  |     | NULL    |       |
| guardianship   | char(1)  | NO   |     |         |       |
+----------------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Everyone has a dream job or jobs. So out of all the companies in the world, if you could choose from all of them for who you would want to work for - which company or companies would it be? Big, small - well known or not, what companies would be the top 3 that you would love to work at and if you feel bold - Why?

For me: OmniTI, Yahoo! and MySQL - though not necessarily in that order. As for why - so many reasons. But primarily because of what I could learn while working at them. So much to learn - so little time to pick so very many brilliant minds.

I was asked to post this after explaining it to someone on IRC.

If you have tried to understand how joins work and constantly get confused about what join to use, you just need to keep a simple picture in mind ( I like pictures). I will be explaining joins by referencing a Venn diagram. Have no fear - I won’t get into any of the set theory/math involved. This is just a basic overview to give you an idea the data a particular join will return to you. This is not a technical discussion - just concepts.

We will start with just an empty diagram:

The T1 circle represents all the records in table 1. The T2 circle represents all the records in table 2. Notice how there is a bit of overlap of the 2 circles in the middle. Simple right?

I will use red to signify the records that will be returned by a particular join.

INNER JOIN
An inner join only returns those records that have “matches” in both tables. So for every record returned in T1 - you will also get the record linked by the foreign key in T2. In programming logic - think in terms of AND.

OUTER JOIN
An outer join is the inverse of the inner join. It only returns those records not in T1 and T2. “Give me the records that DON’T have a match.” In programming logic - think in terms of NOT AND.

LEFT JOIN
A left join returns all the records in the “left” table (T1) whether they have a match in the right table or not.

If, however, they do have a match in the right table - give me the “matching” data from the right table as well. If not - fill in the holes with null.

It should be noted that the same thing is possible with a right join - most people just use a left one.

LEFT OUTER JOIN
A left outer join combines the ideas behind a left join and an outer join. Basically - if you use a left outer join you will get the records in the left table that DO NOT have a match in the right table.

Again it is noted that the same thing is possible with a right outer join - most people just use a left one.

Theta JOIN
A theta join is the Cartesian product of the 2 tables and not normally what people are looking for - but what they sometimes get by mistake. How many of us have written a join similar to this only to get way more then we were ever expecting.

SELECT t1.*, t2.*
FROM table1 t1, table2 t2
WHERE t1.id = 5;


So there you have the basic concepts of joins. Next time you need to use a join and have no clue what to do to get the data you need from the database, draw a picture. It may help you figure out what join to use. Least it helps me.

Important:Please be sure to read the comments below. There are definite situations where my simplistic drawings are invalid and brought to light.

Disclaimer: I am by no means a database guru. Please be sure to learn more about joins from the many online articles available.

I have to admit - I am not one of the great PHP coders. Just a normal Joe (or in my case Jane) here. But I wanted to look back on the year to see what I have learned and done as well as how much the year has changed me.

Well I started 2006 with an earthquake on the January 4th. Talk about shaking up the new year. The only really interesting thing I posted about was a simple question - Who owns your code? Well and a plug for the #phpc channel on freenode.

February had me reading “Objects, Patterns and Practice” (which I still haven’t finished), learning about the phpDoc plugin for Vim (I love that thing), changing hosting services (had to do some modifications on the site to get it working on the new one), and having to cancel my trip to the Philippines to see my dad while he is out here.

March had the first release of the Zend Framework - which I promptly played with for a while but didn’t come back to until the end of the year, and my taking and passing the MySQL4 Core Certification test.

April saw my good friend Davey start of the PHP thinktank channel on freenode (yep - another channel I normally am on), the start of the call for the Google Summer of Code, and my Dad flying out from the Philippines to visit me.

May was quiet since my dad was visiting. I was writing a few basic phpt tests for the QA team at the time and made a couple of notes to myself.

June saw me upgrade the blog and bring it to it’s current implementation, my daughter come out from California and get diving certified, me becoming a codewalkers admin, an apress technical editor, and get Part I of my MySQL Stored Procedures article accepted for phparchitect’s A/R/T.

July was when I almost killed myself out of good old fashioned stupidity. That was fun and I still have the scars. Anyway - it was also the month when we took our Australia trip which was fabulous. Have the pictures to prove it.

August had Part II of the MySQL Stored Procedures article go up on A/R/T, my posting of 25 ways to help PHP and the guys on Pro::PHP podcasting mentioning me because of it, the first glimmers of the PHPCommunity site being moved to ning (which it later did do), my daughter flying back to the US to live with her biological father, and my being hired for a new job.

September saw me start my current job, remember Sept 1t1h and cry again, and do a blog post on MySQL user defined functions. So it was a pretty quiet month.

October had me turn a year older, make the call for PHP women to stand up and be counted (which in turn lead to the start of the PHPWomen.org site). I also managed to take and pass the PHP4 Zend certification test.

November saw the beginning of the holiday craziness, my mother-in-law coming out for a visit, and my husband reenlisting for the last time (he will be retiring from the military after this). Basically anything PHP related was put on a back burner.

And finally, December had the book I was tech editing being published, my being asked to help write a book with a friend, and though I haven’t posted on it a return for me to working with the Zend Framework.

All in all it has been an incredible year for me with so many opportunities and lots fun along the way. So what are my goals for this next year?

1. finish the book I am helping write and see it published
2. get PHP5 ZCE
3. get MySQL5 developer certified
4. learn Javascript and DOM
5. either go back to the little bit of C# I know and become more comfortable in it - or try a new lang. Not sure which
6. Attend a PHP conference

Cross your fingers so hopefully I get to do them all next year.

Was just cruising around on the Yahoo! job listings for the farts and giggles of it when I came across this job announcement. What caught my eye was the job title - MySQL Geek

I would apply for it if I was even remotely qualified just to have that on my desk and business card!

Oh and FYI - there where pages and pages of jobs when you searched using PHP as the keyword.

MySQL User Defined Functions

Did you know you could make custom functions for use in your SQL queries? You could make an entire library of custom functions to work and manipulate your data on your database making them application and language independant. A few possible functions you could make -

• Calculate an Employee’s Accumulated Vacation Time - maybe pass in the Employee ID and return the number of Hours they have in vacation time available.
• Customer Address - Pass in the Customer ID or name and return their mailing address in a standardized format.
• Account Balance - Pass in the Account number and return the Current Balance (assuming you have a table of debits and credits)

So now you would not have to worry about doing the calculations in the application - you can leave it to the database. Pretty Cool.

MySQL’s user defined function syntax is very similar to stored procedures and kinda self explainatory. Below is a simple example.

Example

mysql> CREATE FUNCTION isOdd(value INT) RETURNS TINYINT
    -> RETURN value%2;
Query OK, 0 rows affected (0.19 sec)

This is example of a user defined function simply tells us TRUE(1) or FALSE(0) if a number we pass to it is odd.

Create

mysql> CREATE FUNCTION isOdd(value INT) RETURNS TINYINT

This line states that we are going to create a user defined function by the name of isOdd. The function isOdd will take in an integer that we will call value, and return out a tinyint. Makes sense when we say it like that, but can you point to all the parts? As we said - it is similar to a stored procedure that you feel like you should know it - but syntatically different enough that you can trip and fall a couple of timesif you don’t pay attention.

Return

    -> RETURN value%2;
Query OK, 0 rows affected (0.19 sec)

Here is where we actually calculate what to return and then return that value, just like in a PHP user defined function.

Usage

mysql> select isOdd(13);
+-----------+
| isOdd(13) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select isOdd(14);
+-----------+
| isOdd(14) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

Here we demonstrate how we can now use the newly created user defined function in a standard SQL statement. These SQL statements could have been sent from the developers application just like any other MySQL function. Again we made sure to do some basic testing of the function.

Right about now I guess I should note that this is a very simple example and that all the logical controls and looping functions that are available to stored procedures do work in stored user defined functions as well. You can make the functions as simple or complex as you need.

So there you have it. Now go - and create your own functions to use in MySQL.

MySQL: Storing Passwords in MySQL

An interesting write up on a MySQL’s encryption possibilities. I have to admit it - been using PASSWORD() to encrypt my passwords and found out I shouldn’t be. Guess I need to stop that and try out the other possibilities presented here.

He goes over SHA1, MD5, AES and briefly, at the end, touches on DES.

Found this article through planet MySQL. In it he goes over 4 different “types” of abstraction layers that typically are collectively called a “database abstraction layer” - though they are very different from one another. I found it an interesting read.

His 4 types:

1. A software library to connect to a database server and issue queries, fetch results etc.
2. A software library to present a common API to different database servers.
3. A software library to automatically generate portable SQL queries.
4. A software library to map Object-Oriented Programming to a relational database (Object-Relational Mapping, or ORM)

Well I’ll be danged. Didn’t expect the second part of my “Stored Procedure Programming for MySQL” to be put up so quickly on the PHP Architect A/R/T site.

Anyway - now that it is up… do me a favor and read it. Feedback, Criticism and Suggests are always welcome. Can’t improve otherwise.

Well my article for the php|a A/R/T has finally been posted. You can check it out here. Be sure though to come back and tell me what you think of it. Feedback will really help for the second part of it.

Been doing a little research to try and figure out when it is appropriate to use stored procedures in an application. Most of the answers seem to be “It depends”. My question is - “What does it depend on and how much for each?”

Does it depend on the bandwidth limitations, the complexity of the sequences of the SQL statements, the abstraction away from the application/database, the number of languages used to connect to the database, or complexity of the entire system that interacts with the database…

The only situation I can immediately see is if you have a large complex system built from multiple parts in multiple languages and you want a standard API for the data layer. Then I can see all your database interactions being done with stored procedures… but that to me is an extreme case.

Now I realize there are no easy answers to this question - but if anyone would happen to know of any case studies or links on the decision making process involved, I would appreciate it.

Well I did it - I passed the MySQL Core Exam (at least that is what the paper that they gave me at the testing center says). This is going to sound horrible but - it was both harder and easier then I thought it would be. They asked about things I never heard about, things I did hear about but didn’t remember the details, and luckily stuff I did know (thank goodness I had the study guide and used it). The stuff I thought would be on the exam - wasn’t - so the studying saved my butt.

Well thank goodness that is over… Though I still want to go for my Zend cert sometime around the end of the year. But that is a long way away yet. For now - I just want to sit down and read a good book that has nothing to do with computers.

Well tomarrow I take the MySQL Core Exam for 4.x whether or not I’m ready for it. I am definately nervous and it will not help that I am also sick, but such is life. At least i have a valid reason to be sweating while I take the exam… I’m sick - remember .

Wish me luck.

Well I have taken the plunge and scheduled my test for the MySQL Core Certification. It is on March 22nd at 10:45… and I am so nervous. The company is reimbusing me for the test - though truthfully I would have paid for it myself. It’s only $200 USD.

Cross your fingers and wish me luck - I really hope I pass.

I have just found a pair of really good Database tutorials. They don’t teach SQL - what they teach is Database design (Data Modeling and Relational Modeling)… ERD’s, referential and entity integrity, normalization. All the good stuff. Now I haven’t read both of them in their entirty… skimmed over parts that I already knew, but read parts that I haven’t used/remembered. There were a few parts that I was taught using slightly different terminology… but I quickly realized/recognised them.

I had completely forgotten about super and subtype heirachies, domains, and the term tuple. The INSERT and DELETE integrety rules was a very clear and concise refresher, and the review of data manipulations using set theory definately made me sit up and get the brain thinking (need to go back again and reread it)… I also found a few things I had never heard of - 4th and 5th normal forms for example.

Definately need to remember this site - I’m sure others will want to look it over and learn… and I’m sure I will point others there to learn a few things.