Thanks, Bastien. Your code is bloody brilliant and I incorporated the error reporting for the queries that you suggested. Before I did so, the result was always "Sorry, no records were found!" even though I knew that the criteria I selected should have yielded several results. Now, when I again set the form for criteria that should yield results, it hangs up on query 1 with the error message: "Could not execute query1Unknown column '$img_type' in 'where clause'"
should the checkboxes be arrays like:
etc? I've gone through it multiple times but can't figure out why I'm getting this error.
//check for image type -- checked with some old code and this seems to
//be a better way of handling things...
//are these numbers or alphanumeric strings? (these are strings) string will require the single
//quote to make the query correct
if (count($image_type_request)==1){
$img_type=$image_type_request;
}elseif(count($image_type_request) >1 ) {
$img_type=implode(",",$image_type_request);
//turn the results into a comma separate variable
}else{
//it's ok if the user does not select a value for each of the checkbox sets.
//This just makes the search more general
//lets leave it alone and set it to "na" for not applicable
$img_type="na";
}
//are these numbers or alphanumeric strings? (these are strings) string will require the single
//quote to make the query correct
if (count($subject_request)==1){
$sub_type=$subject_request;
}elseif(count($subject_request)>1) {
$sub_type=implode(",",$subject_request);
//turn the results into a comma separate variable
}else{
//it's ok if the user does not select a value for each of the checkbox sets.
//This just makes the search more general
//lets leave it alone and set it to "na" for not applicable
$sub_type="na";
}
//are these numbers or alphanumeric strings? (these are strings) string will require the single
//quote to make the query correct
if (count($extension_request)==1){
$ext_type=$extension_request;
}elseif(count($extension_request)>1) {
$ext_type=implode(",",$extension_request);
//turn the results into a comma separate variable
}else{
//it's ok if the user does not select a value for each of the checkbox sets.
//This just makes the search more general
//lets leave it alone and set it to "na" for not applicable
$ext_type="na";
}
/*
$query1 = "SELECT id FROM image_info WHERE (image_type IN (".'$img_type'."))
AND
(subject IN (".'$sub_type'.")) AND (extension IN (".'$ext_type'."))";
*/
//build the query dynamically
$query1="select id from image_info where";
//if img_type optional - then check for it and use if needed
if ($img_type!="na"){
$query1.=" image_type in (".'$img_type'.")";
}
//if sub_type optional - check for it and check if others are set for and clause
if (($sub_type!="na")&&($img_type!="na")){ //img_type is set
$query1.=" and subject in (".'$sub_type'.")";
}else{
$query1.=" subject in (".'$sub_type'.")";
}
//if ext_type optional - check for it and check if others are set for and clause
if (($ext_type!="na")&&(($sub_type!="na")||($img_type!="na"))){
//sub_type or img_type is set
$query1.=" and extension in (".'$ext_type'.")";
}else{
$query1.=" extension in (".'$ext_type'.")";
}
$results1 = mysql_query($query1) or die ("Could not execute query1".mysql_error());
//This returns an array holding all keywords, delimited by commas
$the_search = explode(",",$searchstring);
for ($s=0; $s
{
if ($s==0)
{
$search_item="'%".strtolower(trim($the_search[$s]))."%'";
}else
{
$search_item=",'%".strtolower(trim($the_search[$s]))."%'";
}
}
$query2 = "SELECT master_id FROM keyword WHERE (keywords IN (".'$search_item'."))";
$results2 = mysql_query($query2) or die ("Could not execute query2".mysql_error());
$query3 = "SELECT image_location FROM image_info WHERE $results1 = $results2";
$results3 = mysql_query($query3) or die ("Could not execute query3".mysql_error());
if ($myrow = mysql_fetch_array($results3,MYSQL_ASSOC)) {
Email replies to this thread, to the address above.
??wrote: > > Thanks, Bastien. Your code is bloody brilliant and I > incorporated the error reporting for the queries that you > suggested. Before I did so, the result was always "Sorry, no > records were found!" even though I knew that the criteria I > selected should have yielded several results. Now, when I > again set the form for criteria that should yield results, it > hangs up on query 1 with the error message: "Could not > execute query1Unknown column '$img_type' in 'where clause'" > should the checkboxes be arrays like: type=\"checkbox\" value=\"gif\" name=\"extension[]\"> > > etc? I've gone through it multiple times but can't figure > out why I'm getting this error. > > > error_reporting (E_ALL); > if($_POST['op'] != "search") > { > > //shows the blank search form > > > > "; > > } else > // if($_POST['op'] == "search") > { > > //connect to database > $conn = mysql_connect("localhost", "odbc","") or die("Unable > to connect.".mysql_error()); > > mysql_select_db("shcimages",$conn) or die("Unable to access > the db.".mysql_error()); > > $image_type_request = $_POST['image_type']; > $subject_request = $_POST['subject']; > $extension_request = $_POST['extension']; > $searchstring = $_POST['search_words']; > > //check for image type -- checked with some old code and > this seems to > //be a better way of handling things... > > //are these numbers or alphanumeric strings? (these are > strings) string will require the single > //quote to make the query correct > > > if (count($image_type_request)==1){ > $img_type=$image_type_request; > > }elseif(count($image_type_request) >1 ) { > $img_type=implode(",",$image_type_request); > //turn the results into a comma separate variable > > }else{ > //it's ok if the user does not select a value for each of > the checkbox sets. > //This just makes the search more general > //lets leave it alone and set it to "na" for not applicable > $img_type="na"; > } > > //are these numbers or alphanumeric strings? (these are > strings) string will require the single > //quote to make the query correct > if (count($subject_request)==1){ > $sub_type=$subject_request; > > }elseif(count($subject_request)>1) { > $sub_type=implode(",",$subject_request); > //turn the results into a comma separate variable > > }else{ > //it's ok if the user does not select a value for each of > the checkbox sets. > //This just makes the search more general > //lets leave it alone and set it to "na" for not applicable > $sub_type="na"; > } > > //are these numbers or alphanumeric strings? (these are > strings) string will require the single > //quote to make the query correct > if (count($extension_request)==1){ > $ext_type=$extension_request; > > }elseif(count($extension_request)>1) { > $ext_type=implode(",",$extension_request); > //turn the results into a comma separate variable > > }else{ > //it's ok if the user does not select a value for each of > the checkbox sets. > //This just makes the search more general > //lets leave it alone and set it to "na" for not applicable > $ext_type="na"; > } > > /* > $query1 = "SELECT id FROM image_info WHERE (image_type IN > (".'$img_type'.")) > AND > (subject IN (".'$sub_type'.")) AND (extension IN > (".'$ext_type'."))"; > */ > > //build the query dynamically > $query1="select id from image_info where"; > > //if img_type optional - then check for it and use if needed > if ($img_type!="na"){ > $query1.=" image_type in (".'$img_type'.")"; > } > > //if sub_type optional - check for it and check if others are > set for and clause > if (($sub_type!="na")&&($img_type!="na")){ //img_type is set > $query1.=" and subject in (".'$sub_type'.")"; > > }else{ > $query1.=" subject in (".'$sub_type'.")"; > } > > //if ext_type optional - check for it and check if others are > set for and clause > > if > (($ext_type!="na")&&(($sub_type!="na")||($img_type!="na"))){ > //sub_type or img_type is set > $query1.=" and extension in (".'$ext_type'.")"; > > }else{ > $query1.=" extension in (".'$ext_type'.")"; > } > > > $results1 = mysql_query($query1) or die ("Could not execute > query1".mysql_error()); > > //This returns an array holding all keywords, delimited by > commas > $the_search = explode(",",$searchstring); > for ($s=0; $s { > if ($s==0) > { > $search_item="'%".strtolower(trim($the_search[$s]))."%'"; > }else > { > $search_item=",'%".strtolower(trim($the_search[$s]))."%'"; > } > } > $query2 = "SELECT master_id FROM keyword WHERE (keywords IN > (".'$search_item'."))"; > $results2 = mysql_query($query2) or die ("Could not execute > query2".mysql_error()); > > $query3 = "SELECT image_location FROM image_info WHERE > $results1 = $results2"; > $results3 = mysql_query($query3) or die ("Could not execute > query3".mysql_error()); > > if ($myrow = mysql_fetch_array($results3,MYSQL_ASSOC)) { > > do { > > printf ("Image_location: %s", $myrow["image_location"]); > > } while ($myrow = mysql_fetch_array($results3,MYSQL_ASSOC)); > } else { > $display_block= "Sorry, no records were found!"; > } > } > ?> > > > Image Search > > > print $display_block; ?> > ">??
