Query is giving me gray hair

[Anon]

Hello I am trying to write a query that will take an variable from a form that has three fields: State drop down , text box and another drop down that has Entire Database, Book Title, Book Author and Book ISBN in it.

I then send all of the variables to my search script to bring back the results however I am having troubles getting every combination to work maybe one of the super php coders can help...here is the code I have:

<?php

$user = 'x';
$password = 'xxx';
$db = 'xxxx';

mysql_connect('64.156.138.234', $user, $password);

@mysql_select_db($db) or DIE('Unable to open database');

if ($search_in == "Entire Database" && $search_state== "") {
$query = "SELECT * FROM add_info where Book_name LIKE '%$search_text%'
OR Book_ISBN LIKE '%$search_text%' ";
}

else if ($search_in == "Entire Database" && $search_state) {
$query = "SELECT * FROM add_info where state= '$search_state' and (Book_name LIKE '%$search_text%'
OR Book_ISBN LIKE '%$search_text%' )";
}

else if ($search_in == "Book by Title" && $search_state) {
$query = "SELECT * FROM add_info where state= '$search_state' and (Book_name LIKE '%$search_text%' )";
}

else if ($search_in == "Book by Author" && $search_state) {
$query = "SELECT * FROM add_info where state= '$search_state' and (Book_author LIKE '%$search_text%' )";
}

else if ($search_in == "Book by ISBN" && $search_state) {
$query = "SELECT * FROM add_info where state= '$search_state' and (Book_ISBN LIKE '%$search_text%' )";
}

else if ($search_in == "Entire Database" ) {
$query = "SELECT * FROM add_info where Book_name LIKE '%$search_text%'
OR Book_ISBN LIKE '%$search_text%' ";

} else if ($search_in == "Book by Title"){
$query = "SELECT * FROM add_info where Book_name LIKE '%$search_text%'";

} else if ($search_in == "Book by Author") {
$query = "SELECT * FROM add_info where Book_author LIKE '%$search_text%'";

} else if ($search_in == "Book by ISBN") {
$query = "SELECT * FROM add_info where Book_ISBN LIKE '%$search_text%'";

}

$result = mysql_query($query);

while ($results = mysql_fetch_array($result)) {
echo("<TR>\n");
$id =$results["id"];
$first =$results["first"];
$email =$results["email"];
$city =$results["city"];
$state =$results["state"];
$college =$results["college"];
$phone =$results["phone"];
$Book_name =$results["Book_name"];
$Book_author =$results["Book_author"];
$Book_ISBN =$results["Book_ISBN"];
$Book_subject =$results["Book_subject"];
$Book_price =$results["Book_price"];

$first = stripslashes($first);
$email = stripslashes($email);
$city = stripslashes($city);
$state = stripslashes($state);
$college = stripslashes($college);
$phone = stripslashes($phone);
$Book_name = stripslashes($Book_name);
$Book_author = stripslashes($Book_author);
$Book_ISBN = stripslashes($Book_ISBN);
$Book_subject = stripslashes($Book_subject);
$Book_price = stripslashes($Book_price);

echo("<TD><a href='search_book.php?id=$id'>$Book_name</TD>\n");

echo("<td>$college</td>\n");
echo("<td>$state</td>\n");
echo("<td>$Book_price</td>\n");

echo("</TR>\n");

}
?>

[cordex]

//You could try changing your query section to something like this:

trim($search_in);
if($search_state)
{
switch($search_in)
{
case "Entire Database":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_name LIKE '%$search_text%' OR Book_ISBN LIKE '%$search_text%' )";
break;
case "Book by Title":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_name LIKE '%$search_text%')";
break;
case "Book by Author":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_author LIKE '%$search_text%' )";
break;
case "Book by ISBN":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_ISBN LIKE '%$search_text%')";
break;
default:
echo "Error: search_in contains invalid data";
break;
}
}
else
{
switch($search_in)
{
case "Entire Database":
$query = "SELECT FROM add_info where Book_name LIKE '%$search_text%' OR Book_ISBN LIKE '%$search_text%' ";
break;
case "Book by Title":
$query = "SELECT FROM add_info where Book_name LIKE '%$search_text%'";
break;
case "Book by Author":
$query = "SELECT FROM add_info where Book_author LIKE '%$search_text%'";
break;
case "Book by ISBN":
$query = "SELECT FROM add_info where Book_ISBN LIKE '%$search_text%'";
break;
default
echo "Error: search_in contains invalid data";
break;
}
}

