Running a search with a foreign key

[josephbupe]

Hi,

 

I found a script written by someone else which works fine for running searches on multiple columns.

 

I have just included in it a category field, which is a foreign key from the categories table, and I want to run queries with this one also, but currently am not getting results when I run queries with the category (foreign key).

 

the code:

 

 

<?php

if ($_REQUEST["string"]<>'') {
    $search_string = " AND (ctitle LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%' OR csubject LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%')";    
}

if ($_REQUEST["ref"]<>'') {
    $search_string = " AND (creference LIKE '%".mysql_real_escape_string($_REQUEST["ref"])."%')";
}

if ($_REQUEST["category"]<>'') {
    $search_category = " AND category='".mysql_real_escape_string($_REQUEST["category"])."'";    
}

if ($_REQUEST["cmaterial"]<>'') {
    $search_cmaterial = " AND (cmaterial LIKE '%".mysql_real_escape_string($_REQUEST["cmaterial"])."%')";    
}

if ($_REQUEST["ctechnic"]<>'') {
    $search_ctechnic = " AND (ctechnic LIKE '%".mysql_real_escape_string($_REQUEST["ctechnic"])."%')";    
}

if ($_REQUEST["cartist"]<>'') {
    $search_cartist = " AND (cartist LIKE '%".mysql_real_escape_string($_REQUEST["cartist"])."%')";
}
if ($_REQUEST["cperiod"]<>'') {
    $search_string = " AND (cperiod LIKE '%".mysql_real_escape_string($_REQUEST["cperiod"])."%')";
}

if ($_REQUEST["csource"]<>'') {
    $search_string = " AND (csource LIKE '%".mysql_real_escape_string($_REQUEST["csource"])."%')";
}

if ($_REQUEST["cyear"]<>'') {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE cyear = '".mysql_real_escape_string($_REQUEST["cyear"])."'".$search_string.$search_category;
}
else {
    $sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE c_id>0".$search_string.$search_category.$search_cmaterial.$search_ctechnic.$search_cartist;
}

$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);

if (mysql_num_rows($sql_result)>0) {

//    The following line gives us an SQL statement with appropriate limits applied
    $sql_result=mysql_query($sql) or die($sql." - ".mysql_error());
    
    while ($row = mysql_fetch_assoc($sql_result)) {

// and the result table goes below ...

?>
 

This is the select drop down list for categories:

 

<select name="category">
    <option value="">--Object Category--</option>
<?php
    $sql = "SELECT cat_id,category FROM categories GROUP BY category ORDER BY category";
    $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql);
    while ($row = mysql_fetch_assoc($sql_result)) {
        echo "<option value='".$row["category"]."'".($row["category"]==$_REQUEST["category"] ? " selected" : "").">".$row["category"]."</option>";
    }
?>
</select>
 

 

Is it because I am escaping it (foreign key) like a string or something else?

 

joseph