Mysql Remove Line Breaks

I've recently had line breaks added to some data i've imported. Here's how you remove line breaks at the beginning and end of a field in mysql;

update temp_table set fieldname=trim(both char(13) from fieldname)

The above is only if you are in windows. I believe you can also use the folowing for linux;

update temp_table set fieldname=trim(both '\n' from fieldname)

You should also be able to use reaplce usinng the char method, e.g.

update temp_table set fieldname=replace(fieldname, char(13), '-')

I've not tested it though - I hope this helps!

MySQL Monthly Report

Here is is brief howto on generating a Monthly MySQL report from an orders table.

This example assumes you have a table called orders a stored date field called added and a price field for each order - in this example the price is stored as a varchar so it is casted using a (0+price).

The fist column returned is the current month, the second is the total price for that month. Here is the MySQL SQL Monthly Report Query;


SELECT TIMESTAMPDIFF(MONTH, STR_TO_DATE(concat(month(added),'/1/',year(added)), '%m/%d/%Y'), STR_TO_DATE(concat(month(now()),'/',DAYOFMONTH(LAST_DAY(now())),'/',year(now())), '%m/%d/%Y')) as tmonth,
sum(0+total_price) as lprice
from orders
where valid=1 and total_price > 0
group by tmonth


There probably are quicker ways of doing this but it seems like the best solution for reporting on a single orders table. This example basically makes the date as a string using CONCAT and then compare it.

I hope this helps.