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>

hi barand, thank you. On the results page i have added following code: <?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"; ?> when i do the search it shows the saved session contents. Am I on a right track ? i have been trying to get my head over how to do this session part in my code; but I have no idea, I will be really grateful if you can help me please.

hi barand, when you say "save the previous selection", how do i save; do i use session or save it to database. thank you

Hi Barand, sorry i didnt get u. I have total 16 records in the database. For example if i select Duration 8 days and do a search, it will give me 4 records which are not sorted in any order. Now while on the results page with the 4 records if i select Sort by "Price Ascending" then the page clears the 4 records and displays all the 16 records sorted by Price Asc. So basically this dropdown is clearing the search results. What i want is, while on the results page with 4 records I should be able to flick between the sort by dropdown list for these 4 records in either Price ASC/DESC or Duration ASC/DESC or Date ASC/DESC or Teamleader Name ASC/DESC.

hi barand, thank you. Both errors are gone, while on the search results page if I select anything from the sortby drop down list, the results page displays everything.

Hi barand, thank you very much for your help. When I search, it does give me a results but it also throws me following error: Notice: Undefined index: sortby in /htdocs/listactivities.php on line 28 Notice: Undefined variable: orderby in /htdocs/listactivities.php on line 78 Line 28 is switch ($_POST['sortby']) { Line 78 is $orderby"; While on the results page if I select anything from the sort by drop down list, the results page displays everything clearing out the search results, sort by does works though. demo http://bickyz.byethost13.com/ <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 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 } //$query .= $orderby; // add order by clause to the query ?> <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'); } ?>

Hi Barand, thank you very much for your help. Can you please give me an idea on how the code will be if i want to implement a drop down list to sort the results. I have created a form on the top of this results page with following code <form name="form1" method="post" action=""> <select name="sort" id="sort"> <option selected="selected" value="0">Sorty by</option> <option value="costlow">Price Ascending</option> <option value="costhigh">Price Descending</option> <option value="durlow">Duration Ascending</option> <option value="durhigh">Duration Descending</option> <option value="nameasc">Teamleader Name ASC</option> <option value="namedesc">Teamleader Name DESC</option> <option value="acdate">Date Ascending</option> <option value="acdate">Date Descending</option> </select> </form>

thank you very much Barand, I have included this query on my other PHP Search post.

Hi barand, thank you very much. I have managed to get results side by side by using follwing changes, could you please check if I my code looks good. <?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); // and you search query is /*$query = "SELECT * FROM activities, incl_activity, inclusion JOIN incid $whereclause";*/ /* $query = "SELECT * 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";*/ $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 teamleader.tlname ASC"; //echo $query; $test= mysql_query($query) or die(mysql_error()); if(mysql_num_rows($test)) { $i=1; echo '<table><tr>'; 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 '<td>'."$actitle <br> $tlname <br> $acdate <br> $acdur <br> $accost <br> $incdesc <br> $actransp <br><br>".'</td>'; if($i % 4 == 0) echo '</tr><tr>'; $i++; } echo '</tr></table>'; } 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>'; } ?>

hi i am trying to create a seach in php that will query follwing four mysql tables. I have following sql statement: SELECT * 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 WHERE activities.acdate = "2013-10-13" ORDER BY teamleader.tlname ASC which gives following result: In above Jane Doe has three records, these three records are from same activity (Mountain Biking) on same Day and Duration whereas this activity includes Fishing, Gliding & Flying which are three difference records.. So is there anyway I can just list one record for one person. Or is this something I have to do in PHP script. Any help would be much appreciated, thank you.

I mean results to be like this i.e each records displays side by side 1 2 3 4 5 6 7 8 9