Wednesday, December 29, 2010

NOTES: Inserting MySql with 0 as primary key

This is just a reminder to avoid inserting (usually in a data dump) rows with 0 as an ID on an auto incremeted primary key. This will result on the inserted row's primary key to be incremented with the last inserted ID and wont insert it as a 0 primary key.

Took me awhile to figure out a why my data dumps are not populating correctly.

Tuesday, December 14, 2010

++i VS i++

Upon studying the basics of PHP to brush up my knowledge since I plan on taking the Zend Certification :D, I came accross wondering whats the difference between using $i++ and ++$i in our loops.

I have always used $i++ since its the traditional way of incrementing a loop but upon further research it seems like using ++$i is a tad bit faster. An explanation of this is because $i++ makes a "copy" of $i then increment then return whereas  ++$i increments $i then return it.

This bit of code will explain it a little clearer:

$i = 0;

$t .= "First instance: "  . ++$i  . PHP_EOL;
$t .= "Second instance: " . $i++  . PHP_EOL;
$t .= "Third instance: "  . $i    . PHP_EOL;

$i++;

$t .= "Fourth instance: " . $i    . PHP_EOL;
print nl2br($t);

This will print:

First instance: 1
Second instance: 1
Third instance: 2
Fourth instance: 3

As you can see, $i++ doesn't return the value right away; It makes a copy and return it at the end of the print execution where as ++$i returns the value of $i right away like what you can see in "First instance: 1"

Thursday, December 2, 2010

MySql GROUP BY queries in older MYsql version

I decided to document this little situation I had recently with an SQL statement since its been a couple of times I encountered this kind of problem.

This may not be the best example but here it is
Heres the table:

Table: Deceased
Id        |         Name          |          Death           |
1         |         Franz           |          1989            |
2         |         Franz           |          1700            |
3         |         Franz           |          2010            |
5         |         John             |          2006            |
6         |         John             |          1980            |


Requirement: We want to query the person in a group with the same name who died the most recent.
So if we were to query the table above we want to get the result:
row: Id: 3, Franz, 2010
row: Id: 5: John, 2006

So how would we do this? There maybe many solutions out there but this is the sql I thought of out of hand:
SELECT *, MAX(Death) as Recent  FROM Deceased
GROUP BY Name HAVING Death IN (Recent)
This SQL above may work if you have MYsql's ONLY_FULL_GROUP_BY mode disabled but will give you headaches if it is on or if you have an older version as of mysql (<= MySQL 5.0.23). Learned that the hard way. :D

So heres my solution if you have only_full_group_by mode turned on:
SELECT * FROM Deceased
GROUP BY Name, Death HAVING Death IN (SELECT MAX(Death)  FROM Deceased
GROUP BY Name)

OR

SELECT * FROM Deceased WHERE
Id IN (SELECT IF(MAX(Death), Id, null)  FROM Deceased
GROUP BY Name)

Yep a subquery and yep it may be slow. A little explanation on the last query. I used Id with a sub query because I realize that if I used the death column, there may be people who died on the same year that is not the result of a MAX therefore those years are not unique so we have to compare against the ID. One catch is a subquery needs to have 1 column so we have to use an IF().

Wednesday, December 1, 2010

MySQL's FIND_IN_SET()

I recently discovered a neat function in MySql called find_in_set(str, strlist). It basically takes an argument X as your string, and searches the inputed string list.

Say for example you have a column in your table named "country" and it stores the country a specific person visited to separated by a comma (,). Sample table below:

Table: Person
|   Name                   |    Country                            |
|   Michael Jabroni    |    USA, Brazil, Japan           |

To use find_in_set() now you can have a select that looks like this:
SELECT * FROM Person WHERE FIND_IN_SET('Brazil', Country)

Seemed useless right because why not use LIKE '%Brazil%' instead.
Say instead of having comma as a delimiter someone used a colon (:) instead for the database.

Table: Person
|   Name                   |    Country                                                       |
|   Michael Jabroni    |    USA:Japan:Africa:China:South Africa           |
|   John Maldoe        |    China:South Africa                                       |

(I know Africa is not a country,  but for the sake of example.)
Further more, if you want to select a person that ONLY visited Africa, you wont be able to do so because it will also select people that visited South Africa. In addtition, you cannot use find_in_set() because it requires a comma as its delimiter.

One solution you can do is used another MySql string function called REPLACE() with FIND_IN_SET().
A revised select below:
SELECT * FROM Person WHERE FIND_IN_SET( 'Africa', REPLACE(Country, ':', ',') )

This will replace all instance of a collon (:) to a comma(,).
Now you can select only people who visited Africa.