[SOLVED] Help with Switch code for search page - Newbie

[snteran]

I had made a very simple search page about a year ago and now I'm trying to add a couple of things and the first step was to add the ability to search by user.  I thought I would use a switch command to select the query and then fill my table based off of the selection. 

 

Here is the form:

 

<form method="post" action="search_verify2.php">

<table border="1" cellpadding="0" cellspacing="0" width="400" bordercolor="#6600ff">

<tr>

<td align="center" class="style9"><input type="radio" name="search" value="keyword"></td>

    <td align="left" class="style9">Search a word:</td>

    <td valign='top' align="left"><input type='text' name="keyword" id="keyword" size=26 maxlength=56></td>

</tr>

<tr>

<td align="center" class="style9"><input type="radio" name="search" value="user"></td>

    <td align='left' class="style9">Search by User:</td>

<td ><select name="issued_by" id="issued_by">

                  <?php

do { 

?>

                  <option value="<?php echo $row_itc_users['issued_by']?>"><?php echo $row_itc_users['issued_by']?></option>

                  <?php

} while ($row_itc_users = mysql_fetch_assoc($itc_users));

  $rows = mysql_num_rows($itc_users);

  if($rows > 0) {

      mysql_data_seek($itc_users, 0);

  $row_itc_users = mysql_fetch_assoc($itc_users);

  }

?>

                </select></td>

</tr> 

<tr>

<td align="center" class="style9"><input type="radio" name="search" value="category"></td>

    <td align="left" class="style9">Search a Category:</td>

    <td valign='top' align="left"><select name="ticket_category" id="ticket_category">

                    <option value="Custom">Custom</option>

                    <option value="Email">Email</option>

                    <option value="Hardware">Hardware</option>

                    <option value="Internet">Internet</option>

                    <option value="Network">Network</option>

                    <option value="Office App">Office Application</option>

                    <option value="OS">Operating System</option>

                    <option value="Printer">Printer</option>

                    <option value="Solomon">Solomon</option>

<option value="Phone">Phone / XO</option>

                    <option value="Other">Other</option></td>

</tr>

<tr>

    <td align='center'> </td>

<td align="center" colspan="2"><br /><input type='submit' value='Go Find'></td>

</tr>

</table>

</form>

 

The form appears on the website fine, and then when I process I keep getting errors:

 

Notice: Undefined index: search in C:\AMP\Apache2\htdocs\test\tickets\search_verify2.php on line 26

 

Notice: Undefined variable: query_asset_details in C:\AMP\Apache2\htdocs\test\tickets\search_verify2.php on line 43

Query was empty

 

Here is the info on my search_verify2.php page -

 

<?php

// verify user has proper clearence

require_once($_SERVER['DOCUMENT_ROOT'].'/test/Connections/itc_admin.php');

@session_start();

$errorMessage = null;

 

?>

<?php

$keyword = $_POST['keyword'];

$category = $_POST['ticket_category'];

$user = $_POST['issued_by'];

$search = $_POST['search'];

 

 

// The below query is used for category searches

switch($search)

{

case 'category' :

$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.ticket_category = '$category' AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";

break;

case 'keyword' :

$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $keyword AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";

break;

case 'user':

// The below query is for user searches

$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.issued_by LIKE $user AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";

break;

}

$asset_details = mysql_query($query_asset_details, $itc_admin) or die(mysql_error());

$row_asset_details = mysql_fetch_assoc($asset_details);

if (!$row_asset_details) {

  // Do something when data wasn't found

  $errorMessage .="No tickets found for that search criteria.<br>";

  include 'search.php'; // Show the form again!

  exit;

}

$totalRows_asset_details = mysql_num_rows($asset_details);

 

?>

[ryeman98]

Just saying... It would be a lot easier to read if the code was contained within the code BBcode

[snteran]

Form code:

 

<form method="post" action="search_verify2.php">
      <table border="1" cellpadding="0" cellspacing="0" width="400" bordercolor="#6600ff">
         <tr>
            <td align="center" class="style9"><input type="radio" name="search" value="keyword"></td>
             <td align="left" class="style9">Search a word:</td>
              <td valign='top' align="left"><input type='text' name="keyword" id="keyword" size=26 maxlength=56></td>
         </tr>
         <tr>
            <td align="center" class="style9"><input type="radio" name="search" value="user"></td>
             <td align='left' class="style9">Search by User:</td>
            <td ><select name="issued_by" id="issued_by">
                  <?php
