bickyz

Hi, I have following php code that displays data from the mysql tables: <?php $pdo = Database::connect(); $sql = 'SELECT DISTINCT * FROM tblcompany c NATURAL JOIN tblstock s WHERE DATE(updated) = DATE(NOW()) AND updated = (SELECT MAX(_s.updated) FROM tblstock _s WHERE _s.comid = c.comid) ORDER BY stock DESC, updated ASC'; foreach ($pdo->query($sql) as $row) { echo '<tr>'; echo '<td>'. $row['company'] . '</td>'; echo '<td>'. $row['stock'] . '</td>'; echo '</tr>'; } Database::disconnect(); ?> I would like to display the message "no results updated yet" if the query has no results. I have tried adding 'echo "no results updated yet"' after the last line "Database:disconnect ();" but it displays the message whether there is a results or not. How do I display such message. Any help would be much appreciatedm thank you.

thank you very much Barand

hi barand, thank you for looking at it. The above query throws following error Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'USING'. I am using MS SQL server.

Hi I am trying to create a php page that pulls data from MSSQL table. Following is the code I am using: <?php $objConnect = mssql_connect("localhost","sa",""); $objDB = mssql_select_db("mydatabase"); $strSQL = "SELECT * FROM tblPC ORDER BY pc_name ASC"; $objQuery = mssql_query($strSQL) or die ("Error Query [".$strSQL."]"); $Num_Rows = mssql_num_rows($objQuery); $Per_Page = 20; // Per Page $Page = $_GET["Page"]; if(!$_GET["Page"]) { $Page=1; } $Prev_Page = $Page-1; $Next_Page = $Page+1; $Page_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; } $Page_End = $Per_Page * $Page; IF ($Page_End > $Num_Rows) { $Page_End = $Num_Rows; } ?> <table width="600" border="1"> <tr> <th width="20%">PC ID</th> <th width="40%">PC Name</th> <th width="20%">Make</th> <th width="20%">Room</th> </tr> <?php for($i=$Page_Start;$i<$Page_End;$i++) { ?> <tr> <td><?=mssql_result($objQuery,$i,"pc_id");?></td> <td><?=mssql_result($objQuery,$i,"pc_name");?></td> <td><?=mssql_result($objQuery,$i,"makemodel");?></td> <td><?=mssql_result($objQuery,$i,"room_id");?></td> </tr> <?php } ?> </table> <br> Total <?= $Num_Rows;?> Record : <?=$Num_Pages;?> Page : <?php if($Prev_Page) { echo " <a href='$_SERVER[SCRIPT_NAME]?Page=$Prev_Page'><< Back</a> "; } for($i=1; $i<=$Num_Pages; $i++){ if($i != $Page) { echo "[ <a href='$_SERVER[SCRIPT_NAME]?Page=$i'>$i</a> ]"; } else { echo "<b> $i </b>"; } } if($Page!=$Num_Pages) { echo " <a href ='$_SERVER[SCRIPT_NAME]?Page=$Next_Page'>Next>></a> "; } mssql_close($objConnect); ?> tblPC pc_id pc_name makemodel room_id tblROOM room_id room_name building The room_id field on tblPC and tblRoom are in database relationship. I have following function that obtains room name from tblROOM. How can I use this function in the above page so that room_id on <td><?=mssql_result($objQuery,$i,"room_id");?></td> will display the room name instead of the id. function ObtainRoomName($identifier) { global $objDB, $objConnect; mssql_select_db($objDB, $objConnect); $query_rsfunction = sprintf("SELECT tblROOM.room_name FROM tblROOM WHERE tblROOM.room_id = %s", $identifier); $rsfunction = mssql_query($query_rsfunction, $objConnect) or die("Couldn't connect to SQL Server on $objDB"); $row_rsfunction = mssql_fetch_assoc($rsfunction); $totalRows_rsfunction = mssql_num_rows($rsfunction); return $row_rsfunction['room_name']; mssql_free_result($rsfunction); } I have use this functuin with my other project by using <?php echo ObtainRoomName($row_rs1['room_id']); ?> but here Im lost with <?=mssql_result($objQuery,$i,"room_id");?> Any help would be much appreciated, thank you.

thank you barand.

