[SOLVED] Search function

[whaleyb]

I am looking for some guidance from the experts.

 

I am trying to create a search function.  It will only be searching one table.  The search criteria consists of two drop down menus.  Each menu lists the available options for a specific column in the table.  So if someone chooses an option from both menus, the search would display the results that match both options in a row in the table.  If they choose one, and leave the other blank, it will only display matches for the one choice.  I think that's a good explanation.

 

Anyways, I've got the html side of things taken care of, but I have no idea how to go about getting the results I'm looking for.  This is a bit more complicated than inserting, editing, or deletng data from the database.  Those things I can handle.

 

Someone help a brother out who is looking to dig deeper into PHP.

[johnsmith153]

 

if(selected column A) {

 

$sql = "do a basic select query based on column A";

 

}

 

if(selected column B) {

 

$sql = "do a basic select query based on column B";

 

}

 

if(selected both columns) {

 

$sql = "do a basic select query based on both columns";

 

}

 

 

 

a SELECT SQL query:

$tableName = "myTable";
$sql = sprintf("SELECT * FROM $tableName WHERE columnA LIKE '%s'",mysql_real_escape_string($valA));

 

[whaleyb]

This is what I have so far...........

 

if(isset($_GET['sh_class'])) {

$sql = sprintf("SELECT * FROM $Item_List_tablename WHERE sh_class LIKE '%s'",mysql_real_escape_string($varA));

 }

         if(isset($_GET['sh_slot'])) {

$sql = sprintf("SELECT * FROM $Item_List_tablename WHERE sh_slot LIKE '%s'",mysql_real_escape_string($varB));

              }

         if(isset($_GET['sh_class']) && isset($_GET['sh_slot'])) {

$sql = sprintf("SELECT * FROM $Item_List_tablename WHERE sh_class AND sh_slot LIKE '%s'",mysql_real_escape_string($varA),mysql_real_escape_string($varB));

 }

         $result = mysql_db_query($default_dbname, $sql, $link_id);

         if (!$result) 
{ 
      echo("ERROR: " . mysql_error() . "\n$SQL\n"); 
}
         else {
     echo "$result";
     echo ("Update Successful!\n");
   }

 

Does this look like what you suggested?

[johnsmith153]

If it works, yes.

[whaleyb]

Ok, so it works and it doesn't work.  I'm gonna show the form and the php for this.

 

Form code:

<form method="post" action="<?php echo $php_self ?>">
<input type="hidden" name="action" value="search_items">
<div align="center">
<table border="0" width="15%" cellpadding="2">
<tr>
       <th align="center" width="100%" colspan="2" nowrap><b>Search Items</b></th>
</tr>
<tr>
       <th align="right" width="40%" nowrap>Class:</th>
	<td width="60%">
	            <select name="class">
  					 <option value=""></option>
				 <option value="beastlord">Beastlord</option>
				 <option value="cleric">Cleric</option>
  					 <option value="magician">Magician</option>
  					 <option value="monk">Monk</option>
  					 <option value="necromancer">Necromancer</option>
				 <option value="warrior">Warrior</option>
		</select>
	</td>
</tr>
<tr>
        <th align="right" width="40%" nowrap>Slot:</th>
	 <td width="60%">
		<select name="slot">
  				        <option value=""></option>
				<option value="back">Back</option>
				<option value="charm">Charm</option>
  					<option value="ear">Ear</option>
  					<option value="face">Face</option>
  				        <option value="finger">Finger</option>
				<option value="neck">Neck</option>
				<option value="primary">Primary</option>
				<option value="range">Range</option>
  					<option value="secondary">Secondary</option>
  					<option value="shoulder">Shoulder</option>
  					<option value="waist">Waist</option>
		</select>
	</td>
</tr>
<tr>
         <th align="center" width="100%" colspan="2" nowrap><input type="submit" value="Search"></th>
</tr>
</table>
</div>
</form>

 

PHP code:

 

