Im new to postgresql and php, but i am looking to implement a full text search and am having trouble querying the basic query for text search, I would like to print the result on screen as it would show in postgres itself, Help would be greatful.
Thank you.
Theres a lot of concepts in your question, lets run through it:
First you need to CREATE TABLE:
CREATE TABLE person (
id SERIAL PRIMARY KEY,
fullname TEXT NOT NULL,
dob DATE,
bio TEXT NOT NULL
);
Insert some test data:
INSERT INTO person (fullname, dob, bio) VALUES
('Steve Jobs', '1955-02-24', 'Steven Paul "Steve" Jobs...'),
('Tutankhamun', NULL, 'Tutankhamun (alternately spelled...');
To make searching faster you should create a full text index on the column you plan to search on:
CREATE INDEX person_fts ON person USING gin(to_tsvector('english', bio));
In your PHP script you will have to connect to PostgreSQL:
$dbconn = pg_connect("dbname=mary");
Now you can do a full text search with pg_query():
$words = "steve jobs";
$sql = "SELECT * FROM person WHERE to_tsvector(bio) @@ to_tsquery('$words')";
$query = pg_query($dbconn, $sql);
if(!$query)
die("An error occured.\n");
If you want to return everthing like you see it in psql then render the records to a TABLE:
echo "<table>";
while($row = pg_fetch_row($result)) {
echo "<tr>";
foreach($row as $cell)
echo "<td>{$cell}</td>";
echo "</tr>";
}
echo "</table>";
CREATE INDEX person_fts ON person USING gin(to_tsvector('english', bio)); ...if i want to search even in other fields, not only bio, what i have to do? Have i to do CREATE INDEX for every field?to_tsvector('english', bio || field2). The downside is you can't use the same index to search in only bio or field2. So you will need to create an index for each scenario.<?php
$conn = pg_connect('host=localhost port=5432 dbname=test user=lamb password=bar');
$search = 'some words';
$result = pg_query($conn, "SELECT id, text FROM some_table WHERE text ILIKE '%$search%'");
?>
<table>
<tr>
<th>ID</th>
<th>Text</th>
</tr>
<?php foreach($array = pg_fetch_all_columns($result) as $value): ?>
<tr>
<td><?php echo $value[0]; ?></td>
<td><?php echo $value[1]; ?></td>
</tr>
<?php endforeach; ?>
</table>