hi all, I am trying to create a database tables for my software inventory. I have following information: The three table with green title are what I have finalised my design to; I need your expertise whether I am doing it correctly or if there is better way of doing it. Any help would be much appreciated, thank you.

Hi, I have a mysql table with following fields id – int company – varchar(50) cost – decimal(5,2) Updated - datetime Example of data: 101 company1 120.10 2013-05-22 10:21:19 102 company2 121.20 2013-05-22 11:11:29 103 company3 120.15 2013-05-23 12:44:10 104 company4 124.25 2013-05-23 13:26:49 Following is the text field code for cost: Cost: <input type="text" name="txtcost"><br /> How will my php code be if I want to insert Pound and Pence from two form field to one mysql field like this or if there is better method please advice. <select name="pound"> <option value="0" selected="selected">Pound:</option> <option value="120">120</option> <option value="121">121</option> <option value="122">122</option> <option value="123">123</option> <option value="124">124</option> <option value="125">125</option> </select> <select name="pence"> <option value="0" selected="selected">Pence:</option> <option value="00">00</option> <option value="10">10</option> <option value="15">15</option> <option value="20">20</option> <option value="25">25</option> <option value="30">30</option> </select> Your help will be much appreciated, thank you.

Hi we have a inhouse Win32 application that saves data to MSSQL. I am trying to create a php search form that will search a table which has following data. my search form <form name="myform" method="post" action="searchresults.php" id="myform"> <input type="text" name="search" size=30> <Select NAME="field"> <Option VALUE="contents">Contents</option> <Option VALUE="item_id">Item Number</option> </Select> <input name="Search" type="Submit" id="Search" value="Search"> </form> my search results page <?php include 'includes/opendb.php'; $search = $_POST['search']; $field = $_POST['field']; if ($search == ""){ echo "<p>You forgot to enter a search term"; exit; } $result = mssql_query("SELECT * FROM dbo.userAnswers WHERE {$field} LIKE '%{$search}%'"); echo '<ul class="SearchResults">'; while($r = mssql_fetch_array($result)){ $item_id = $r['item_id']; $contents = $r['contents']; echo <<<LIST <li> <ul class="Result"> <li>{$item_id}</li> <li>{$contents}</li> </ul> </li> LIST; } echo '</ul>'; ?> <?php include 'includes/closedb.php'; ?> I now have changed my search form to be like this. When someone select Firstname from dropdown list I want the search to be done for all the 101 item id and its contents. <form name="myform" method="post" action="searchresults.php" id="myform"> <input type="text" name="search" size=30> <Select NAME="field"> <Option VALUE="101">Firstname</option> <Option VALUE="102">Start Date</option> <Option VALUE="103">Introduction</option> <Option VALUE="104">Question 1</option> <Option VALUE="105">Question 2</option> </Select> <input name="Search" type="Submit" id="Search" value="Search"> </form> What changes I will be looking to do in my search results page script. I have tried changing few bits like this but nothing is displayed in the search results page. Your help will be much appreciated thank you. <?php include 'includes/opendb.php'; $search = $_POST['search']; $field = $_POST['field']; if ($search == ""){ echo "<p>You forgot to enter a search term"; exit; } $result = mssql_query("SELECT * FROM dbo.userAnswers WHERE item_id = $field AND contents LIKE '%{$search}%' "); echo '<ul class="SearchResults">'; while($r = mssql_fetch_array($result)){ $item_id = $r['item_id']; $contents = $r['contents']; echo <<<LIST <li> <ul class="Result"> <li>{$item_id}</li> <li>{$contents}</li> </ul> </li> LIST; } echo '</ul>'; ?> <?php include 'includes/closedb.php'; ?>

