Add search form for mysql database query

[maximus83]

Hi, I'm very new to php and it took all my effort , help and resolve to build the database query below. I was wondering if someone could advise me how to add a search form to this so you can search by ticket number, and email and the results will be returned.

 

Really appreciate any help with this.

 

/* Connect to DB */

$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Sorry, there seems to be a technical problem at the moment - please try again later');
mysql_select_db (DB_NAME) OR die ('Sorry, there seems to be a technical problem at the moment - please try again later');

$_SERVER['REQUEST_URI'] = preg_replace ('!&startoftable=.*!', '', $_SERVER['REQUEST_URI']); // clears query string when viewing anø†her sta†e


/* Write view state control buttons */

echo "<div id=\"shownew\"><table class=\"showhide\"><tr>
      <td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI']). "\"><INPUT TYPE=\"submit\" name=\"showopen\" VALUE=\"\" class=\"show_open\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"showpending\" VALUE=\"\" class=\"show_pending\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"showclosed\" VALUE=\"\" class=\"show_closed\"></form></p></td>";
echo "<td><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URI'])."\"><INPUT TYPE=\"submit\" name=\"show_all\" VALUE=\"\" class=\"show_all\"></form></p></td></tr></table></div>";


/* Status set code */

if (isset($_POST['close'])) $_SERVER['REQUEST_URI'] = preg_replace ('!&startoftable=.*!', '', $_SERVER['REQUEST_URI']); {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_dev SET status = 'Closed', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['open'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_dev SET status = 'Open', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_dev SET status = 'Pending', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending_ami'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_dev SET status = 'Pending AMI', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
}

if (isset($_POST['pending_arp'])) {
    $close_row = $_POST['close_row'];
    $close = "UPDATE support_dev SET status = 'Pending ARP', date_altered = NOW() WHERE ticket_number=$close_row";
    $closeresult = @mysql_query ($close);
    $viewstate = $_POST['viewstate'];
    
}

/* View state set code */

if (isset($_POST['showopen'] ) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'open' )) {
    $viewstate='open';
}

if (isset($_POST['showpending']) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'pending')) {
    $viewstate='pending';
}


if (isset($_POST['showclosed']) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'closed')) {
    $viewstate='closed';
}

if (isset($_POST['show_all']) || (isset($_GET['viewstate']) && $_GET['viewstate'] == 'all')) {
    $viewstate='all';
    
}


function supportquery($viewstate) {
    
$display = 6;// number of*results per page

        if (isset($_GET['np'])) {
    $num_pages = $_GET['np'];
} else {
    
    $where = "1"; // default
    switch ($viewstate) {
        case "open": $where = "status='Open'"; break;
        case "pending": $where = "status LIKE 'Pending%'"; break;
        case "closed": $where = "status='Closed'"; break;
    }
    
    $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop,
        message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE $where ORDER BY ticket_number DESC";
    $query_result = mysql_query ($query);
    $num_records = @mysql_num_rows ($query_result);
    if ($num_records > $display) {
        $num_pages = ceil ($num_records/$display);
    } else {
        $num_pages = 1;
    }
}


if (isset($_GET['startoftable'])) {
    $start = $_GET['startoftable'];
} else {
    $start = 0;// start of results from row 0 in table

}

    $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev ORDER BY ticket_number DESC LIMIT $start, $display";
    if ($viewstate=='open') {
    
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Open' ORDER BY ticket_number DESC LIMIT $start, $display";
        }
            elseif ($viewstate=='pending') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status LIKE 'Pending%' ORDER BY ticket_number DESC LIMIT $start, $display";
        }
        elseif ($viewstate=='closed') {
        $query = "SELECT ticket_number, first_name, surname, email, product, retailer, DATE_FORMAT(dop, '%d %M %Y') AS dop, message, address, DATE_FORMAT(created, '%d %M %Y %r') AS created, status FROM support_dev WHERE status='Closed' ORDER BY ticket_number DESC LIMIT $start, $display";}
        $result = @mysql_query ($query);
        $num = mysql_num_rows ($result);
    
