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.
Wednesday, December 29, 2010
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:
This will print:
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);
$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
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:
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:
So heres my solution if you have only_full_group_by mode turned on:
OR
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().
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 GROUP BY Name HAVING Death IN (Recent)
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. :DSo 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:
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.
(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:
This will replace all instance of a collon (:) to a comma(,).
Now you can select only people who visited Africa.
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.
Subscribe to:
Posts (Atom)