How to search a database

Anon

I am creating a website for a non-profit organization. One of the site's sections is a library database where users can find books that the organization has. They would search by an Alphabetical listing, keyword, subject, title, and author. I have created a table named library, with the six columns, id,letter,title,subject,keyword, and author.

How can I get users to search the database in the five formats: subject,title,keyword,letter, and author?

The undeveloped search page can be seen here: http://www.challengedfrc.org/?p=library

crislewis

I suggest a simple form with an input text field for each criterion. Set the submit action to point to a search php file. This file retrieves the criteria from the get or post params and constructs a query string from them. e.g.

$sql_query .= "SELECT * FROM library";

// if any criteria are set add a where clause

if ($subject ¦¦ $title ¦¦ ...) {

£sql_query .= " WHERE";

// if a subject criteria was given add it to the where clause

if ($subject_criteria) {

$sql_query .= " subject = '$subject_criteria';
$multiple_criteria = 1; // show we have already got where clause

}

// if a title criteria was given add it to the where cause

if ($title_criteria) {

// if we already have where clause include AND for the new criteria

 if ($multiple_criteria){
    $sql_query .= " AND";
 }

 $sql_query .= " subject = '$subject_criteria';
 $multiple_criteria = 1;

} . . .

$result = mysql_query($sql_query);

. . .

For default searches merely 'order by' the relevant field. e.g.

SELECT * FROM library ORDER BY subject

Please check the syntax of the above. I am doing this on a laptop on a plane so can;t runn it to check but it should be fairly obvious what's going on.

Hope this helps,

Cris

Anon

Alright, here is my script:
<?
mysql_connect ($server,$user,$pass);

mysql_select_db (challengedfrc);

$result = mysql_query ("SELECT * FROM library WHERE $querytype LIKE '$querytype%' AND search LIKE '$search%' ORDER BY LETTER DESC ");
while($row = mysql_fetch_array($result))
{

$search = $row["search"];
$querytype = $row["querytype"];
?>
<p>
<b><? echo "$querytype"; ?></b>
Your search, <? echo "$search"; ?>, resulted in: <? echo "$result"; ?>
</p>
<?
}
?>

And what I get is: Warning: Supplied argument is not a valid MySQL result resource in /home/challengedfrc/search.php on line 212

Line 212 is while($row = mysql_fetch_array($result))

What is wrong??

crislewis

$result will not be set if the query returns no rows. I suspect this is what is happening.

Try echoing out the query string to check how it is actually resolved into the mysql_query command.

if it still looks OK run the same query directly into mysql through somehting like phpmyadmin.

This might help you find the problem.

I note from you script that:

mysql_select_db argument is not a string. Could this be it? This might not give you a correct connection to your chosen database. Check it with something like:

if (!mysql_select_db(..)) die ("db not selected);

Cheers,

Cris

Anon

Thanks for the advice. I tried the die thing, but no error message turned up.. so that is not the problem.

When I ran the query in phpmyadmin, I got this:
MySQL said: Unknown column '$querytype' in 'where clause'

How can I define the clause from which the user specified?