Searching Problem

[rawky1976]

Hello all

I have a mysql database with a table field called relating_to. The site has an upload page where users add files to a knowledgebase, there is a form field where keywords can be entered; these keywords go into the relating_to filed of the table when uploaded.

I am having trouble with the search page. It has only one textbox for entering keywords and a 'search' button.

Say i have a record with 'big blue house' in the relating_to field, if I search for big blue house it returns the record. If I just search for big I get nothing. I want it to pick up on partial entries like this. Obviously someone searching is unlikely to enter exactly the string that the submitter used.

I am using Dreamweaver to create the recordset, here is the code: -

[sub]$maxRows_rs_searchResults = 10;
$pageNum_rs_searchResults = 0;
if (isset($_GET['pageNum_rs_searchResults'])) {
  $pageNum_rs_searchResults = $_GET['pageNum_rs_searchResults'];
}
$startRow_rs_searchResults = $pageNum_rs_searchResults * $maxRows_rs_searchResults;

$colname_rs_searchResults = "-1";
if (isset($_GET['searchCriteria'])) {
  $colname_rs_searchResults = (get_magic_quotes_gpc()) ? $_GET['searchCriteria'] : addslashes($_GET['searchCriteria']);
}
mysql_select_db($database_ConnKnowledgeBase, $ConnKnowledgeBase);
$query_rs_searchResults = sprintf("SELECT document_name, about, category_id, format_id, submitter_id FROM document WHERE relating_to = '%s' ORDER BY document_name ASC", $colname_rs_searchResults);
$query_limit_rs_searchResults = sprintf("%s LIMIT %d, %d", $query_rs_searchResults, $startRow_rs_searchResults, $maxRows_rs_searchResults);
$rs_searchResults = mysql_query($query_limit_rs_searchResults, $ConnKnowledgeBase) or die(mysql_error());
$row_rs_searchResults = mysql_fetch_assoc($rs_searchResults);

if (isset($_GET['totalRows_rs_searchResults'])) {
  $totalRows_rs_searchResults = $_GET['totalRows_rs_searchResults'];
} else {
  $all_rs_searchResults = mysql_query($query_rs_searchResults);
  $totalRows_rs_searchResults = mysql_num_rows($all_rs_searchResults);
}
$totalPages_rs_searchResults = ceil($totalRows_rs_searchResults/$maxRows_rs_searchResults)-1;
?>[/sub]

I am pretty new to php and have been letting dreamweaver do the work for me really. I imagine some kind of array is in order to put the multiple words into; if that's the case can you explain it in idiots terms for me please?!

Mark

[craygo]

You have to use LIKE for partial searches not "="

example
SELECT * FROM table WHERE field LIKE '%s%'

Ray

[pocobueno1388]

<a href="http://www.htmlite.com/mysql011.php">Using "LIKE"</a>

I think that might be what you are looking for.

[rawky1976]

Thank you both, so now I've got: -

$query_rs_searchResults = sprintf("SELECT document_name, about, category_id, format_id, submitter_id FROM document WHERE relating_to LIKE '%s' ORDER BY document_name ASC", $colname_rs_searchResults);

But it still doesn't return the record sorry?

[pocobueno1388]

$query_rs_searchResults = sprintf("SELECT document_name, about, category_id, format_id, submitter_id FROM document WHERE relating_to LIKE '%s%' ORDER BY document_name ASC", $colname_rs_searchResults);

Try that.

[rawky1976]

No output on the page apart from: -

[sub]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 10' at line 1[/sub]

Does that relate to the line: -

[sub]$query_limit_rs_searchResults = sprintf("%s LIMIT %d, %d", $query_rs_searchResults, $startRow_rs_searchResults, $maxRows_rs_searchResults);[/sub]

?

[Deltran]

More than likely, whatever $query_rs_searchResults contains is causing the error.

[rawky1976]

So is $query_rs_searchresults different from rs_searchresults (which is just the recordset)?

rs_searchresults just holds the SQL select statement doesn't it?

How can i find out what it holds? Is there a break/display contents line I can add that will display on the output page?

[craygo]

If you want to see what the sql statement says, add this after the query

echo $query_rs_searchResults;

this will echo the query and you can check it for mistakes

Ray

[rawky1976]