hi, I have following php search code (grateful to another forum member Barand for his help) that pulls the data from mysql, I would like only 6 results to be displayed on first page then rest to be paginated. I am trying to follow these tutorials from phpfreaks.com/tutorial/basic-pagination & jadendreamer.wordpress.com/2012/11/20/php-tutorial-searching-and-pagination but I have no clue how to integrate the pagination code; Could someone please assist me. Here is the search page demo Your help will be much appreciated, thank you. <?php if(isset($_REQUEST['searchbtn'])){ $where = array(); $whereclause = ''; if (!empty($_POST['duration'])) { $val = intval($_POST['duration']); $where[] = "(acdur = $val)"; } if (!empty($_POST['transport'])) { $val = mysql_real_escape_string($_POST['transport']); $where[] = "(actransp = '$val')"; } if (!empty($_POST['datepicker'])) { $val = mysql_real_escape_string($_POST['datepicker']); $where[] = "(acdate = '$val')"; } if (isset($_POST['activity'])) { $val = join(",", array_map('intval', $_POST['activity'])); $where[] = "(incl_activity.incid IN ($val))"; } if (count($where)) $whereclause = "WHERE " . join(' AND ', $where); $query = "SELECT activities.*, teamleader.tlname, GROUP_CONCAT(incdesc SEPARATOR ', ') as Includes FROM activities JOIN incl_activity on activities.acid = incl_activity.acid JOIN inclusion on incl_activity.incid = inclusion.incid JOIN teamleader on activities.tlid2 = teamleader.tlid $whereclause GROUP BY activities.acid ORDER BY activities.accost ASC"; $test= mysql_query($query) or die(mysql_error()); if(mysql_num_rows($test)) { $i=0; while($row = mysql_fetch_assoc($test)){ $actitle=$row["actitle"]; $tlname=$row["tlname"]; $acdate=$row["acdate"]; $acdur=$row["acdur"]; $accost=$row["accost"]; $incdesc=$row["Includes"]; $actransp=$row["actransp"]; echo "<div style='width:250px; padding:10px; float:left;'> $actitle <br> $tlname <br> $acdate <br> $acdur <br> $accost <br> $incdesc <br> $actransp </div>"; $i++; if($i % 3 == 0) echo "<div style='clear:both'></div>\n"; } if ($i%3) echo "<div style='clear:both'></div>\n"; } else { echo '<div align=center style="margin:20px; font-family:Arial, Helvetica, sans-serif; font-size: 20px; font-weight:bold; color: #ae1919;">Your search did not match any results.</div>'; header('Refresh: 5; URL=search.php'); } } ?> </body> </html> <?php mysql_free_result($test); ?>

