Jump to content

Add search form for mysql database query

maximus83

By maximus83
in PHP Coding Help

 Share

Recommended Posts

maximus83 Newbie

maximus83

Posted
Posted

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);

maximus83 Newbie

maximus83

Posted
  • Author
Posted

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.