Solved

Search all columns in Postgresql table

Posted on 2012-04-04
2
1,124 Views
Last Modified: 2012-08-13
I am looking for a way to allow a user from a web front end to query all columns in a postgresql table for multiple values. So if had a tabled named Orders that has columns "id, date, customer, product".

I would like allow the user to be able to search for all entries in the table where the customer Bill ordered lamps by passing a search value of "Bill + Lamps" or something similar to this.

I'm thinking I will need to setup full-text search, but I wasn't sure if this is the case and how to set it up.
0
Comment
Question by:abgtemp
2 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37809623
Suggest you create a script that uses SELECT * to get all of the information in all of the rows in the table, and that uses var_dump() to put all of this information on the screen.  Feed the resulting pages to Google "site search" and see if you get the results you want.  If that works, you can refine the var_dump() output to make something that is more eye-pleasing to the client.

Not sure what your table structure might look like, but I am guessing that you have more than one table?  If so, you will need to write a "view" script for every possible relationship between the tables.  This may be a big and time-consuming task.  Whether you choose to use a pre-existing service like Google or invent your own search with full-text searching will largely be decided by how much time and budget you have available.  Google will likely be faster and less expensive.
0
 
LVL 39

Accepted Solution

by:
lcohan earned 300 total points
ID: 37812367
I suggest you use Postgres functions to do that where client app can pass in certain parameter values (like column and string to search for) then in the function you run pg sql like:

select * from table_name where column name like '%search_text%';

You don't necesarily need full text search especialy FULL_TEXT searches are different than LIKE and can show more records than exact match - please see more details at: http://www.postgresql.org/docs/9.0/static/textsearch.html

As far as installing full text - you don't need to install anything but create GIN/GIST indexes [ http://www.postgresql.org/docs/9.0/static/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX ] and use speciffic commands to do the full text search - http://www.postgresql.org/docs/9.0/static/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Decrypt string by php 7 47
AJAX pass along a variable 3 46
Help with PHP 13 27
Extracting content from meta tag PHP MYHTML DOM 3 21
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question