[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Search all columns in Postgresql table

Posted on 2012-04-04
2
Medium Priority
?
1,363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 111

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 40

Accepted Solution

by:
lcohan earned 900 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

649 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