Getting data out of a mysql database into a text file is pretty straight forward using mysqldump
mysqldump5 -u USERNAME -p DATABASE_NAME TABLE_NAMES > /PATH/TO/FILENAME
TABLE_NAMES are optional and need to separated by a space if you are exporting a couple.
A couple of useful additional flags you can add after table names
- –xml – converts sql output to xml which makes for an easier read
- –no-create-info – outputs just data and no schema data.
- –no-data – the opposite of above, which gives you just the schema
- –complete-insert – to get column names within your insert statements
- –skip-triggers –compact –no-create-info – for just insert statements
Filed under: development, database, export, mysql
I’ve been using LIKE for ages which is a useful way to search for part of a word or phrase in a db field.
$conditions = array( 'TABLE.field LIKE'=>'%'.$search_term.'%');
Now i didn’t realise that this wasn’t case sensitive meaning that ‘Ultra’ returned results but ‘ultra’ didn’t.
So to make it case insensitive you can do the following:
$conditions = array('UPPER(TABLE.field) LIKE'=>'%'.strtoupper($search_term).'%');
Not much different but now a useful(UPPER) addition to the sql arsenal.
Filed under: development, cakephp, mysql, postgres, sql
January 8, 2009 • 9:47 pm
a useful tip from andygale to convert sql from latin to utf-8
iconv -f ISO-8859-1 -t UTF-8 dump.sql > dump_utf8.sql
Filed under: development, mysql, sql