Sorry to be a bit stupid here, I've tried adding that command at various points in the script and in the body without any output?

So what does the 'LIMIT 0, 10' mean anyway?

Mark

[Deltran]

[quote author=rawky1976 link=topic=123715.msg511782#msg511782 date=1169588186]
So what does the 'LIMIT 0, 10' mean anyway?
[/quote]

It will only send back the first 10 records the query finds.

[linuxdream]

Using sprintf requires that you have a %s or %d or whatever to substitute for the given value (I hate C style strings!!!). Yet in the SQL statement after LIKE you want to use a % to tell Mysql to search for anythign before/after. So your main SQL statement should be something like:

$sql = "SELECT document_name, about, category_id, format_id, submitter_id FROM document WHERE relating_to LIKE '%$colname_rs_searchResults%' ORDER BY document_name ASC";

Try it without the LIMIT statement first to be sure it works.

[rawky1976]

When I originally set this up I added a dynamic table in dreamweaver but it put each field in its own column (not formatted the way i want) [b]and added navigation controls[/b]. Is this display only 10 left over from that? Because I deleted it and created a proper table then selected the whole thing to be a repeating region.

I never specified '10' records anywhere apart from 10 records per page in the first dynamictable.

Do I need it? Should I start again!? Since moving to PHP from trying to learn ASP everything has gone so well; I've knocked this whole thing up in less than a week but now I face too high a hurdle!!!

[rawky1976]

So I use

[sub]$sql[/sub]

instead of

[sub]$query_rs_searchResults = sprintf[/sub]

?, I just tried replacing linuxdream suggestion [sub]%s%[/sub] with [sub]'%$colname_rs_searchResults%'[/sub] and it returns all records on the intial page, then if i click the search again link (to back to search page) and type keywords I get the original error!!!

[linuxdream]

Hmmm, I guess I missed the part where the tables and such were created entirely in Dreamweaver. I didn't even know Dreamweaver did that? Scary! Anyway, it should be like so:

$query_rs_searchResults = "SELECT document_name, about, category_id, format_id, submitter_id FROM document WHERE relating_to LIKE '%$colname_rs_searchResults%' ORDER BY document_name ASC";

I forgot to assign the SQL to your variable name.

That should work for getting you the results based on the search criteria. As far as the table design and working through Dreamweaver...I'm really sure. I don't know how Dreamweaver does queries, tables, results, etc.. If you can login to your MySQL server, execute a "describe document;" which will give you a print out of the table structure. That way you can craft your own queries withouth relying on Dreamweaver...if you want.

[rawky1976]

I didn't use Dreamweaver to create! I used this in the mysql monitor: -

CREATE TABLE document
(
    document_id INT NOT NULL,
    document_name VARCHAR(50),
    relating_to VARCHAR(50),
    about VARCHAR(100),
    category_id  VARCHAR(30),
    format_id  VARCHAR(30),
    submitter_id  VARCHAR(30),

    PRIMARY KEY(document_id)
) TYPE = INNODB;

As it happens your last suggestion seemed to work. It now returns a record based on only one of the keywords!

[u][b]You are all living legends, thank you so much!!!!!!!!!!!!!!!!!!!![/b][/u]

[linuxdream]

OK so I looked at the code again you will want to replace the stuff below with

[code]<?php
mysql_select_db($database_ConnKnowledgeBase, $ConnKnowledgeBase);
$query_rs_searchResults = "SELECT document_name, about, category_id, format_id, submitter_id FROM document WHERE relating_to = '%$colname_rs_searchResults%' ORDER BY document_name ASC ";
//$query_limit_rs_searchResults = sprintf("%s LIMIT %d, %d", $query_rs_searchResults, $startRow_rs_searchResults, $maxRows_rs_searchResults);
$query_limit_rs_searchResults = '';
$rs_searchResults = mysql_query($query_rs_searchResults . $query_limit_rs_searchResults, $ConnKnowledgeBase) or die(mysql_error());
$row_rs_searchResults = mysql_fetch_assoc($rs_searchResults);
?>[/code]

This will remove the LIMIT stuff since you said you didn't want to limit the results. If you want go get it back, then just comment out the blank searchResults line and uncomment the original one above it. It's best to copy/paste the above since I added as space after ASC in the main query. That could have been the source of your other error as well...

This should work.