Justin Kelly various ramblings

Importing CSV files into MySQL from the Linux command line

Every so often I need to bulk import csv type files into MySQL and I forgot the process. Here are the steps for a simple csv into MySQL.

Assuming you have a csv file with contents like below (with no headings line)

234245,0bMHxBO76w5T23t
564565,0BzRf05nA0lrn2G
234233,0eHhkxexkLDkVRz
124235,0Ezp93248igtsYu
12314,0I1d96ujWqrqqU6

Ensure the mysql-client is installed and use the mysqlimport command. Refer below for a detailed example.

mysqlimport  --fields-terminated-by=, --verbose --local -u root -p mysql-database-name mysql-table-name.csv 

where

  • --fields-terminated-by is the delimited
  • --local indicates that the csv or file is on your local file system
  • -u is the MySQL user
  • -p prompts for the MySQL password
  • mysql-database-name replace this with the name of your database
  • mysql-table-name the name of the csv file needs to match the target MySQL table name, the extension doesn’t matter. This can also include the file path at the start ie. /var/www/session.csv

If your csv has a headings row (such as example below) you can added the option --ignore-lines=1 to skip that line

user_id, session_id
234245,0bMHxBO76w5T23t
564565,0BzRf05nA0lrn2G
234233,0eHhkxexkLDkVRz
124235,0Ezp93248igtsYu
12314,0I1d96ujWqrqqU6

If the import works you’ll see a similar output to below, the number inf Records should match the lines in the csv, just watch for the numbers in Skipped and Warning to ensure all rows were imported - these should be 0

Enter password: 
Connecting to localhost
Selecting database users
Loading data from LOCAL file: session.csv into session
users.session: Records: 58918  Deleted: 0  Skipped: 0  Warnings: 0
Disconnecting from localhost

MOGA Pro controller for Android

Uninspired by the state of mobile gaming on Android? Doesn’t have to be that way - Enter the Moga Pro controller. Gaming on Android is dominated by puzzle and endless runner games that you can play for a few minutes to kill time. Save for a few great games like VVVVVV gaming on a touch screen is in a totally different leagure to the tactile experience of PC or console gaming.


Continue reading

Moto G tempered glass screen protector review

After scratching and smashing a few screens on my last couple of phones I was determined to find a solution to save my new Motorola G from the same fate. In the last year a new breed of phone screen protectors has launched onto the market - These are tempered glass protectors, thin strong glass overlays that replace the cheap plastic protectors.


Continue reading

Motorola grip shell for Moto G review

Following on from my review of the Cruzerlite case for the Moto G the second case from MobileZap is the Motorola Grip Shell.


Continue reading

Cruzerlite case for Moto G review

After picking up a couple of Motorola Moto G phones on the weekend I set about looking for a couple of different covers for them so as to not destroy yet another phone. The good guys over at MozileZap set over a couple of cases to review. The first up is the Cruzerlite Bugdroid case.


Continue reading

Nginx domain setup script

After writing a number of scripts to auto create domains for Apache and Nginx on linux servers I’ve finally gotten round to cleaning it up and releasing by current script to handle auto creating domains for Nginx (my current preferred web server)

This creates the Nginx virtual host config file, sets up a sane folder structure for the domain, enables the domain in Nginx and reloads Nginx.

Hope this help other Nginx users to manage domains easier - let me know if you have any additions to the script

Note: script assumes using PHP via fastcgi and unix sockets.

Nginx auto domain setup script nginx_domain.sh

#!/bin/bash

# Info
# ---
# script can run with the domain as a command line input 
# `sudo ./nginx_domain.sh my_domain.com` or without and
# the script will prompt the user for input

#config
web_root='/usr/share/nginx/'
config_dir='/etc/nginx/'

if [ -z "$1" ]
then   

        #user input
        echo -e "Enter domain name:"
        read DOMAIN
        echo "Creating Nginx domain settings for: $DOMAIN"

        if [ -z "$DOMAIN" ]
        then   
                echo "Domain required"
                exit 1
        fi
fi

if [ -z "$DOMAIN" ]
then
        DOMAIN=$1
fi

