Randomize timestamp in MySQL

To randomize timestamp column in mysql You can use the following statement:

UPDATE yourtable SET 
timestampfield = TIMESTAMPADD(MINUTE, RAND()*60,timestamp_field);

Show images depending on the browser's width with CSS only

I had the make promo offers sections form our site. Usually this is an easy task but the requirement on this case was to preserve the full page width design and keep the page usable for 600px wide screens.

So let's start with the HTML portion.

image 1 image 2 image 3 image 4 image 5

The HTML will render as this: Screen Shot 1

Let's add a little css to get the desired effect:

  #top_offers{
    height: 190px;
    background-color: #d3d7cf;
    overflow: hidden; /* hide the excess images*/
    text-align: justify; /* or center here */
  }
  #top_offers a img{
    margin: 20px;
    border:none;
  }

Which brings the following results: Screen Shot 2

Screen Shot 3

You can play with the example here.

Linux batch encoding convert one liner

find -name '*.php' -exec iconv -f cp1251 -t utf8 '{}' -o '{}'.utf8 \; 

Idea taken from here.

Alter Columns Position in Postgres

Some workarounds to alter columns position in Postgres

MySQL Database Alias

Sure, it's not possible to create alias for database in MySQL but here's a good workaround. Don't forget to set the perditions for the alias as well.

chomp for bash

Bash doesn't have chomp command but you can use tr to get rid of \n \r (and other) characters.

SQL_CALC_FOUND_ROWS (kind of) for postgres

When I switched from MySQL to PostgreSQL, one of the things I missed the most was the ease of paging using SQLCALCFOUNDROWS and SELECT FOUNDROWS() afterwards. One of the places I use it heavily is in my Table class, which generates, sortable, filterable and paged HTML table based on an SQL Query. The tough spot was to get all the records' count, without supplying second query.

I found the solution in ADODB's Pager class and it is the following:

Given the query: SQL, execute the following query:

SELECT Count(*) FROM ([SQL]) AS foo;

I know it's not as easy as SQLCALCFOUND_ROWS but it works;

MySQL UNION DISTINCT with result priority

I had to modify the search algorithm today, the idea was to fetch the exact matches first then the split words matches. The status quo was:

(SELECT product_id FROM products WHERE a=1)
  UNION DISTINCT
(SELECT product_id FROM products WHERE a=2 OR a=1)

I wanted to be sure that the result from the first sub-query are before the second one. The first thing that kame to my mind was:

(SELECT product_id, 1 as priority FROM products WHERE a=1)
  UNION DISTINCT
(SELECT product_id, 2 as priority FROM products WHERE a=2 OR a=1)
ORDER BY priority

but unfortunately this breaks the UNION DISTINCT clause and the results are duplicated.

I came out with the following solution:

SELECT MIN(priority) AS priority, product_id
FROM (
  (SELECT product_id, 5 AS priority FROM products WHERE a=1)
    UNION
  (SELECT product_id, 6 AS priority FROM products WHERE a=1 OR a=2)
) AS t
GROUP BY (product_id)
ORDER BY priority

RT73 on Hardy Heron

If you happen to use rt73 wireless with serial monkey drivers, and after upgrading the wireless stopped to work, take a look at this thread on how to fix it. The good news is that rt73 adapters now work out of the box with Hardy.

Tags database schemas

A great article about tags database schemas can be found here.