Simple search script it not working??

[Cep]

???

 

I really don't get this, I have a simple demo script setup to search two fields which then search a table. The table has two records,

 

Record 1 = Field1 is Cep, Field2 is moo

Record 2 = Field1 is Cepeleon, Field2 is moocow

 

When the user enters Cep into field1's text box the sql runs,

 

SELECT * FROM new WHERE field1 LIKE 'cep'

 

However it returns 0 results saying that the query is empty?

 

If I run this exact same query in PHPmyAdmin, I get the result. I don't get it?

 

<?php
// Connection settings
$host = "****";
$user = "*****";
$pass = "*****";
$dbase = "*****";

// Connecting and selecting database
$db = mysql_connect($host, $user, $pass) or die("Could not connect: ".mysql_error());

mysql_select_db($dbase) or die("Could not select database");

// Place form field values into variables
if (isset($_POST['one'])) { $fld1 = $_POST['one']; } else { $fld1 = ""; }
if (isset($_POST['two'])) { $fld2 = $_POST['two']; } else { $fld2 = ""; }

// Function for Search
/***************************************** search_fields function ********************************************/
function search_fields($field1, $field2) {

         $sql = "SELECT * FROM new";
         
         $likes = array($field1, $field2);

         echo

         $fname = "";
         
         $no_fields = 0;

         for ($i = 0; $i <= 2; $i++) {

           switch ($i) {
             case 0:
                  $fname = "field1";
                  break;
             case 1:
                  $fname = "field2";
                  break;
           }

           if ($likes[$i]!="") {

              if ($no_fields==0) {
                 $sql = $sql." WHERE {$fname} LIKE '{$likes[$i]}'";
              } else {
                 $sql = $sql." OR {$fname} LIKE '{$likes[$i]}'";
              }
              $no_fields++;

           } else {

             // Escape the query if no records are found
             if ($no_fields==0) {
              $sql = $sql." WHERE field1 = 'davey jones locker'";
             }
             $no_fields++;

           }
         }
         
         $result = mysql_query($query) or die("SQL Error: Function search_fields - unable to select<br /><br />SQL = {$sql}<br /><br />".mysql_error());
         
         $num_rows = mysql_num_rows($result);

         $options = "";

         switch ($num_rows) {
                case 0:
                       // No results found
                       $options = "No results found matching search criteria
                                   <br />
                                   <br />
                                   Field1 = {$field1}
                                   <br />
                                   Field2 = {$field2}
                                   <br />
                                   <br />";
                       break;
                case 1:
                      // Single result found
                      $row = mysql_fetch_array($result, MYSQL_ASSOC) or die("SQL Error: Function search_fields - unable to fetch array single row<br /><br />".mysql_error());

                      $options = "<form name=\"search\" method=\"post\" action=\"new.php\">"
                                 .$row['ID']." ".$row['field1']." ".$row['field2']."
                                 <br />
                                 <br />
                                 </form>";
                      break;
                default:
                      // Multiple results found
                      $options = "<form name=\"search\" method=\"post\" action=\"new.php\">
                                  <select name='sel_result' size='{$num_rows}'>";

                      for ($i = 1; $i <= $num_rows; $i++) {
                        $row = mysql_fetch_array($result, MYSQL_ASSOC) or die("SQL Error: Function search_fields - unable to fetch array row {$i}<br /><br />".mysql_error());

                        $options .= "<option value='{$row['ID']}'>{$row['field1']} {$row['field2']}</option>";
                      }

                      $options .= "</select>
                                   <br />
                                   <br />
                                   </form>";
                      break;
         }
return $options;
}
/****************************************************************************************/

// Test function
$display = search_fields($fld1, $fld2);

$html = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html>\n<head>\n<title>Search</title>\n</head><body>';

$html = $html.$display."</body>\n</html>";

echo $html;

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($db);
?>

[chocopi]

I think you need the % sign so you would have

 

SELECT * FROM new WHERE field1 LIKE 'cep%'

[only one]

where are the feild1, feild2 variables set??

 

if (isset($_POST['one'])) { $fld1 = $_POST['one']; } else { $fld1 = ""; }

if (isset($_POST['two'])) { $fld2 = $_POST['two']; } else { $fld2 = ""; }

 

is that not meant to be

 

if (isset($_POST['one'])) { $feild1 = $_POST['one']; } else { $feild1 = ""; }

if (isset($_POST['two'])) { $feild2 = $_POST['two']; } else { $feild2 = ""; }

[Cep]

They are called on this simple html form,

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Search Fields</title>
</head>
<body>
<form name="form" method="post" action="new.php">
Field 1 <input name="one" type="text" size="10" />
<br />
<br />
Field 2 <input name="two" type="text" size="10" />
<br />
<br />
<input name="search" type="submit" value="Search" />
</form>
</body>
</html>

 

And the statements are correct as I call the function with, $display = search_fields($fld1, $fld2);

[Cep]

@chocopi

 

That does not appear to make a difference. It just seems strange I can run this query in phpmyadmin and get a result yet here I cannot. The connection details reference the correct database and table so I am a little bemused.

 

 

[GingerRobot]

I suggest you echo the contents of the query string after you attempt to do the query:

 

$result = mysql_query($query) or die("SQL Error: Function search_fields - unable to select<br /><br />SQL = {$sql}<br /><br />".mysql_error());
echo 'SQL: '.$sql'<br />';

 

That way you can see exactly what is being passed into the query. I susepect there is a problem with a variable, and while it may not cause a syntax error and thus a mysql error, it will cause your query to function incorrectly.

[Cep]

Hi Ben

 

You mean echo it twice?

 

I already echo the SQL statement as part of the die statement

 

<br /><br />SQL = {$sql}<br /><br />