(
cat <<EOF
server {
        listen   80; ## listen for ipv4; this line is default and implied
        #listen   [::]:80 default_server ipv6only=on; ## listen for ipv6

        root $web_root/$DOMAIN/public;
        index index.php  index.html index.htm;

        # Make site accessible from http://localhost/
        server_name $DOMAIN www.$DOMAIN;

        location / {try_files $uri $uri/ @forum;}

        location ~ \.php$ {
                try_files $uri =404;

                fastcgi_split_path_info ^(.+\.php)(/.+)$;
                #NOTE: You should have "cgi.fix_pathinfo = 0;" in php.ini

                fastcgi_pass unix:/var/run/php5-fpm.sock;
                fastcgi_index index.php;
                include fastcgi_params;
        }
        location ~ /\.ht {
                deny all;
        }
            access_log $web_root/$DOMAIN/log/access_log.txt;
            error_log $web_root/$DOMAIN/log/error_log.txt error;
}
EOF
) >  $config_dir/sites-available/$DOMAIN.conf

echo "Making web directories"
mkdir -p $web_root/"$DOMAIN"
mkdir -p $web_root/"$DOMAIN"/{public,private,log,backup} 
ln -s $config_dir/sites-available/"$DOMAIN".conf $config_dir/sites-enabled/"$DOMAIN".conf
/etc/init.d/nginx reload
echo "Nginx - reload"
chown -R www-data:www-data $web_root/"$DOMAIN"
chmod 755 $web_root/"$DOMAIN"/public
echo "Permissions have been set"
echo "$DOMAIN has been setup"

Veho bluetooth speakers for Nexus 7

The original Nexus 7 (2012 model) is a fantastic tablet but has the constant criticism of its limited mono back facing speakers. Though the audio output level is decent, compared to the iPhone 5 or Nexus 10 (with front facing speakers) it’s quite lacking Enter the Veho bluetooth speakers from the nice guys at MobileZap. These device has 2 speakers and dramatically increases the audio output volume of the Nexus.


Continue reading

Merge PDFs with the Zend Framework

I’ve used a number of PHP libraries to handle creation of PDFs but only recently needed to merge existing PDF files via PHP.

There are a couple of existing libraries to handle this, the previously popular pdfmerger script at codeplex has become unmaintained. Currently using the Zend Framework is the best way to merge PDFs.

Using the PDF component of the Zend Framework is suprisingly easy, just load in the PDFs, clone the pages and save as a new PDF (or display in the browser).

Here’s the code to merge 2 existing PDFs. Its easy enough to add more PDFs or dynamic content using Zend PDF or tcpdf

Note: This is using Zend Framework version 1

PDF merge with Zend - pdf_merge_zend.pph

<?php
$path ="lib/";
set_include_path(get_include_path() . PATH_SEPARATOR . $path);
require_once 'lib/Zend/Pdf.php';

// LOAD PDF DOCUMENTS
$pdf1 = Zend_Pdf::load('first.pdf');
$pdf2 = Zend_Pdf::load('second.pdf');
// WE WILL MERGE OUR TWO PDF FILES INTO A NEW ZEND_PDF OBJECT
$pdfMerged = new Zend_Pdf();

// ADD ALL PAGES FROM THE FIRST PDF TO OUR NEW DOCUMENT
foreach($pdf1->pages as $page){
  $clonedPage = clone $page;
  $pdfMerged->pages[] = $clonedPage;
}
// ADD ALL PAGES FROM THE SECOND PDF TO OUR NEW DOCUMENT
foreach($pdf2->pages as $page){
  $clonedPage = clone $page;
  $pdfMerged->pages[] = $clonedPage;
}
unset($clonedPage);

// SEND THE MERGED PDF DOCUMENT TO BROWSER
//header('Content-type: application/pdf');
//echo $pdfMerged->render();

//save to a file
$pdfMerged->save('out/3.pdf');
?>

Bluetooth headphones for Nexus 7

Thanks again to the guys at MobileZap for sending over Avantree Hive bluetooth headphones to review. I’ve been after wireless heapdhones for my Nexus 7 since I purchased it and the Avantree Hive headphones looks like a great budget friendly bluetooth headphones. Lets look closer at these bluetooth headphones


Continue reading

Nexus 7 keyboard cover

The good folks at MobileZap(AU version of MobileFun.co.uk) sent over a bluetooth keyboard case for my Nexus 7(2012) to review. Currently this is the only hard cover aluminium - clip on - case for the Nexus 7 on the market (though is availble as a few different brands) thats similar to Logitechs offerings for the iPad. This is a mini keyboard to match the size of the Nexus 7, its pretty much the same size and quality of the original Asus Eee 701 PC keyboard


Continue reading