if ($num > 0) {
    
    if ($num_pages > 1) {
        echo '<div class="pagination"><p>';
        $current_page = ($start/$display) + 1;
        
        if ($current_page != 1) {
            echo '<a href="?page_id=1072&startoftable=' . (0) . '&np=' . $num_pages . '&viewstate=' . $viewstate .'">First</a> ';  
            echo '<a href="?page_id=1072&startoftable=' . ($start - $display) . '&np=' . $num_pages . '&viewstate=' . $viewstate . '"><</a> ';
        }
    
    
    
    // range of num links to show
$range = 4;

// loop to show links to range of pages around current page
for ($i = ($current_page - $range); $i < (($current_page + $range)  + 1); $i++) {
   // if it's a valid page number...
   if (($i > 0) && ($i <= $num_pages)) {
      // if we're on current page...
      if ($i == $current_page) {
         // 'highlight' it but don't make a link
         echo " [<b>$i</b>] ";
      // if not current page...
      } else {
         // make it a link
     echo ' <a href="?page_id=1072&startoftable=' . (($display * ($i - 1))) . '&np=' . $num_pages . '&viewstate=' . $viewstate .'">' .$i .'</a> ';
        
      }}}    
        
        if ($current_page != $num_pages) {
        
            echo '<a href="?page_id=1072&startoftable=' . ($start + $display) . '&np=' . $num_pages . '&viewstate=' . $viewstate .'">></a> ';
            echo '<a href="?page_id=1072&startoftable=' . ( $display * $num_pages-$display ) . '&np=' . $num_pages . '&viewstate=' . $viewstate .'">Last</a> ';
        }
        
        echo '</p></div> ';

    }
    

     if ($result) {
         echo '
    <div id="supviewwrapperheader">
    <div id="supviewticket"><p>Ticket</p></div>
    <div id="supviewfirst"><p>First Name</p></div>
    <div id="supviewlast"><p>Last Name</p></div>
    <div id="supviewemail"><p>Email</p></div>
    <div id="supviewproduct"><p>Product</p></div>
    <div id="supviewretailer"><p>Retailer</p></div>
    <div id="supviewdop"><p>D.O.P.</p></div>
    <div id="supviewmessage"><p>Message</p></div>
    <div id="supviewaddress"><p>Address</p></div>
    <div id="supviewcreated"><p>Date created</p></div>
    <div id="supviewstatus"><p>Status</p></div>
    <div id="supviewbuttons"><p></p></div>
    </div>
    ';
    
    while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
        echo "
        <div id=\"supviewwrapper\" class=\"".($row[10])."\">
        <div id=\"supviewticket\"><p><a name=\"a".$row[0]."\"></a>$row[0]</p></div>
        <div id=\"supviewfirst\"><p>$row[1]</p></div>
        <div id=\"supviewlast\"><p>$row[2]</p></div>
        <div id=\"supviewemail\"><p>$row[3]</p></div>
        <div id=\"supviewproduct\"><p>$row[4]</p></div>
        <div id=\"supviewretailer\"><p>$row[5]</p></div>
        <div id=\"supviewdop\"><p>$row[6]</p></div>
        <div id=\"supviewmessage\"><p>$row[7]</p></div>
        <div id=\"supviewaddress\"><p>$row[8]</p></div>
        <div id=\"supviewcreated\"><p>$row[9]</p></div>
        <div id=\"supviewstatus\"><p>$row[10]</p></div>
        <div id=\"supviewbuttons\"><p><form method=\"post\" action=\"".htmlentities($_SERVER['REQUEST_URL'])."#a".$row[0]."\"><input type=\"hidden\" name=\"close_row\" value=\"".($row[0])."\" /><input type=\"hidden\" name=\"viewstate\" value=\"".$viewstate."\" /><INPUT TYPE=\"submit\" name=\"open\" VALUE=\"\" class=\"submit_open\"><br /><INPUT TYPE=\"submit\" name=\"pending\" VALUE=\"\" class=\"submit_pending\"><br /><INPUT TYPE=\"submit\" name=\"pending_ami\" VALUE=\"\" class=\"submit_pendingami\"><br /><INPUT TYPE=\"submit\" name=\"pending_arp\" VALUE=\"\" class=\"submit_pendingarp\"><br /><INPUT TYPE=\"submit\" name=\"close\" VALUE=\"\" class=\"submit_closed\"></form></p></div></div>";
    }
     }
    
    mysql_free_result ($result);
    
     }    



if (is_null($viewstate)) {
    $viewstate='all';
}

}

supportquery($viewstate); 

[the182guy]

You haven't given enough information for someone to be able to help you. Describe what the script does currently and give more info on what you want to achieve.

[maximus83]

Sorry, at the moment the script calls a database that is made up of rows with four different states, open, pending , closed and all, you are able to click buttons at the each of each row to change the status. there is also a filter to display each different state, all the results are also paginated to ten per page.

 

I now want to add a search box that will allow the user to search by email address and ticket number, so that only results that match the search will be shown this should also keep the pagination if results exceed the page limit of 10.

 

Hope this is enough information and is much clearer.