?

Search Help Board

PHP FAQ
PHP Articles
PHP Help
Bulletin Board

PHP Manual (NEW!)
First Time PHP'ers
Help with programming
Sql assignment help
PHP Homework Help


C# Help

?
?Re: keywords, checkbox array search
Author:??(---.phnx.uswest.net)
Date:???07-16-03 20:20

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.


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; ?>

?Topics Author? Date
?keywords, checkbox array search??new
Truly 07-05-03 12:47?
?Re: keywords, checkbox array search??new
bastien 07-06-03 07:10?
?Re: keywords, checkbox array search??new
Truly 07-08-03 10:20?
?Re: keywords, checkbox array search??new
bastien 07-09-03 19:00?
?Re: keywords, checkbox array search??new
Truly 07-09-03 20:54?
?Re: keywords, checkbox array search??new
bastien 07-13-03 17:29?
?Re: keywords, checkbox array search??new
bastien 07-15-03 10:37?
?Re: keywords, checkbox array search??new
Truly 07-16-03 20:20?
?Re: keywords, checkbox array search??new
bastien 07-17-03 06:44?
?Re: keywords, checkbox array search??new
Truly 07-20-03 13:57?
?Re: keywords, checkbox array search??new
bastien 07-21-03 17:41?
?Re: keywords, checkbox array search??new
Truly 08-21-03 08:21?
Go to Top??|??Go to Topic??|??Flat View??|??Search?
??|??
New Topic
?Reply To This Message
?Your Name:
?Your Email:
?Subject:
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; ?> > ">??

Provided By
Phorum