do { 
?>
                  <option value="<?php echo $row_itc_users['issued_by']?>"><?php echo $row_itc_users['issued_by']?></option>
                  <?php
} while ($row_itc_users = mysql_fetch_assoc($itc_users));
  $rows = mysql_num_rows($itc_users);
  if($rows > 0) {
      mysql_data_seek($itc_users, 0);
     $row_itc_users = mysql_fetch_assoc($itc_users);
  }
?>
                </select></td>
         </tr> 
         <tr>
            <td align="center" class="style9"><input type="radio" name="search" value="category"></td>
             <td align="left" class="style9">Search a Category:</td>
              <td valign='top' align="left"><select name="ticket_category" id="ticket_category">
                    <option value="Custom">Custom</option>
                    <option value="Email">Email</option>
                    <option value="Hardware">Hardware</option>
                    <option value="Internet">Internet</option>
                    <option value="Network">Network</option>
                    <option value="Office App">Office Application</option>
                    <option value="OS">Operating System</option>
                    <option value="Printer">Printer</option>
                    <option value="Solomon">Solomon</option>
               <option value="Phone">Phone / XO</option>
                    <option value="Other">Other</option></td>
         </tr>
         <tr>
             <td align='center'> </td>
            <td align="center" colspan="2">
<input type='submit' value='Go Find'></td>
         </tr>
      </table>
         </form>

 

verify code

 

<?php
// verify user has proper clearence
require_once($_SERVER['DOCUMENT_ROOT'].'/test/Connections/itc_admin.php');
@session_start();
$errorMessage = null;

?>
<?php
$keyword = $_POST['keyword'];
$category = $_POST['ticket_category'];
$user = $_POST['issued_by'];
$search = $_POST['search'];


// The below query is used for category searches
switch($search)
{
   case 'category' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.ticket_category = '$category' AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
   case 'keyword' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $keyword AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
   case 'user':
// The below query is for user searches
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.issued_by LIKE $user AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
}
$asset_details = mysql_query($query_asset_details, $itc_admin) or die(mysql_error());
$row_asset_details = mysql_fetch_assoc($asset_details);
if (!$row_asset_details) {
   // Do something when data wasn't found
   $errorMessage .="No tickets found for that search criteria.
";
   include 'search.php'; // Show the form again!
   exit;
}
$totalRows_asset_details = mysql_num_rows($asset_details);

?>

 

Sorry about that, will make sure to add code correctly next time.

[snteran]

OK, well I figured out the first part of my issue.  In my initial search page, I have two sections, first is the radio button and then I have the select criteria.  I did not have a default radio button selected.  However, I would like to find a dynamic way of switching the radio button based off of information entered in the select criteria section.  Moving on with the solution, or first part, I then added the checked parameter for the radio button and then found my issue in my php code.

 

In my select statements I did not have single quotes around two of my variables.  I was able to figure this out, very nice feeling.

 

<?php
$keyword = $_POST['keyword'];
$category = $_POST['ticket_category'];
$user = $_POST['issued_by'];
$search = $_POST['search'];

$val = "'%$keyword%'";
//print_r($val);

//print_r($category);
// The below query is used for category searches
switch($search)
{
case 'category' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.ticket_category = '$category' AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
case 'keyword' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $val AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
case 'user':
// The below query is for user searches
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.issued_by LIKE '$user' AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
}
$asset_details = mysql_query($query_asset_details, $itc_admin) or die(mysql_error());
$row_asset_details = mysql_fetch_assoc($asset_details);
if (!$row_asset_details) {
   // Do something when data wasn't found
   $errorMessage .="No tickets found for that search criteria.<br>";
   include 'search.php'; // Show the form again!
   exit;
}
$totalRows_asset_details = mysql_num_rows($asset_details);

?>

 

However I do have another question to one of my switch statements.

 

In our ticket system, we store our initial tickets in one table and then any additional notes in another table, I would like to first query the main section and if there is a NULL result to then query the notes table.

 

