mysql: import csv data

by prettyscripts on 2011-12-16 10:47:00 • Leave a comment »

sql

there are a few ways to to it. here's a solution using load data. load data local infile '/path/to/csvfile' fields terminated by ',' enclosed by '"' lines terminated by '\n' [ignore 1 lines] [(column_1, @dummy, column_2,...)] notes line 5: t… more »

Tags: csv, data, import, mysql, sql

sql: running total

by prettyscripts on 2011-10-28 10:35:00 • Leave a comment »

sql

to calculate running total / balance in a single sql statment: select a.id, a.quantity, (select sum(b.quantity) from stock b where b.id <= a.id) as running_total from stock a ; more »

Tags: balance, running, sql, sum, total

wordpress: change url

by prettyscripts on 2011-09-12 10:44:00 • 1 comment »

phpsqlwordpress

when deploying a site (and database) from development to production, when going to the production the site goes back to development. that's because in wordpress, site url is defined and saved in database by default. according to the official document,… more »

Tags: mysql, php, sql, wordpress, wp

sql: order by ascending order with null at the end

by prettyscripts on 2011-07-25 11:38:00 • 1 comment »

sql

when using 'order by' clause to sort columns in ascending order, rows with null value comes first. what if the requirement is to sort in ascending order but rows with null values comes last? use case. select * from table order by case when column… more »

Tags: mysql, null, order, sort, sql

yii: accessing value in select as column

by prettyscripts on 2010-11-16 11:16:06 • 2 comments »

phpsqlyii

when selecting a column as alias name, a variable (property) of the same name must be manually defined in the model. eg, if the sql is:select column_1 as alias_a from table_1 in the model file:class MyModel as CActiveRecord { public $alias… more »

Tags: alias, framework, model, select, sql, yii