Click here to Skip to main content
15,847,748 members
Home / Discussions / Linux, Apache, MySQL, PHP
   

Linux, Apache, MySQL, PHP

 
AnswerRe: SQL Pin
syed shanu22-Mar-15 23:28
mvasyed shanu22-Mar-15 23:28 
QuestionHow to expire a session? Pin
Jassim Rahma20-Mar-15 9:14
Jassim Rahma20-Mar-15 9:14 
AnswerRe: How to expire a session? Pin
speda127-Sep-15 14:15
speda127-Sep-15 14:15 
QuestionHow to Delete file when Selected from Dropdown List Pin
Member 1150850510-Mar-15 21:09
Member 1150850510-Mar-15 21:09 
QuestionRe: How to Delete file when Selected from Dropdown List Pin
Richard MacCutchan10-Mar-15 23:08
mveRichard MacCutchan10-Mar-15 23:08 
AnswerRe: How to Delete file when Selected from Dropdown List Pin
Member 1150850510-Mar-15 23:11
Member 1150850510-Mar-15 23:11 
GeneralRe: How to Delete file when Selected from Dropdown List Pin
Richard MacCutchan11-Mar-15 0:10
mveRichard MacCutchan11-Mar-15 0:10 
QuestionDisplay results based on dropdown selected dropdown value Pin
samflex10-Mar-15 8:47
samflex10-Mar-15 8:47 
Dear php gurus,

This is my second post on this php forum. Not much luck with my first post.

First, please bear with me as this code is rather long.

I have this dropdown dynamically populated from SQL Server database. The code below is derived from VIEW -> SOURCE
XML
<select name="SType">
  <option value=""></option>
  <option value="Bid" name="Bid">Bid[2]</option>
  <option value="Lib CM" name="Library CM">Lib CM[23]</option>
  <option value="Quote" name="Quote">Quote[20]</option>
  <option value="RFP" name="RFP">RFP[2]</option>
  <option value="Removed" name="Removed">Removed[29]</option>
  <option value="Archived" name="Archived">Archived[4501]</option>
  <option value="Awarded" name="Awarded">Awarded[40]</option>
  <option value="Open" name="Open">Open[47]</option>
  <option value="Closed" name="Closed">Closed[11]</option>
  <option value="Under Review" name="Under Review">Under Review[126]</option>
  <option value="Cancelled" name="Cancelled">Cancelled[64]</option>
</select>


When a user selects any value, say Bid, we would like to display all records associated with Bid. In this case, it will be two records.

The long code below (sorry again), works perfectly in terms of displaying records.

However, when I tried to integrate the code that displays records based on dropdown value, I expected to see just those records.

Instead, it displays all records.

Can you please see what I am doing wrong?

First the code that should display records based on dropdown selection, followed by the long code I am trying to integrate it with.

Your help and understanding very much appreciated.

PHP
//If value matches dropdown value, then display records associated with dropdown value
if(isset($_REQUEST['Solicitations'])){
    if($_GET['Solicitations'] == "Bid"){
    $result = "SELECT * FROM bids where BidStatus = 1 and BidType = 'Bid' ORDER BY title";
    $query = sqlsrv_query( $conn, $sql , $params, $options );
    }
  }


XML
<?php
error_reporting(E_ERROR | E_WARNING | E_PARSE);

?>

<?php
// Connect to SQL Server database
include("connections/CWDevConnect.php");

// this function is used to sanitize code against sql injection attack.
function ms_escape_string($data) {
        if ( !isset($data) or empty($data) ) return '';
        if ( is_numeric($data) ) return $data;

        $non_displayables = array(
            '/%0[0-8bcef]/',            // url encoded 00-08, 11, 12, 14, 15
            '/%1[0-9a-f]/',             // url encoded 16-31
            '/[\x00-\x08]/',            // 00-08
            '/\x0b/',                   // 11
            '/\x0c/',                   // 12
            '/[\x0e-\x1f]/'             // 14-31
        );
        foreach ( $non_displayables as $regex )
            $data = preg_replace( $regex, '', $data );
        $data = str_replace("'", "''", $data );
        return $data;
    }

