accessrichard.blogspot.com

I needed to generate some PHP table objects for a couple MySql tables so wrote up the query below real quick. I am sure there are better ways to do this through IDE's, tools, or PHP. There will also be some kinks that can be worked out such as turning database fields such as create_date into getCreateDate instead of getCreate_date but this did the trick for me. It will generate class members, getters and setters. SET @table = 'post'; SET @db = 'blog'; SELECT '<?php' UNION ALL SELECT CONCAT('class ', CONCAT( UCASE(SUBSTRING(@table,1,1)), SUBSTRING(@table,2)), '{') UNION ALL SELECT CONCAT('\tprotected $_', COLUMN_NAME, ';') FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = @table and table_schema = @db UNION ALL SELECT '\n' UNION ALL SELECT CONCAT('public function get', CONCAT(UCASE(SUBSTRING(COLUMN_...

As part of some analysis I was doing, I needed to take exactly 2 decimal places of a number. For some reason my query was not outputting data I had expected. I pinpointed this down to rounding when converting to a decimal of only 2 decimal places. For Example: select cast (10.554 as decimal (5,2)) --returns 10.55 select cast (10.555 as decimal (5,2)) --returns 10.56 To fix this issue all I needed to do was shift the decimal place to the right a couple digits, take the integer portion, and then shift the decimal place to the left a couple digits. select cast ( cast (10.555   * 100 as int ) / 100.0 as decimal (5,2)) --returns 10.55 As an alternative approach, we can use the modulus to extract the value after the decimal place we no longer need. select (10.555%.01) --returns .005 Then subtract the result from the original number: select cast ( 10.555 - (10.555%.01) as decimal (5,2)) --returns 10.55