Keyword search.

 

case 'keyword' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $val AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;

 

Thanks,

[Dragen]

<?php
case 'keyword' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $val AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
if($result = mysql_query($query_asset_details)){
    if(mysql_num_rows($result) > 0){
        //you've found the data in the first table.. do something with it.
    }else{
        //no data was found in the first table, now check the second table
    }
}else{
    echo 'ERROR: ' . mysql_error()
}
break;
?>

[snteran]

It just seemed so obvious....I got a long way to go.

 

Thanks

 

<?php
case 'keyword' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $val AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
if($result = mysql_query($query_asset_details)){
    if(mysql_num_rows($result) > 0){
        break;
    }else{
        $query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing, itc_tickets_notes WHERE itc_tickets_notes.notes_body LIKE $val AND itc_tickets.ticket_nbr = itc_tickets_notes.ticket_nbr AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
    }
} 
break;
?>

 

[Dragen]

That code won't work. You've got a break; in the middle of your mysql statement.

You'd need something more like this:

case 'keyword' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $val AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
if($result = mysql_query($query_asset_details)){
    if(mysql_num_rows($result) > 0){
        //insert code here to be output if it finds data.. i.e:
        while($row = mysql_fetch_assoc($result)) {
                echo $row['itc_tickets.ticket_nbr'];
        }
    }else{
        $query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date FROM itc_tickets, itc_priority_listing, itc_status_listing, itc_tickets_notes WHERE itc_tickets_notes.notes_body LIKE $val AND itc_tickets.ticket_nbr = itc_tickets_notes.ticket_nbr AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
	 if($result = mysql_query($query_asset_details)){
		if(mysql_num_rows($result) > 0){
			//insert code here to be output if it finds data in second table.. i.e:
			while($row = mysql_fetch_assoc($result)) {
              	  echo $row['itc_tickets.ticket_nbr'];
        		}
    		}else{
        		echo 'no data found';
    		}
	 }else{
	 	echo mysql_error();
	 }
}
}else{
	echo .mysql_error();
}
break;
?>

[snteran]

I had to revisit my code and wanted to make sure I posted the final copy of code that I am using in production.

 

$search = $_POST['search'];

$val = "'%$keyword%'";

// The below query is used for category searches
switch($search)
{
case 'category' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date, itc_tickets.description FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.ticket_category = '$category' AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
case 'keyword' :
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date, itc_tickets.description FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.description LIKE $val AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
if($result = mysql_query($query_asset_details)){
    if(mysql_num_rows($result) > 0){
        break;
    }else{
        $query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date, itc_tickets.description FROM itc_tickets, itc_priority_listing, itc_status_listing, itc_tickets_notes WHERE itc_tickets_notes.notes_body LIKE $val AND itc_tickets.ticket_nbr = itc_tickets_notes.ticket_nbr AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
    }
} 
break;
case 'user':
// The below query is for user searches
$query_asset_details="SELECT itc_tickets.ticket_nbr, itc_tickets.ticket_status, itc_tickets.ticket_category, itc_tickets.ticket_priority, itc_tickets.issued_by, itc_tickets.assigned_to, itc_tickets.lastupdt_date, itc_tickets.create_date, itc_tickets.description FROM itc_tickets, itc_priority_listing, itc_status_listing WHERE itc_tickets.issued_by LIKE '$user' AND itc_tickets.ticket_priority = itc_priority_listing.priority_name AND itc_tickets.ticket_status = itc_status_listing.status_name ORDER BY itc_status_listing.status_level ASC, itc_tickets.create_date DESC, itc_priority_listing.priority_level ASC, itc_tickets.ticket_nbr ASC";
break;
}
$asset_details = mysql_query($query_asset_details, $itc_admin) or die(mysql_error());
$row_asset_details = mysql_fetch_assoc($asset_details);
if (!$row_asset_details) {
   // Do something when data wasn't found
   $errorMessage .="No tickets found for that search criteria.<br>";
   include 'search.php'; // Show the form again!
   exit;
}
$totalRows_asset_details = mysql_num_rows($asset_details);

$description = ($row_asset_details['description']);