$fields = array(
    'projectTitle' => array('field' => 'b.BidTitle', 'searchType' => 'like'),
    'BidType' => array('field' => 'b.BidType', 'searchType' => 'equal'),
    'BidStatus' => array('field' => 'b.BidStatus', 'searchType' => 'equal'),
    'department' => array('field' => 'b.Department', 'searchType' => 'equal'),
    'bidId' => array('field' => 'b.BidID', 'searchType' => 'equal'),
    'bidDate' => array('field' => 'b.BidDate', 'searchType' => 'equal'),
    'dueDate' => array('field' => 'b.DueDate', 'searchType' => 'equal')

);

$where = array();
foreach($fields as $fieldPost => $field) {
    if(isset($_POST[$fieldPost]) && strlen($_POST[$fieldPost]) > 0) {
        if($field['searchType'] == 'like') {
            $where[] = "".$field['field']." LIKE '%" . ms_escape_string($_POST[$fieldPost]) . "%'";
        } else {
            $where[] = "".$field['field']." = '" . ms_escape_string($_POST[$fieldPost]) . "'";
        }
    }
}
   $sql = "Select b.ID,convert(char(10),b.BidDate,101) BidDate,convert(char(10),
          b.DueDate,101)DueDate,b.BidTitle,b.DueTime,b.BidID,BidIDFile,
          d.Department,b.BidType,CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe convert(char(10),b.AwardDate,101)END AS AwardDate,
          convert(char(10),b.LastUpdate,101) LastUpdate,s.Status
          FROM bids b inner join dept d on b.Department=d.DeptID inner join Status s on b.BidStatus=s.StatusId " . ( count($where) > 0 ? " WHERE " . implode(' AND ', $where) : "" );
//echo $sql;
  $params = array();
  $options =  array( "Scrollable" => SQLSRV_CURSOR_KEYSET );
  $query = sqlsrv_query( $conn, $sql , $params, $options );

  $num_rows = sqlsrv_num_rows($query);

  $per_page = 20;   // Per Page
  $page  = 1;

  if(isset($_GET["Page"]))
  {
      $page = $_GET["Page"];
  }

  $prev_page = $page-1;
  $next_page = $page+1;

  $row_start = (($per_page*$page)-$per_page);
  if($num_rows<=$per_page)
  {
      $num_pages =1;
  }
  else if(($num_rows % $per_page)==0)
  {
      $num_pages =($num_rows/$per_page) ;
  }
  else
  {
      $num_pages =($num_rows/$per_page)+1;
      $num_pages = (int)$num_pages;
  }
  $row_end = $per_page * $page;
  if($row_end > $num_rows)
  {
      $row_end = $num_rows;
  }

  $sql = " SELECT c.* FROM (
      SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowID,ID,convert(char(10),b.BidDate,101) BidDate,convert(char(10),
          b.DueDate,101)DueDate,b.BidTitle,b.DueTime,b.BidID,BidIDFile,
          d.Department,b.BidType,CASE WHEN b.AwardDate ='01/01/1900' Then NULL ELSe convert(char(10),b.AwardDate,101)END AS AwardDate,
          convert(char(10),b.LastUpdate,101) LastUpdate,s.Status
          FROM bids b inner join dept d on b.Department=d.DeptID inner join Status s on b.BidStatus=s.StatusId " . ( count($where) > 0 ) ? " WHERE " . implode(' AND ', $where) : " )
      ) AS c
  WHERE c.RowID > $row_start AND c.RowID <= $row_end

  $query = sqlsrv_query( $conn, $sql);
  ";
?>
  <table>
      <thead>
      <tr style="white-space:nowrap">
          <th></th>
          <th>Issue Date</th>
          <th>Due Date</th>
          <th>Project Title</th>
          <th>ID</th>
          <th>Department</th>
          <th>Type</th>
          <th>Award Date</th>
          <th>Last Update</th>
          <th>Status</th>
      </tr>
      </thead>