Hi I have following tables: And following is my PHP code (mysearch.php) which will search and list the relevant results in the same page. For example lets say in the search criteria when someone enters particuar date like 05/05/2013, I want all the results of that month i.e May 2013 (anything from 01/05/2013 - 31/05/2013) to be listed; Can this be done. Or I have to create another field in the database with MM-YY only and search that field. Any help would be much appreciated, thank you. <?php ini_set('display_errors',1); error_reporting(E_ALL); ?> <?php include 'db.inc.php'; ?> <?php session_start(); $_SESSION['duration']='duration'; $_SESSION['datepicker']='datepicker'; $_SESSION['transport']='transport'; $_SESSION['activity']='activity'; ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Intranet Activities Search</title> <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" /> <script src="http://code.jquery.com/jquery-1.9.1.js"></script> <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script> <link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css" /> <script> $(function() { $("#datepicker").datepicker({dateFormat: 'yy-mm-dd'}); }); </script> <style type="text/css" media="screen"> table { font-size: 14px; } </style> </head> <body> <form id="form1" name="form1" method="post" action="mysearch.php"> <table width="600" border="0" cellspacing="0" cellpadding="0"> <tr> <td>Duration (days)</td> <td> <?php $duration_options = ''; for ($d=7; $d<=14; $d++) { $sel = $d == $_SESSION['duration'] ? "selected='selected" : ''; $duration_options .= "<option $sel value='$d'> $d</option>"; } ?> <select name="duration" id="duration"> <option value="">Any</option> <?php echo $duration_options ?> </select></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td width="45%">Date (YYYY-MM-DD)</td> <td width="55%"> <?php $datepicker_options['datepicker'] = 'datepicker'; $_SESSION['datepicker'] = $datepicker_options['datepicker']; ?> <input name="datepicker" type="text" id="datepicker" /> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td>Transportation</td> <td> <?php $transport_options = ''; $trans = array('included', 'excluded'); foreach ($trans as $o) { $sel = $o == $_SESSION['transport'] ? "selected='selected" : ''; $transport_options .= "<option $sel value='$o'> $o</option>"; } ?> <select name="transport" id="transport"> <option selected="selected" value="0">Any</option> <?php echo $transport_options ?> </select> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td colspan="2"> <?php $_SESSION['activity'] = (isset($_POST['activity'])) ? $_POST['activity'] : array(); $query="SELECT * FROM inclusion ORDER BY incid"; $test= mysql_query($query) or die(mysql_error()); while(list($incid, $incdesc) = mysql_fetch_row($test)) { $chk = in_array($incid, $_SESSION['activity']) ? "checked='checked'" : ''; echo "<input type=\"checkbox\" name=\"activity[]\" value=\"$incid\" $chk /> $incdesc <br>"; } ?> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td><input type="submit" name="searchbtn" id="searchbtn" value="Search" /></td> <td> </td> </tr> </table> </form> <p></p> <p></p> <p>RESULTS</p> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td> </td> </tr> </table> <?php if(isset($_REQUEST['searchbtn'])){ $where = array(); $whereclause = ''; if (!empty($_POST['duration'])) { $val = intval($_POST['duration']); $where[] = "(acdur = $val)"; } if (!empty($_POST['transport'])) { $val = mysql_real_escape_string($_POST['transport']); $where[] = "(actransp = '$val')"; } if (!empty($_POST['datepicker'])) { $val = mysql_real_escape_string($_POST['datepicker']); $where[] = "(acdate = '$val')"; } // assuming you change you checkbox names if (isset($_POST['activity'])) { $val = join(",", array_map('intval', $_POST['activity'])); $where[] = "(incl_activity.incid IN ($val))"; } if (count($where)) $whereclause = "WHERE " . join(' AND ', $where); $query = "SELECT activities.*, teamleader.tlname, GROUP_CONCAT(incdesc SEPARATOR ', ') as Includes FROM activities JOIN incl_activity on activities.acid = incl_activity.acid JOIN inclusion on incl_activity.incid = inclusion.incid JOIN teamleader on activities.tlid2 = teamleader.tlid $whereclause GROUP BY activities.acid ORDER BY activities.accost ASC"; $test= mysql_query($query) or die(mysql_error()); if(mysql_num_rows($test)) { $i=0; while($row = mysql_fetch_assoc($test)){ $actitle=$row["actitle"]; $tlname=$row["tlname"]; $acdate=$row["acdate"]; $acdur=$row["acdur"]; $accost=$row["accost"]; $incdesc=$row["Includes"]; $actransp=$row["actransp"]; echo "<div style='width:250px; padding:10px; float:left;'> $actitle <br> $tlname <br> $acdate <br> $acdur <br> $accost <br> $incdesc <br> $actransp </div>"; $i++; if($i % 3 == 0) echo "<div style='clear:both'></div>\n"; } if ($i%3) echo "<div style='clear:both'></div>\n"; } else { echo '<div align=center style="margin:20px; font-family:Arial, Helvetica, sans-serif; font-size: 20px; font-weight:bold; color: #ae1919;">Your search did not match any results.</div>'; header('Refresh: 5; URL=mysearch.php'); } } ?> </body> </html> <?php mysql_free_result($test); ?>

thank you very much barand, I have set the session at the top of the page, no errors now. how do i now tag the session from index page to the sort-by drop down list in results page so that selecting items from the drop down will use the session. <?php ini_set('display_errors',1); error_reporting(E_ALL); ?> <?php include 'db.inc.php'; ?> <?php session_start(); $_SESSION['duration']='duration'; $_SESSION['datepicker']='datepicker'; $_SESSION['transport']='transport'; $_SESSION['activity']='activity'; ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Intranet Activities Search</title> <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" /> <script src="http://code.jquery.com/jquery-1.9.1.js"></script> <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script> <link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css" /> <script> $(function() { $("#datepicker").datepicker({dateFormat: 'yy-mm-dd'}); }); </script> <style type="text/css" media="screen"> table { font-size: 14px; } </style> </head> <body> <form id="form1" name="form1" method="post" action="listactivities.php"> <table width="600" border="0" cellspacing="0" cellpadding="0"> <tr> <td>Duration (days)</td> <td> <?php $duration_options = ''; for ($d=7; $d<=14; $d++) { $sel = $d == $_SESSION['duration'] ? "selected='selected" : ''; $duration_options .= "<option $sel value='$d'> $d</option>"; } ?> <select name="duration" id="duration"> <option value="">Any</option> <?php echo $duration_options ?> </select></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td width="45%">Date (YYYY-MM-DD)</td> <td width="55%"> <?php $datepicker_options['datepicker'] = 'datepicker'; $_SESSION['datepicker'] = $datepicker_options['datepicker']; ?> <input name="datepicker" type="text" id="datepicker" /> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td>Transportation</td> <td> <?php $transport_options = ''; $trans = array('included', 'excluded'); foreach ($trans as $o) { $sel = $o == $_SESSION['transport'] ? "selected='selected" : ''; $transport_options .= "<option $sel value='$o'> $o</option>"; } ?> <select name="transport" id="transport"> <option selected="selected" value="0">Any</option> <?php echo $transport_options ?> </select> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td colspan="2"> <?php $_SESSION['activity'] = (isset($_POST['activity'])) ? $_POST['activity'] : array(); $query="SELECT * FROM inclusion ORDER BY incid"; $test= mysql_query($query) or die(mysql_error()); while(list($incid, $incdesc) = mysql_fetch_row($test)) { $chk = in_array($incid, $_SESSION['activity']) ? "checked='checked'" : ''; echo "<input type=\"checkbox\" name=\"activity[]\" value=\"$incid\" $chk /> $incdesc <br>"; } ?> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td><input type="submit" name="searchbtn" id="searchbtn" value="Search" /></td> <td> </td> </tr> </table> </form> </body> </html> <?php mysql_free_result($test); ?>

