Convert UNIX time into other formats on the fly with MySQL

Typo3 and other CMSs often save dates in UNIX time, which is quite indecipherable by humans. Luckily MySQL has a built in function to convert that number into a human-readable format on the fly.

See the following SQL sample for extracting creation date and last login dates of Members’ Area accounts:

SELECT 
`uid`,
FROM_UNIXTIME(crdate,GET_FORMAT(DATE,'INTERNAL')) AS `DATE CREATED`,
FROM_UNIXTIME(lastlogin,GET_FORMAT(DATE,'INTERNAL')) AS `LAST LOGIN`,
`first_name` AS `FIRST NAME`,
`last_name` AS `LAST NAME`,
`username` AS `EMAIL`,
`company` AS `COMPANY`,
`disable` AS `DISABLED?`
FROM `fe_users`
ORDER BY `fe_users`.`crdate`  DESC
LIMIT 10000

More info:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

2015
08/03
CATEGORY
MySQL
Typo3
Write comment

Allowing Filemanager in User Role Editor

I assign the role of Editor to the staff editing the event websites. By default this limits their ability to use Filemanager. And the usual check boxes don’t work so we have to switch the interface to “Deprecated mode” and check all the “Level_#” boxes.

user-role-editor-filemanager

2015
06/01
CATEGORY
Plugins
Wordpress
Write comment

Find original process in Linux

When you need to find the original thread in a list of a hundred child threads, you can use “ps auxc | grep ”

[root@main www.ewea.org]# ps auxc | grep httpd
root      1989  0.0  0.2 319260 12916 ?        Ss   09:24   0:01 httpd
apache    6341  1.4  1.1 483420 69864 ?        S    13:18   3:10 httpd
apache   12844  1.5  1.1 472324 68452 ?        S    14:16   2:32 httpd
apache   12854  1.5  1.0 466824 62652 ?        S    14:16   2:26 httpd
apache   22137  1.6  1.0 465680 61540 ?        S    15:30   1:25 httpd
apache   22145  1.5  1.0 466868 61920 ?        S    15:30   1:17 httpd
apache   22501  1.5  1.0 466620 62464 ?        S    15:32   1:19 httpd
apache   22508  1.5  1.0 369720 62528 ?        S    15:32   1:18 httpd
apache   22509  1.5  1.1 480484 67440 ?        S    15:32   1:17 httpd
2015
05/06
CATEGORY
Bash
Linux
Write comment

Remote Windows shutdown

To remote shutdown a workstation in 5mins from now:

shutdown /s /t 300 /m \\EWEAW00020

To cancel the command:

shutdown /a /m \\EWEAW00020

 

2015
04/24
CATEGORY
DOS
Microsoft
Write comment

Fix a broken PST?

Outlook can rely on local data files called PSTs for archiving emails. Sometimes these PSTs can get corrupted.
In the unfortunate instance that they stop working, you can use a tool called “SCANPST.exe” that ships with Outlook to try and repair it.

  1. Navigate to C:\Program Files\Microsoft Office\Office15
  2. Locate the SCANPST.EXE icon and double-click it.
    scanpst
  3. Locate the broken PST and click on “Start”.
    scanpst-01
  4. The tool will take a while to scan the broken PST.
    scanpst-02
  5. Then it will tell you what’s wrong with it and ask you if you wish to repair it.
    Click the “Repair” button.
    scanpst-03
  6. The tool will work away (you will probably get a “non-responsive” status until it’s finished, but ignore it and just let it do its thing).
  7. Finally you should be presented with the “Repair complete” dialogue box.
    scanpst-04
  8. Open Outlook and see if your PST is now accessible.
2015
04/16
CATEGORY
Microsoft
Outlook
Write comment

Exhibitor Press Release Uploads

There are three components to the system:

 

A) The exhibitor upload page

The first thing we need to do is build the form using the “WordPress Form Manager” plugin.
We create a form with the following fields: “Company name”, “Document title” and “Select document”. We also add a note field to display additional information about accepted file formats and file size, etc. Pay attention to the upload document section as to restrict the document types.

We also add a “Private Field” at the bottom of the “Form extra” tab. This is what our administrator will use to approve each press release.

Once you have created the form, add it to the exhibitor upload page with the following shortcode format:
[form form-XYZ]

 

B) The public listing page

We include a simple PHP script into the WordPress page to pull data off the database. The code I used can be obtained here.

To summarise, the script will need to be modified in the following places:

  1. Database name
  2. Form table
  3. User (an account with read-only access just to the form table was created for this application)
  4. Password
  5. The blob variables in the loop

 

C) Administration page