<?php
while($result = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC))
{
?>
 <tbody>
 <?php echo '<tr style="background:',(($c=!$c)? '#eee' : '#ddd' ),';font-size:12px;">'; ?>
    <td><div align="center"><a href="bidDetails.php?Id=<?php echo $result["ID"];?>"><img src="images/details.gif" alt="" title="Details" /></a></div></td>
    <td><div align="center"><?php echo $result["BidDate"];?></div></td>
    <td><?php echo $result["DueDate"];?><br />@<?php echo $result["DueTime"];?></td>
   <!-- <td><a href="pdfdownload.php?file=<?php echo $result["BidIDFile"];?>"><?php echo $result["BidTitle"];?></a></td>  -->
    <td><a href="uploads/<?php echo $result["BidIDFile"]; ?>" Target="DETAILS" onClick="window.open('','DETAILS','width=850,height=700,scrollbars=yes'); return true;" type="application/octet-stream"><?php echo $result["BidTitle"];?></a></td>
    <td style="white-space:nowrap"><div align="center"><?php echo $result["BidID"];?></div></td>
    <td align="right"><?php echo $result["Department"];?></td>
    <td align="right"><?php echo $result["BidType"];?></td>
    <td align="right"><?php echo $result["AwardDate"];?></td>
    <td align="right"><?php echo format_date($result["LastUpdate"]);?></td>
    <td align="right"><?php echo $result["Status"];?></td>
  </tr>
<?php
}
?>
</tbody>
</table>
<br>
Total <?php echo $num_rows;?> Record : <?php echo $num_pages;?> Page :
<?php
if($prev_page)
{
  echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$prev_page&txtKeyword=$strprojectTitle&BidType=$strbidType'><< Back</a> ";
}

for($i=1; $i<=$num_pages; $i++){
  if($i != $page)
  {
      echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i&txtKeyword=$strprojectTitle&BidType=$strbidType'>$i</a> ]";
  }
  else
  {
      echo "<b> $i </b>";
  }
}
if($page!=$num_pages)
{
  echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$next_page&txtKeyword=$strprojectTitle&BidType=$strbidType'>Next>></a> ";
}
sqlsrv_close($conn);
?>

AnswerRe: Display results based on dropdown selected dropdown value Pin
tobisoft28-Apr-15 14:34
tobisoft28-Apr-15 14:34 
GeneralRe: Display results based on dropdown selected dropdown value Pin
nilprajapati8-Dec-15 23:13
nilprajapati8-Dec-15 23:13 
QuestionCan't get ubuntu boot to recognize USB drive! Pin
swampwiz22-Feb-15 8:46
swampwiz22-Feb-15 8:46 
QuestionHow Can I Detect Whether My Total Price Is Greater Than The Price I Inputted On Textbox Pin
Patrick sarmiento12-Feb-15 21:49
Patrick sarmiento12-Feb-15 21:49 
SuggestionRe: How Can I Detect Whether My Total Price Is Greater Than The Price I Inputted On Textbox Pin
Richard MacCutchan12-Feb-15 23:50
mveRichard MacCutchan12-Feb-15 23:50 
QuestionPass datepicker value to PHP & MySQL Pin
Jassim Rahma8-Feb-15 0:28
Jassim Rahma8-Feb-15 0:28 
AnswerRe: Pass datepicker value to PHP & MySQL Pin
Thomas Daniels8-Feb-15 3:59
mentorThomas Daniels8-Feb-15 3:59 
QuestionMake this Hashing in PHP Pin
Jassim Rahma28-Jan-15 22:05
Jassim Rahma28-Jan-15 22:05 
AnswerRe: Make this Hashing in PHP Pin
Richard MacCutchan28-Jan-15 22:24
mveRichard MacCutchan28-Jan-15 22:24 
Question$_COOKIE[session_name()] vs session_id() which one holds good? Pin
Jayapal Chandran28-Jan-15 11:11
Jayapal Chandran28-Jan-15 11:11 
QuestionApache2.sin do not peer *.pl domain Pin
pixcraft14-Jan-15 3:33
pixcraft14-Jan-15 3:33 
QuestionFlash drive on Linux Pin
ForNow13-Dec-14 15:28
ForNow13-Dec-14 15:28 
AnswerRe: Flash drive on Linux Pin
Michael Martin9-Jan-15 21:22
professionalMichael Martin9-Jan-15 21:22 
Questionselectbox sql problems Pin
ceewhitehead7-Nov-14 1:39
ceewhitehead7-Nov-14 1:39 
AnswerRe: selectbox sql problems Pin
Richard MacCutchan7-Nov-14 1:59
mveRichard MacCutchan7-Nov-14 1:59 
GeneralRe: selectbox sql problems Pin
ceewhitehead7-Nov-14 3:29
ceewhitehead7-Nov-14 3:29 
GeneralRe: selectbox sql problems Pin
Richard MacCutchan7-Nov-14 3:40
mveRichard MacCutchan7-Nov-14 3:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.