//Notice, this will tell you when you are having problems with your query by displaying an error message.
//
//-Ben

[Anon]

Hey Ben.....

Thanks that worked!!!! I however forgot that in the state drop down it defaults to all so I thouhgt I would just add an:

trim($search_in);
elseif($search_state =="all")
{
switch($search_in)
{
case "Entire Database":
$query = "SELECT FROM add_info where Book_name LIKE '%$search_text%' OR Book_ISBN LIKE '%$search_text%' )";
break;
case "Book by Title":
$query = "SELECT FROM add_info where (Book_name LIKE '%$search_text%')";
break;
case "Book by Author":
$query = "SELECT FROM add_info where(Book_author LIKE '%$search_text%' )";
break;
case "Book by ISBN":
$query = "SELECT FROM add_info where(Book_ISBN LIKE '%$search_text%')";
break;
default:
echo "Error: search_in contains invalid data";
break;
}
}

However it does not work if I leave the state selected to all and say enter in an ISBN nunmber it does not bring anything back?

These questions might seem easy for you but for a rookie it can be nerve wrecking!!!

Thanks again....
Khris

[Anon]

Can anyone help with this.....

Thanks!!!

[cordex]

snip
trim($search_in);
elseif($search_state =="all")
{
switch($search_in)
{
snip

This will cause problems. The trim($search_in); needs to be outside of the if-elseif structure. Placed where it is, it will interfere with the flow of the if structure.

You do have an if statement above your elseif, right?

[Anon]

So I treid this and it doesn't seem to work am I just missing somthing here?

trim($search_in);
if($search_state)
{
switch($search_in)
{
case "Entire Database":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_name LIKE '%$search_text%' OR Book_ISBN LIKE '%$search_text%' )";
break;
case "Book by Title":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_name LIKE '%$search_text%')";
break;
case "Book by Author":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_author LIKE '%$search_text%' )";
break;
case "Book by ISBN":
$query = "SELECT FROM add_info where state= '$search_state' and (Book_ISBN LIKE '%$search_text%')";
break;
default:
echo "Error: search_in contains invalid data";
break;
}
}

elseif($search_state == "all" && $search_in)
{
switch($search_in)
{
case "Entire Database":
$query = "SELECT FROM add_info where (Book_name LIKE '%$search_text%' OR Book_ISBN LIKE '%$search_text%' )";
break;
case "Book by Title":
$query = "SELECT FROM add_info where (Book_name LIKE '%$search_text%')";
break;
case "Book by Author":
$query = "SELECT FROM add_info where(Book_author LIKE '%$search_text%' )";
break;
case "Book by ISBN":
$query = "SELECT FROM add_info where(Book_ISBN LIKE '%$search_text%')";
break;
default:
echo "Error: search_in contains invalid data";
break;
}
}

else
{
switch($search_in)
{
case "Entire Database":
$query = "SELECT FROM add_info where Book_name LIKE '%$search_text%' OR Book_ISBN LIKE '%$search_text%' ";
break;
case "Book by Title":
$query = "SELECT FROM add_info where Book_name LIKE '%$search_text%'";
break;
case "Book by Author":
$query = "SELECT FROM add_info where Book_author LIKE '%$search_text%'";
break;
case "Book by ISBN":
$query = "SELECT FROM add_info where Book_ISBN LIKE '%$search_text%'";
break;
default
echo "Error: search_in contains invalid data";
break;
}
}

Thanks again for all fo your help

[cordex]

Look at your if structure:

if($search_state)
- this says, "If there is a value in $search_state, we do these things"

elseif($search_state == "all" && $search_in)

• now, this says "if there isn't a value in $search_state (as we have passed the above statement) AND $search_state contains the value 'all' AND there is a value in $search_in, then we do these things" This set of instructions will never be reached.

else
- "Otherwise, do this"

That is your problem, or at least one of them. What kind of error are you getting?