hi barand, thank you for the help. it throws following error now, Notice: Undefined index: duration in index.php on line 40 Notice: Undefined index: transport in index.php on line 74 <?php ini_set('display_errors',1); error_reporting(E_ALL); ?> <?php include 'db.inc.php'; ?> <?php session_start(); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Intranet Activities Search</title> <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" /> <script src="http://code.jquery.com/jquery-1.9.1.js"></script> <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script> <link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css" /> <script> $(function() { $("#datepicker").datepicker({dateFormat: 'yy-mm-dd'}); }); </script> <style type="text/css" media="screen"> table { font-size: 14px; } </style> </head> <body> <form id="form1" name="form1" method="post" action="listactivities.php"> <table width="600" border="0" cellspacing="0" cellpadding="0"> <tr> <td>Duration (days)</td> <td> <?php $duration_options = ''; for ($d=7; $d<=14; $d++) { $sel = $d == $_SESSION['duration'] ? "selected='selected" : ''; $duration_options .= "<option $sel value='$d'> $d</option>"; } ?> <select name="duration" id="duration"> <option value="">Any</option> <?php echo $duration_options ?> </select></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td width="45%">Date (YYYY-MM-DD)</td> <td width="55%"> <?php $datepicker_options['datepicker'] = 'datepicker'; $_SESSION['datepicker'] = $datepicker_options['datepicker']; ?> <input name="datepicker" type="text" id="datepicker" /> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td>Transportation</td> <td> <?php $transport_options = ''; $trans = array('included', 'excluded'); foreach ($trans as $o) { $sel = $o == $_SESSION['transport'] ? "selected='selected" : ''; $transport_options .= "<option $sel value='$o'> $o</option>"; } ?> <select name="transport" id="transport"> <option selected="selected" value="0">Any</option> <?php echo $transport_options ?> </select> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td colspan="2"> <?php $_SESSION['activity'] = (isset($_POST['activity'])) ? $_POST['activity'] : array(); $query="SELECT * FROM inclusion ORDER BY incid"; $test= mysql_query($query) or die(mysql_error()); while(list($incid, $incdesc) = mysql_fetch_row($test)) { $chk = in_array($incid, $_SESSION['activity']) ? "checked='checked'" : ''; echo "<input type=\"checkbox\" name=\"activity[]\" value=\"$incid\" $chk /> $incdesc <br>"; } ?> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td><input type="submit" name="searchbtn" id="searchbtn" value="Search" /></td> <td> </td> </tr> </table> </form> </body> </html> <?php mysql_free_result($test); ?>

hi barand, i didn know how to put the checkboxes values in the session so i end up trying to copy the codes from the dropdown list. Could u plz help with the code, i will be really grateful,thank you.

