mysql

mysqldump -p --no-data  --routines --triggers test allt > allt-data.sql

export.sh

fields_in="email"
fields_out="@usr"
file="/tmp/ca.csv"
table="table"
 
echo "exporting $fields_in from $table"
mysql -u in -N << eof
  select $fields_in
  from $table                                                                                                                                
    into outfile '$file'                                                                                                                         
    fields enclosed by '"' terminated by ';' escaped by '"'
    lines terminated by '\r\n';
eof
[[ $? != 1 ]] || exit
 
echo "importing $fields_out from $file"
mysql -u out << eof
  load data infile '$file' ignore into table usrs 
    fields enclosed by '"' terminated by ';' escaped by '"'
    lines terminated by '\r\n'
    ( $fields_out )
    set id = @id,
      usr = @usr;
eof
[[ $? != 1 ]] || exit

-- check charsets
SELECT * FROM information_schema.SCHEMATA S;

SELECT 
    CCSA.character_set_name,CCSA.collation_name,T.table_name 
  FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA 
  WHERE CCSA.collation_name = T.table_collation ;
 
SELECT table_schema, table_name, column_name, character_set_name 
  FROM information_schema.`COLUMNS` C;

== php

=== utterly complicated csv output
 
<?php                                                                
 
$db = new mysqli( '','root','xxx','a');          
$res = $db->query( 'select * from t1 order by ide' );                      
 
$db_p = new mysqli( '','root','xxx','b');                   
$fp = fopen('php://output', 'w');                                    
 
while( $row = $res->fetch_assoc( )) {                                
    if( $row['id'] == 0 ) { continue; }                           
    $res_p = $db_p->query( 'select * from t2 where id = '.$row['id'] );
    $row_p = $res_p->fetch_assoc( );                                 
    if( $row_p ) {                                                   
        fputcsv($fp, array(                                          
            $row['id )); }}                                                                    
fclose($fp); 

=== primitive searchfield parsing

public function search( $s ) {                                                                                                                   
 
        $q = array( ); $p = array( ); $a = array( );
 
        $s = str_replace( '*', '%', trim( $s ));
        if( !$s ) { return array( ); }
 
        foreach( explode( ' ', $s ) as $sv ) {
                if( strpos( $sv, '@' ) !== false ) {
                        $q[] = 'tix.e like ?';
                        $p[] = 's';
                        $a[] = "%$sv%";
                } else {
                    $qv = array( );
                    foreach( array( 'fn', 'ln' ) as $f ) {
                        $qv[] = "$f like ?";
                        $p[] = "s";
                        $a[] = "%$sv%";
                    }
                    $q[] = '('.implode( ' or ', $qv ).')';
                }
        }
        $q = 'select tix.*, usrs.name as crewname from tix, usrs 
                where usrs.uname=tix.parent and ('.implode( ' and ', $q ).')';
        $res = $this->sql->query( $q,$p,$a );
        return $res;
}