Fix 00-00-0000 00:00:00 date errors in MySQL

I want my updated_at fields to be:

• non-null
• equal to created_at when the record is created
• updated by the db when the record changes

Sometimes I stumble on a project where the date field is allowed to be a zero date.

To fix that, I use the following query:

UPDATE users
SET updated_at = created_at
WHERE CAST(updated_at AS CHAR(20)) = '0000-00-00 00:00:00'

Then, I fix the field itself.

ALTER TABLE users CHANGE updated_at 
updated_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP

SQL: get number of days until next birthday

What if you have a birthdate and you want to order the records by number of days until the next birthday?

That is what this query does. It takes into account that the birthdate is in the past and you want to get the next birthday given the current date.

SELECT 
  birthday
  , TIMESTAMPDIFF(day, birthday, CURDATE())
  ,365.25 -
        (
          case
            WHEN TIMESTAMPDIFF(day, birthday, CURDATE()) = 0 THEN 364.25
            WHEN birthday IS NULL THEN 0
            ELSE TIMESTAMPDIFF(day, birthday, CURDATE())
          end
          mod 365.25
         ) AS days_till_birthday
FROM users
order by 365.25 -
        (
          case
            WHEN TIMESTAMPDIFF(day, birthday, CURDATE()) = 0 THEN 364.25
            WHEN birthday IS NULL THEN 0
            ELSE TIMESTAMPDIFF(day, birthday, CURDATE())
          end
          mod 365.25
         ) asc

It’s based off of this answer on StackOverflow: https://stackoverflow.com/a/51407757

The difference is that it takes also works for people having their birthday today and ordering the records having NULL values for birthday at the end.

So people without their birthday are places last, where normally the NULL records are ordered first.

MySQL fix timestamps for laravel

Execute the following queries to have your ‘created_at’ column the current timestamp upon creation and your ‘updated_at’ the current timestamp upon modification.

// set the default values
DB::statement("ALTER TABLE $table CHANGE created_at created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP")
DB::statement("ALTER TABLE $table CHANGE updated_at updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP");

Prepared statements debuggen? Zet je query log aan!

Prepared statements zijn mooie dingen, maar debuggen is niet zo fraai. Als je door je code stapt met xdebug kom je er al gauw achter dat je niet hele query terug kunt zien.

Je ziet alleen de query met placeholders.

De makkelijkste oplossing is je query-log aan te zetten in je database. Hiermee worden alle queries gelogd die de database uitvoert.

Continue reading

Change mysql_ to mysqli_ functions

In the process of upgrading PHP5.3 code I had to change all deprecated mysql_* functions to their mysqli_* counterparts.

For a lot of functions the signature stayed the same.

But mysqli_query and mysqli_connect have differences. So you can’t just find and replace them.

Instead of doing this manually, I wanted to find and replace recursively while changing the order of the arguments.

In vim:

# change mysql_query(param1, param2) to: 
# mysqli_query(param2, param1)
:%s/mysql_query(\(.\{-}\),\(.\{-}\))/mysqli_query(\2, \1)/g

Using sed:

# on linux

# mysql_query(param1, param2) to 
# mysqli_query(param2, param1)
sed -i 's|mysql_query(\(.*\),\(.*\))|mysqli_query(\2, \1)|g' devices.php

# on mac (otherwise you get the 'invalid command mode' when 
# you run the sed command)

# mysql_query(param1, param2) to: 
# mysqli_query(param2, param1)
sed -i '' -e 's|mysql_query(\([^,]*\),\([^)]*\))|mysqli_query(\2, \1)|g' devices.php

Recursively changing all files:

# in all files under current directory:
# mysql_query(param1, param2) to: 
# mysqli_query(param2, param1)
fgrep -rl mysql_query . | while read file; do
  sed -i '' -e 's|mysql_query(\([^,]*\),\([^)]*\))|mysqli_query(\2, \1)|g' $file
done

Note that sed cannot do non greedy matching.

That’s why we’re searching for anything but the separator until the separator like this:

# non greedy matching with sed
\([^,]*\),

It basically states: get everything except for the comma until you get a comma (which is the first one to appear).