Mysql: Dump data in XML or HTML

Assume you have the table “exams” in the database “test”.Then, the following will give you XML output if executed from the shell prompt with the “-X” option. For html output use the “-H” option.

mysql -X -e "select \* from exams" test

Mysql: Dumping data to a file

To dump data into a comma separated file use this:

  SELECT * INTO OUTFILE 'tablename.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n'
  FROM tablename;

Replace tablename with the tablename of the table you which to dump to a file.

Mysql: Loading data from file

Loading Data into Tables from Text Files.

Assume you have the following table.

            CREATE TABLE loadtest (
                pkey int(11) NOT NULL auto\_increment,
                name varchar(20),
                exam int,
                score int,
                timeEnter timestamp(14),
                PRIMARY KEY  (pkey)
               );
And you have the following formatted text file as shown below with the unix “tail” command:

    $ tail /tmp/out.txt
    'name22999990',2,94
    'name22999991',3,93
    'name22999992',0,91
    'name22999993',1,93
    'name22999994',2,90
    'name22999995',3,93
    'name22999996',0,93
    'name22999997',1,89
    'name22999998',2,85
    'name22999999',3,88
NOTE: loadtest contains the "pkey" and "timeEnter" fields which are not
present in the "/tmp/out.txt" file. Therefore, to successfully load
the specific fields issue the following:

         mysql> load data infile '/tmp/out.txt' into table loadtest
                  fields terminated by ',' (name,exam,score);

Mysql: Random dice

Getting a random roll of the dice:

          CREATE TABLE dice (
            d\_id int(11) NOT NULL auto\_increment,
            roll int,
            PRIMARY KEY  (d\_id)
          );

          insert into dice (roll) values (1);
          insert into dice (roll) values (2);
          insert into dice (roll) values (3);
          insert into dice (roll) values (4);
          insert into dice (roll) values (5);
          insert into dice (roll) values (6);

          select roll from dice order by rand() limit 1;

Zend Framework - Ready or not?

We are a fairly large PHP shop at work running some of the largest Danish websites. In a fairly new project, it was suggested that we considered using the Zend Framework to fast track development and piggy back upon some of the components provided by the framework. We looked at it, and said no – at least for now. Since the Zend Framework website does an excellent sales pitch on why you should use it, here’s some of the arguments why you should restrain from using the framework.