LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 12-01-2009, 03:06 PM   #1
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,033

Rep: Reputation: 69
postgresql + php case insensive query question...


I am running Slackware 13.0 and I have implemented postgresql + php + apache. I am attaching my database to my website via php. I have the following php code. I also have an html page that passes the data inputed on the forms to this php code. However, I want the users to be able to input either upper case, lower case, or a combination of upper/lower in the forms.

Code:
$result = pg_query($dbconn, "select game_id,title,developer,publisher,year,genre,platform,mode,requirements from pc_t where title='$_GET[title]' or
genre='$_GET[genre]' or year='$_GET[year]' or developer='$_GET[developer]' or publisher='$_GET[publisher]' order by game_id");
So I changed to code to this(I only changed the title part for testing purposes). Notice that I replace '=' with '~*' in the 'where title' section.

Code:
$result = pg_query($dbconn, "select game_id,title,developer,publisher,year,genre,platform,mode,requirements from pc_t where title~*'$_GET[title]' or
genre='$_GET[genre]' or year='$_GET[year]' or developer='$_GET[developer]' or publisher='$_GET[publisher]' order by game_id");
The title form works great. I could use any combination of upper/lower case strings. However, it seems that the wildcard character '*' did not end and continued to work across the other fields (genre,year,developer,publisher). For instance with the code directly above implemented I would go to the forms page and leave the title form blank. I would enter something in the genre field and run the query. The database would return all the records instead of the ones that were in that genre I inputed.

I tried enclosing just the title section in quotes. Why does the '~*' continue to work across the other fields?
I have tried using lower(col) which does work if the user inputs lower case ONLY chars. However, if it's all CAPS or a combination of lower/upper the query will not work successfully.

I may have to make case statements for each situation where a user only fills out one of the forms. However, since you can implement 'OR' in SQL I would rather not do this especially because the user can use two of the fields at once providing even more filtering efficiency. For instance, say a user enters a year, genre, developer, the results will be more efficient and accurate instead of putting only a genre.

Last edited by trist007; 12-01-2009 at 03:18 PM.
 
Old 12-01-2009, 04:46 PM   #2
trist007
Senior Member
 
Registered: May 2008
Distribution: Slackware
Posts: 1,033

Original Poster
Rep: Reputation: 69
I got it. I used ILIKE.
[code]$result = pg_query($dbconn, "select game_id,title,developer,publisher,year,genre,platform,mode,requirements from pc_t where title ilike '$_GET[title]' or
genre ilike '$_GET[genre]' or year ilike '$_GET[year]' or developer ilike '$_GET[developer]' or publisher ilike '$_GET[publisher]' order by game_id");
[code]
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Quick question about compiling PHP with PostgreSQL support JellyStones Linux - Server 7 03-14-2007 09:02 PM
working with php and postgresql mamta Linux - Server 1 03-07-2007 05:44 AM
how to disable case sensitivity in mysql query in Fedora Linux Core 3 radatan71 Linux - Software 1 10-29-2005 12:39 AM
PHP MySQL Query Question vi0lat0r Programming 1 07-15-2004 06:02 AM
php/postgresql BobNz Programming 4 05-10-2004 07:18 PM


All times are GMT -5. The time now is 05:29 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration