pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Lynna Landstreet <lynna@gallery44.org>
To: pgsql-php@postgresql.org
Subject: Case sensitivity when searching for and displaying data
Date: Wed, 03 Sep 2003 18:31:45 -0400
Message-ID: <BB7BE411.AB4%lynna@gallery44.org> (raw)

Hello,

I've gotten a simple PHP search page working on the artists database that
I've been developing these past few months, but I'm running into a few
problems with PostgreSQL's case sensitivity.

Ideally I'd like the search text to case insensitive, so that if the user
enters "jane smith", "Jane Smith" or "JANE SMITH", they'll still find the
record for Jane Smith. But I'm not sure how to do that.

I know that in PHP you can use strtoupper() and strtolower() to change the
case of text, but any solution I can think of with forcibly changing the
case of the search text the user inputs would be problematic given that I
don't necessarily know what case the names are in in the actual database -
most of them would be in standard title case, with the first character
capitalized and the rest lower case, but these are artists we're talking
about, so some of them insist on having their name in all lower case and
that sort of thing. Plus some artists from other cultures have
capitalization in their name that doesn't follow the English standard (de
Broin, di Maria, etc.). So I think I need a way of making the actual SQL
query case-insensitive, and I'm not sure how or even if that can be done.

Secondly, the case sensitivity thing is also messing up the order of the
displayed results. Right now I have them set to order by lastname, and that
makes any names that begin with a lower case character come at the end of
the list because of the order of the ASCII values of the letters, rather
than where they would normally be in alphabetical order. Does anyone know if
there's a way around this?


Thanks,

Lynna
-- 
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org




view thread (6+ messages)  latest in thread

Message-ID: <BB7BE411.AB4%lynna@gallery44.org>
Permalink:  ../BB7BE411.AB4%25lynna@gallery44.org/
Also on:    postgresql.org/message-id/BB7BE411.AB4%lynna@gallery44.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: lynna@gallery44.org
  Subject: Re: Case sensitivity when searching for and displaying data
  In-Reply-To: <BB7BE411.AB4%lynna@gallery44.org>
  References: <BB7BE411.AB4%lynna@gallery44.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox