Livesearch Query

urbanmac

Bitflux (http://blog.bitflux.ch/de/ ) has a great livesearch feature and also a wiki (http://blog.bitflux.ch/wiki/LiveSearch ) on how to implement it. Accept he glances over the part about querying your database and making the results which for a newbie I'm fidning hard to get my head around (im sure its very simple)

he says:

Write a simple page for querying the database and retrieving matching records against the string contained in the above cited variable ('q'). Resulting output has to be a text/xml file (check http://blog.bitflux.ch/livesearch.php?q=PHP as a reference) in the form of a parent <div class="LSRes"></div>, containing one more <div class="LSRow"></div> for each record: inside the latter put whatever code had to be displayed (typically a link).

I can do a simple connect with the below code:

<?php

$dhost = 'localhost';
$duser = 'username';
$dpass = 'password';
$dname = 'databasename';

@mysql_connect($dhost, $duser, $dpass) or
die("mySQL service unavailable please try again later");

@mysql_select_db($dname) or
die("Database $dname unreachable, contact an administrator");

But I'm stuck on the query against the string q 😕

Would really appreciate some help

Thanks

Roger_Ramjet

Well now, what data do you have in your database?

He is doing a full text search on his. Bit difficault to help you query your database when we don't know anything about it.

urbanmac

Hello,

Thanks for the link, I've had good read through that.

Its a really simple database with one table and five fields.

Table is called hwz_postcodes
Fields are: outcode, x, y, latitude, longitude
(just dummy data I found on the web to play with)

I would like the livesearch to display all five fields based upon the results in the outcode field.

Is this alnong the correct lines:
SELECT * FROM hwz_postcodes WHERE outcode, x, y, latitude, longitude

thanks 😃

Roger_Ramjet

// exact match
$sql = "SELECT * FROM hwz_postcodes WHERE outcode = '" . $input . "'";
// partial match
$sql = "SELECT * FROM hwz_postcodes WHERE outcode LIKE '%" . $input . "%'";

I would also point out that his method queries the database for every keystroke. That is how I can see he is using full text search: the list does not change or match anything until after the 4th char has been input and that is a feature of FTS, short words are not matched.

What you query and how really depends on the data type and contenst of the column/s you are searching on.

urbanmac

interesting point about the 4th key stroke, thanks for that.

so now I have my query, which I was sort of :bemused: getting there with, how do I then match that against 'q string'?

Something like this to print it

echo "Results(<b>". $q ."</b>) :";

Roger_Ramjet

EH!!

If $q is the user input then you replace my $input var in the query string with $q - that's why I called it 'input'.

To display the results you do the usual with query results.

Now, if you really want to learn this then FTS is the only way to go. It is also the only thing that I can see that this would be a worthwhile effort for. It will order the results according to relevence, whereas any other search will order according to the primary key inless you specify otherwise.

urbanmac

Roger Ramjet wrote:
EH!!

To display the results you do the usual with query results.

which is?

Roger_Ramjet

while ($row = mysql_fetch_array($result)) {
    // process row
    // eg
   echo '<tr><td class="boldTxt">' . $row['id'] . '</td></tr>';
}