Hi barand, thank you for your help. I have now created a checkbox from db query My session code for textbox (datepicker) and checkboxex (activities) seems to be not right, could you please have a look. index.php <?php ini_set('display_errors',1); error_reporting(E_ALL); ?> <?php include 'db.inc.php'; ?> <?php session_start(); $duration_options = ''; for ($d=7; $d<=14; $d++) { $sel = $d == $_SESSION['duration'] ? "selected='selected" : ''; $duration_options .= "<option $sel value='$d'> $d</option>"; } $transport_options = ''; $trans = array('included', 'excluded'); foreach ($trans as $o) { $sel = $o == $_SESSION['transport'] ? "selected='selected" : ''; $transport_options .= "<option $sel value='$o'> $o</option>"; } $activity_options = ''; for ($d=1; $d<=3; $d++) { $sel = $d == $_SESSION['activity'] ? "checked='checked" : ''; $activity_options .= "<input $sel value='$d'> $d</option>"; } $datepicker_options['datepicker'] = 'datepicker'; $_SESSION['datepicker'] = $datepicker_options['datepicker']; ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Intranet Activities Search</title> <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" /> <script src="http://code.jquery.com/jquery-1.9.1.js"></script> <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script> <link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css" /> <script> $(function() { $("#datepicker").datepicker({dateFormat: 'yy-mm-dd'}); }); </script> <style type="text/css" media="screen"> table { font-size: 14px; } </style> </head> <body> <form id="form1" name="form1" method="post" action="listactivities.php"> <table width="400" border="0" cellspacing="0" cellpadding="0"> <tr> <td>Duration (days)</td> <td><select name="duration" id="duration"> <option value="">Any</option> <?php echo $duration_options ?> </select></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td width="45%">Date (YYYY-MM-DD)</td> <td width="55%"> <input name="datepicker" type="text" id="datepicker" /> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td>Transportation</td> <td><select name="transport" id="transport"> <option selected="selected" value="0">Any</option> <?php echo $transport_options ?> </select></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td colspan="2"> <?php $query="SELECT * FROM inclusion ORDER BY incid"; $test= mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($test)){ $activityname = $row["incdesc"]; $activityid = $row["incid"]; "<br/>"; "<br/>"; echo "<input type=\"checkbox\" name=\"activity[]\" value=\"$activityid\"> $activityname"; echo "<br>"; } ?> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td><input type="submit" name="searchbtn" id="searchbtn" value="Search" /></td> <td> </td> </tr> </table> </form> </body> </html> <?php mysql_free_result($test); ?> listactivities.php <?php ini_set('display_errors',1); error_reporting(E_ALL); ?> <?php include 'db.inc.php'; ?> <?php session_start(); foreach(array('duration', 'datepicker', 'transport', 'activity') as $name) { $_SESSION[$name] = (isset($_POST[$name])) ? $_POST[$name] : ""; } echo "<pre>"; print_r($_SESSION); echo "</pre>\n"; ?> <form name="form1" method="post" action=""> <select name="sortby" id="sortby" onChange="this.form.submit()"> <option selected="selected" value="0">Sorty by</option> <option value="costasc">Price Ascending</option> <option value="costdesc">Price Descending</option> <option value="durasc">Duration Ascending</option> <option value="durdesc">Duration Descending</option> <option value="nameasc">Teamleader Name ASC</option> <option value="namedesc">Teamleader Name DESC</option> <option value="dateasc">Date Ascending</option> <option value="datedesc">Date Descending</option> </select> </form> <?php $orderby = ''; if (isset($_POST['sortby'])) { switch ($_POST['sortby']) { case '0': $orderby = ''; break; case 'costasc': $orderby = " ORDER BY accost ASC"; break; case 'costdesc': $orderby = " ORDER BY accost DESC"; break; case 'durasc': $orderby = " ORDER BY acdur ASC"; break; case 'durdesc': $orderby = " ORDER BY acdur DESC"; break; case 'nameasc': $orderby = " ORDER BY tlname ASC"; break; case 'namedesc': $orderby = " ORDER BY tlname DESC"; break; case 'dateasc': $orderby = " ORDER BY acdate ASC"; break; case 'datedesc': $orderby = " ORDER BY acdate DESC"; break; // etc } } ?> <p></p><p></p> <?php $where = array(); $whereclause = ''; if (!empty($_POST['duration'])) { $val = intval($_POST['duration']); $where[] = "(acdur = $val)"; } if (!empty($_POST['transport'])) { $val = mysql_real_escape_string($_POST['transport']); $where[] = "(actransp = '$val')"; } if (!empty($_POST['datepicker'])) { $val = mysql_real_escape_string($_POST['datepicker']); $where[] = "(acdate = '$val')"; } // assuming you change you checkbox names if (isset($_POST['activity'])) { $val = join(",", array_map('intval', $_POST['activity'])); $where[] = "(incl_activity.incid IN ($val))"; } if (count($where)) $whereclause = "WHERE " . join(' AND ', $where); $query = "SELECT activities.*, teamleader.tlname, GROUP_CONCAT(incdesc SEPARATOR ', ') as Includes FROM activities JOIN incl_activity on activities.acid = incl_activity.acid JOIN inclusion on incl_activity.incid = inclusion.incid JOIN teamleader on activities.tlid2 = teamleader.tlid $whereclause GROUP BY activities.acid $orderby"; //ORDER BY teamleader.tlname ASC"; //echo $query; $test= mysql_query($query) or die(mysql_error()); if(mysql_num_rows($test)) { $i=0; while($row = mysql_fetch_assoc($test)){ $actitle=$row["actitle"]; $tlname=$row["tlname"]; $acdate=$row["acdate"]; $acdur=$row["acdur"]; $accost=$row["accost"]; $incdesc=$row["Includes"]; $actransp=$row["actransp"]; echo "<div style='width:250px; padding:10px; float:left;'> $actitle <br> $tlname <br> $acdate <br> $acdur <br> $accost <br> $incdesc <br> $actransp </div>"; $i++; if($i % 3 == 0) echo "<div style='clear:both'></div>\n"; } if ($i%3) echo "<div style='clear:both'></div>\n"; } else { echo '<div align=center style="margin:20px; font-family:Arial, Helvetica, sans-serif; font-size: 20px; font-weight:bold; color: #ae1919;">Your search did not match any results.</div>'; header('Refresh: 5; URL=index.php'); } ?> <?php mysql_free_result($test); ?>