function search_items() {
global $default_dbname, $Item_List_tablename;
global $name, $item, $itemid;
global $php_self;

$link_id = db_connect($default_dbname);
if(!$link_id) error_message(sql_error());

html_header();

if(isset($_POST['class'])) {

	$query = sprintf("SELECT * FROM $Item_List_tablename WHERE Class LIKE '%s'",mysql_real_escape_string($class));

}

if(isset($_POST['slot'])) {

	$query = sprintf("SELECT * FROM $Item_List_tablename WHERE Slot LIKE '%s'",mysql_real_escape_string($slot));

}

if(isset($_POST['class']) && isset($_POST['slot'])) {

	$query = sprintf("SELECT * FROM $Item_List_tablename WHERE Class AND Slot LIKE '%s'",mysql_real_escape_string($class),mysql_real_escape_string($slot));

}

$result = mysql_query($query);

echo "$result";
echo "<br>";

if (!$result) 
	{ 
	      echo("ERROR: " . mysql_error() . "\n$SQL\n"); 
	}
else {
	echo ("Update Successful!\n");
}

html_footer();
}

 

When you click on search, the page loads and $result echos "Resource id #5", then an echo <br>, then the echo "Update Successfull!"

 

So I'm not getting any errors, but the queries aren't pulling the data they are supposed to.

[johnsmith153]

Your problem is that you are doing

 

echo $result;

 

which displays "Resource number 5" or whatever.

 

doing

if (!$result) 

is fine for checking if mysql did the job, but you need to do more to get the data out:

 

do:

 

if (mysql_num_rows($mysqlRes) != 0) {
   while($row = mysql_fetch_array($mysqlRes)) {
    echo $row[0]."<br>";
    echo $row[1]."<br><br>";
   }
} else {
   echo "Nothing returned.";
}	

 

I am sure you can adjust this to your needs now.

[whaleyb]

Here's my current code one more time.  No matter what I try and do, I cannot get the search to work.  When you hit search it just refreshes the page and it is blank.  No errors, no data, nothing.  I don't even get anything if I add error_reporting to my code.

 

function search_items() {
global $default_dbname, $Item_List_tablename;
        global $php_self;

$link_id = db_connect($default_dbname);
if(!$link_id) error_message(sql_error());

html_header();

if(isset($_POST['class'])) {

	$query = "SELECT * FROM $Item_List_tablename WHERE Class LIKE '%s'";

         }

if(isset($_POST['slot'])) {

	$query = "SELECT * FROM $Item_List_tablename WHERE Slot LIKE '%s'";

}

if(isset($_POST['class']) && isset($_POST['slot'])) {

	$query = "SELECT * FROM $Item_List_tablename WHERE Class AND Slot LIKE '%s'";

}

$result = mysql_query($query);

        $num = mysql_num_rows($result);

$i = 0;
while($i < $num) {
	$class=mysql_result($result,$i,"Class");
	$slot=mysql_result($result,$i,"Slot");

	$i++;
}

html_footer();
}

[mrMarcus]

post the HTML form as well.

 

and, where are you calling for search_items()?

[whaleyb]

The form code is posted a couple replies back, it hasn't changed since I posted it.  The function search_items is being called in the form action.

[mrMarcus]

The form code is posted a couple replies back, it hasn't changed since I posted it.

my bad .. i gotta learn to read the WHOLE thread before responding.

 

The function search_items is being called in the form action.

how so?  how is $php_self calling the function?  what is $php_self anyways?  what has it been defined as?

[whaleyb]

I'm not concerned about the $PHP_SELF call.  I know this works as I use it in other areas of the site with no problems.  I think I'm narrowing down the problem a little.  When you hit the search button, I'm getting the search function to output the choices selected in the dropdown menus.  So it would appear to me, the queries are not working right as they do not select any rows in the table when I am specifically choosing choices that I know are available to list from the table.  So I guess I need to find a new way to query and a loop so I can output all the data.

[whaleyb]

Finally got it working!

 

Thanks for the help guys.