Development

Through the mysql client:

update mysql.user set password=password('NEW_PASSWORD') where user='USERNAME' and host='HOSTNAME';
flush privileges;

Through the command line:

mysqladmin -u USERNAME -p CURRENT_PWD password NEW_PWD

Replace USERNAME, CURRENT_PWD and NEW_PWD with appropriate values.

Finding records that do not match between two tables.

          CREATE TABLE bookreport (
            b\_id int(11) NOT NULL auto\_increment,
            s\_id int(11) NOT NULL,
            report varchar(50),
            PRIMARY KEY  (b\_id)

          );

          CREATE TABLE student (
            s\_id int(11) NOT NULL auto\_increment,
            name varchar(15),
            PRIMARY KEY  (s\_id)
          );

          insert into student (name) values ('bob');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'A Death in the Family');

          insert into student (name) values ('sue');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'Go Tell It On the Mountain');

          insert into student (name) values ('doug');
          insert into bookreport (s\_id,report)
            values ( last\_insert\_id(),'The Red Badge of Courage');

          insert into student (name) values ('tom');

 To find the sudents where are missing reports:

          select s.name from student s
            left outer join bookreport b on s.s_id = b.s_id
          where b.s_id is null;

              +------+
              | name |
              +------+
              | tom  |
              +------+
              1 row in set (0.00 sec)

 Ok, next suppose there is an orphan record in
 in bookreport. First delete a matching record
 in student:

       delete from student where s_id in (select max(s_id) from bookreport);

 Now, how to find which one is orphaned:

       select * from bookreport b left outer join
       student s on b.s_id=s.s_id where s.s_id is null;

     +------+------+--------------------------+------+------+
     | b_id | s_id | report                   | s_id | name |
     +------+------+--------------------------+------+------+
     |    4 |    4 | The Red Badge of Courage | NULL | NULL |
     +------+------+--------------------------+------+------+
     1 row in set (0.00 sec)

To clean things up (Note in 4.1 you can’t do subquery on same table in a delete so it has to be done in 2 steps):

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

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.

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);

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;

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.

Suppose you want to write a system, which requires a password to do something. Not a login system, but just a shared secret. Suppose also, that you need to show someone the source code, but you don’t want him or her, the secret password can you do that. Sure. Here’s a simple way to do it with PHP.

So I may be slightly atypical. 18 months ago I decided to drop Windows. For a while I’ve been running OSX at home, but since it required new hardware at work, it wasn’t an option there. So I switched to Fedora (our Linux God at work was runing it, and it always nice with an expert around to save the day :-) ). Friday however I switch to KUbuntu and unassisted. KUbuntu is the KDE derivate of Ubuntu, and the installer is the coolest OS installed I’ve ever tried. A few questions, some disc spins and the system were running and usable within an hour.

I’ve been playing with XML-RPC – a remote procedure calling using HTTP as the transport and XML. If you’re interested in how to use XML-RPC in PHP, go to the lab page on XML-RPC with PHP.

Mysql: Change account password

Mysql: Delete orphan records

Mysql: Dump data in XML or HTML

Mysql: Dumping data to a file

Mysql: Loading data from file

Mysql: Random dice

Zend Framework - Ready or not?

To hide a secret in the open

Switch – Fedora to KUbuntu

XML-RPC with PHP