Tag Archive for MySQL

Don’t use IsValid in your Doctrine Models – a warning

Thursday September 9th, 2010 PHP 2 comments

Recently for a project i’ve been making heavy use of Doctrine. In one of the models I had a method to check to see if the object was valid under a certain set of conditions. I named this function isValid .. and it had 2 params. Little did I know that this was actually overriding an existing deep rooted isValid which gets fired on saving the object to the DB. Bummer.

So this is a quick warning to all you Doctrine users. Unless you want to actually override the isValid method for saving, call your check method function something else!

MySQL – Conditional Joins

Saturday March 7th, 2009 MySQL 5 comments

For the most part I’ve used many JOINs in my queries since I leartn how to use them and what they were good for, I much prefer to use an Inner Join over the the Implied join of a WHERE statement.

For those who haven’t yet learnt about JOINs you might be using an implied join in your queries even now… lets take this example:

SELECT table1.field1,table2.field2
FROM table1,table2
WHERE table1.table2_id = table2.IDField

This is an Implied JOIN, you are telling MySQL to get the 2 fields from the 2 tables but only return the ones where the field in table1 is in the field in table2. This works, but using Joins we can improve it.

Ok, Lets take the above example and apply the join mentality to it:

SELECT table1.field1, table2.field2
FROM table1
INNER JOIN table2 on table1.table2_id = table2.IDField

This Links in only the rows that match the ON criteria which in theory should be less than the whole table, which again in theory should make the query faster.

Ok, now we have the JOIN theory out the way, we can get down to what this blog post is actually really all about, Conditional joins. Just Like the WHERE Clause in the SELECT statement you can use AND & OR in the ON part of a JOIN. Usually it’s used to add more constraints to the join, but you can use it in interesting ways,

SELECT table1.field1, table2.field2
FROM table1
INNER JOIN table2 on (table1.table2_id = table2.IDField) AND (table1.itemName = 'foo')

Only JOIN rows that have the matching ID and where the itemName from table1 is equal to foo;

SELECT table1.field1, table2.field2
FROM table1
INNER JOIN table2 on ((table1.table2_id = table2.IDField) AND (table1.itemName = 'foo')) OR (table2.item = 'xx')

Only JOIN rows when the IDs are matching and the itemname is foo, OR when the item in table2 is ‘xx’

Baring in mind that the more complicated you make it, the harder it will be to maintain IT still can be a great method of Reducing the amount of rows that need to be processed.

Hopefully this will help someone, if so or if you have anything to add/point out/criticise etc.. please do comment.. I Don’t Bite.

MySQL – Conditional Updates

Friday March 6th, 2009 MySQL No comments

Just a quick blog post in an update related way,

Recently i’ve needed to toggle multiple rows in a database from 1 value to 1 of possible 2 values dependant on the contents of a field on the row. I wasn’t sure if this was possible, so i asked in #PHPWomen IRC Channel and the general response was that of “Don’t Know”.

So After a backup of the table in question, i decided to try it and see. After all, the worst it could do was just not work. So I tried the following:

UPDATE `tble_name` SET `field1`=IF(`field2`='arbitrary_value',1,0) WHERE `field3`='other_value'

And Voila updated all the rows exactly as needed.

So, It is possible to update all rows in a table, limited to a group and update value based upon a field matching a certain Value.
So all in all MySQL For the WIN!!!