hi barand, thank you for your help, much appreciated. it throws Undefined variable error on red marked lines. do i use FOREACH to those check boxes. <?php ini_set('display_errors',1); error_reporting(E_ALL); ?> <?php $duration_options = ''; for ($d=7; $d<=14; $d++) { $sel = $d == $_SESSION['duration'] ? "selected='selected" : ''; $duration_options .= "<option $sel value='$d'> $d</option>"; } $transport_options = ''; $trans = array('included', 'excluded'); foreach ($trans as $o) { $sel = $o == $_SESSION['transport'] ? "selected='selected" : ''; $transport_options .= "<option $sel value='$o'> $o</option>"; } $_SESSION["datepicker"] = $_POST["datepicker"]; $_SESSION['activity'] = $_POST['activity']; ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Intranet Activities Search</title> <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" /> <script src="http://code.jquery.com/jquery-1.9.1.js"></script> <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script> <link rel="stylesheet" href="http://jqueryui.com/resources/demos/style.css" /> <script> $(function() { $("#datepicker").datepicker({dateFormat: 'yy-mm-dd'}); }); </script> <style type="text/css" media="screen"> table { font-size: 14px; } </style> </head> <body> <form id="form1" name="form1" method="post" action="listactivities.php"> <table width="400" border="0" cellspacing="0" cellpadding="0"> <tr> <td>Duration (days)</td> <td><select name="duration" id="duration"> <option value="">Any</option> <?php echo $duration_options ?> </select></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td width="45%">Date (YYYY-MM-DD)</td> <td width="55%"> <input type="text" id="datepicker" name="datepicker" /> </td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td>Transportation</td> <td><select name="transport" id="transport"> <option selected="selected" value="0">Any</option> <?php echo $transport_options ?> </select></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td>Fishing</td> <td><input name="activity[]" type="checkbox" id="chkfishing" value="1" /></td> </tr> <tr> <td>Gliding</td> <td><input name="activity[]" type="checkbox" id="chkgliding" value="2" /></td> </tr> <tr> <td>Flying</td> <td><input name="activity[]" type="checkbox" id="chkflying" value="3" /></td> </tr> <tr> <td> </td> <td> </td> </tr> <tr> <td><input type="submit" name="searchbtn" id="searchbtn" value="Search" /></td> <td> </td> </tr> </table> </form> </body> </html>