Articles filled under ‘Database’

By default, null values are put on top of the query resultset when field is ordered by in ascendant form.

This comes to be a problem in many scenarios, specially when we are ordering by a position field that can contain an integer value for its position on the dataset or null if position is not defined. Rows that have undefined position have lower weight than the specified ones thus coming first.

The following SQL query is from a very common scenario that represents a SELECT to fetch all city registries in “importance” (most common, not in fact important cities – don’t get mad if you live in an odd city) order.

SELECT
  id, city
FROM
  cities
ORDER BY
  position;

This brings us all null-valued position rows first and not null positioned in ascending order, at the bottom.

This happens because our ordering pool will look like the following:
position (integer or null), city field value (string)

So null values are considered smaller than 1 (lowest positive integer) and then comes first in our resultset.
Read the full article

Searching never seemed something really hard to do because SQL’s LIKE was there to aid the oppressed but… does LIKE the job right?

So we have data and when it gets bigger and bigger, we need to search through it. Let’s take some approaches to the Search function and find out the better way.
Read the full article

This one was quite interesting. I’ve wrote my PHP function to capitalize the fileld at insert with ucwords() but I already had some on the base that I need to convert without re-importing them.

Read the full article