The administrator logs into WordPress and navigates to the Submission Data tab of the form being used.
Using the checkboxes and the “Edit selected” function from the dropdown list of actions, the admin can edit…

form-edit

and approve each press release.

form-approve

2015
02/04
CATEGORY
Coding
HTML
PHP
Plugins
Wordpress
Write comment

CSS box shadow

Here are some lesser-know tricks for CSS dropshadows.
http://css-tricks.com/snippets/css/css-box-shadow/

You can create the following styles:

Using the following code:

<style type="text/css">
#example {
background: none repeat scroll 0 0 #ccc;
box-shadow: 5px 5px 5px #666;
height: 100px;
margin: 15px 0;
width: 100px;
}
#example2 {
background: none repeat scroll 0 0 #ccc;
box-shadow: 0 0 10px #000000 inset;
height: 100px;
margin: 15px 0;
width: 100px;
}
#example3 {
background: none repeat scroll 0 0 #ccc;
box-shadow: 0 8px 6px -6px black;
height: 100px;
margin: 15px 0;
width: 100px;
}
</style>
2014
12/16
CATEGORY
Coding
CSS
Write comment

Force downloads on certain filetypes

If you want to force a certain filetype to be downloaded (and not be opened directly in the browser) add the following into an .htaccess file in the directory of the files.
Change the filetype to match your needs.

<FilesMatch "\.(gif|jpe?g|png)$">
   ForceType application/octet-stream
   Header set Content-Disposition attachment
</FilesMatch>
2014
12/01
CATEGORY
.htaccess
Apache
Write comment

Resetting the root password

If you ever forget your root password, you can actually reset it with the following steps.

  • Interrupt the boot at the GRUB stage and boot to runlevel 1, AKA single user mode.
  • Interrupt GRUB by typing a character such as “space” then append to the kernel line by typing “a”
  • Backspace through “rhgb quiet” and append ” 1 “
  • Then hit <enter>

This will give you a root shell and not a login prompt. From there you can use the “passwd” command to set a new root password.
Other user passwords can be reset, and other administrative tasks can be performed as well. Exiting the single user root shell will boot to the multi-user mode defined in /etc/inittab.

CentOS-6 Note: Due to an upstream SELinux bug the root password cannot be reset on a fresh install of CentOS-6 without an additional step. Booting with “selinux=0” appended to the grub kernel line, or doing “setenforce 0” after logging in and before attempting to reset the password are work-arounds.

(If SELinux is enforcing then do not forget to relabel your filesystem afterwards or you will not be able to login until you reboot and repeat the instructions and include the autorelabel)

 From: http://wiki.centos.org/TipsAndTricks/ResetRootPassword

2014
09/22
CATEGORY
Linux
Security
Write comment

Install MySQL 5.5 on CentOS/RHEL 6.5 and 5.10 via Yum

Redhat Enterprise Linux (RHEL) 5.x and CentOS 5.x have been providing MySQL 5.0 in it’s distribution since that was the active stable branch when RHEL 5.0 was released on 14th March 2007 (CentOS 5.0 on 12th April 2007). Since then, Sun/Oracle have released MySQL 5.1 and MySQL 5.5, so the Webtatic Yum repository has added them to it’s web stack, which now provides updated versions of all the AMP in LAMP.

Update 2014-03-01 – mysql55 packages have been renamed to mysql55w packages (as well as mysql51 to mysql51w). This is to ensure it doesn’t clash with SCL packages.

First, to set up the yum repository, install the webtatic-release RPM based on your CentOS/RHEL release:

Webtatic EL6 for CentOS/RHEL 6.x

rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm

If you already have MySql client or server installed (rpm -q mysql mysql-server), then you can upgrade using the following method:

yum install mysql.`uname -i` yum-plugin-replace
 yum replace mysql --replace-with mysql55w

“yum install mysql” is only there to make sure yum-plugin-replace can resolve dependencies correctly if only mysql-server was installed.

Otherwise, to install MySql client and server, then run:

yum install mysql55w mysql55w-server

You should upgrade existing tables before setting the server to become a production machine, which can be done by starting the server and running the mysql_upgrade script (this may take time depending on the size of the database).

service mysqld start

# This will issue a password prompt for the user. If you don’t have a root user password, remove the “-p”
mysql_upgrade -u root -p

MySQL 5.1

The Webtatic 5 repository for CentOS/RHEL 5.x includes mysql51-* packages for installing MySQL 5.1. This can be installed by replacing in the above commands “mysql55w” with “mysql51w”.

As MySQL 5.1 is standard in CentOS/RHEL 6.x, it is not included in the Webtatic EL 6 repository.

2014
09/12
CATEGORY
Linux
MySQL
Write comment