I have a search script that searches my database for a particular property address, but I have a problem displaying all the records ... my script is displaying only one record no matter how many records exist in the database with the same property address. I have two tables in the database that needs to be grouped together to display records, the first table is showings and the second table is notes where notes.nid = showings.id. My scripts search.php code is below: (hoping someone can help me). I also have some html code in there for functionalities in my page.
<?php
require_once('auth.php');
?>
<html>
<head>
<title>Search Showings</title>
<link type="text/css" rel="stylesheet" href="dhtmlgoodies_calendar.css?random=20051112" media="screen"></LINK>
<SCRIPT type="text/javascript" src="dhtmlgoodies_calendar.js?random=20060118"></script>
<script language="JavaScript" type="text/JavaScript">
<!--
function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}
function MM_setTextOfTextfield(objName,x,newText) { //v3.0
var obj = MM_findObj(objName); if (obj) obj.value = newText;
}
//-->
</script>
<script src="sorttable.js"></script>
<link href="table.css" rel="stylesheet" type="text/css" />
<script language="JavaScript" type="text/javascript">
<!--//
function openNotes(url, win)
{
var ptr = window.open(url, win,
'menubar=0,resizable=0,width=380,height=480,top=10,left=10');
return false;
}
//-->
</script>
</head>
<body>
<?php
$Details = nl2br($_POST['Details']);
$note = nl2br($_POST['note']);
if($_GET["txtKeyword"] != "")
{
$objConnect = mysql_connect("localhost","xxxxxxxxxxxxxxx","xxxxxxxxxxxx") or die(mysql_error());
$objDB = mysql_select_db("xxxxxxxxxxxxxxxxx");
// Search By Property or Date
$strSQL = "SELECT showings.id as id, showings.Property as Property, showings.Requested as Requested, showings.Company as Company, showings.Phone as Phone, showings.Date_show as Date_show, showings.Details as Details, showings.Cell as Cell, GROUP_CONCAT(notes.date, '--', notes.note ORDER BY notes.date ASC SEPARATOR '\n')
as note from showings LEFT JOIN notes ON notes.nid = showings.id WHERE (Property LIKE '%".$_GET["txtKeyword"]."%' or Date_show LIKE '%".$_GET["txtKeyword"]."%')";
$objQuery = mysql_query($strSQL) or die ("Error Query [".$strSQL."]");
$Num_Rows = mysql_num_rows($objQuery);
$Per_Page = 5; // 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;
}
$strSQL .=" order by Date_show DESC LIMIT $Page_Start , $Per_Page";
$objQuery = mysql_query($strSQL);
//if no records found
if (mysql_num_rows($objQuery)==0)
{
echo "<table width='50%' border='0' cellpadding='5'>";
echo "<tr>";
echo "<td width='17%'></td>";
echo "<td width='83%'></td>";
echo "</tr>";
echo "<tr>";
echo "<td></td>";
echo "<td><h2>Your Search Returned No Results</h2></td>";
echo "</tr>";
echo "<tr>";
echo "<td></td>";
echo '<td><a href="javascript: history.go(-1)">GO BACK</a></td>';
echo "</tr>";
echo "</table>";
exit;
}
?>
<h1>Search Results</h1>
Your Search for <b> "<font color="ff0000"><?=$_GET["txtKeyword"];?></font>"</b> Returned <b><font color="ff0000"><?= $Num_Rows;?> </b></font> Results on <b><font color="ff0000"> <?=$Num_Pages;?> </b></font> Pages<br/><br/>
<table width="1246" border="1" class="sortable">
<tr>
<th width="42"> <div align="center">ID </div></th>
<th width="78"> <div align="center">Date </div></th>
<th width="206"> <div align="center">Property </div></th>
<th width="154"> <div align="center">Requested By </div></th>
<th width="220"> <div align="center">Details </div></th>
<th width="245"> <div align="center">Agent Notes </div></th>
<th width="50" align="center"> <div align="center">EDIT<br/>DELETE </div></th>
</tr>
<?
while($objResult = mysql_fetch_array($objQuery))
{
?>
<tr valign="top">
<td><?=$objResult["id"];?></td>
<td><?=$objResult["Date_show"];?></td>
<td><?=$objResult["Property"];?></td>
<td><?=nl2br(stripslashes(htmlspecialchars($objResult["Requested"])));?><br/><?=stripslashes(htmlspecialchars($objResult["Company"]));?><br/><?=$objResult["Phone"];?></td>
<td><?=nl2br(stripslashes(htmlspecialchars($objResult["Details"])));?></td>
<td><?=nl2br(stripslashes(htmlspecialchars($objResult["note"])));?><br /><a href="notes.php?id=<?=$objResult["id"];?>" target="notes" onclick="return openNotes(this.href, this.target);">Add note</a> - <a href="notesedit.php?id=<?=$objResult["id"];?>">Edit Notes</a></td>
<td align="center"><a href="edit.php?id=<?=$objResult["id"];?>">Edit Showing</a><br/><a href="delete.php?id=<?=$objResult["id"];?>" onClick="return confirm('Are you sure you want to delete this showing?')">Delete Showing</a></td>
</tr>
<?
}
?>
</table>
<br>
<br>
PAGE :
<?
if($Prev_Page)
{
echo " <a href='search.php?Page=$Prev_Page&txtKeyword=$_GET[txtKeyword]'><< Back</a> ";
}
for($i=1; $i<=$Num_Pages; $i++){
if($i != $Page)
{
echo "[ <a href='search.php?Page=$i&txtKeyword=$_GET[txtKeyword]'>$i</a> ]";
}
else
{
echo "<b> $i </b>";
}
}
if($Page!=$Num_Pages)
{
echo " <a href ='search.php?Page=$Next_Page&txtKeyword=$_GET[txtKeyword]'>Next>></a> ";
}
mysql_close($objConnect);
}
?>
<br><br>
<form><input type="button" value=" Print this page " onclick="window.print();return false;" /></form>
<br>
<table width="81%" border="1" cellpadding="5" class="search">
<tr>
<td width="91%"><font face="Verdana, Arial, Helvetica, sans-serif"><strong>Search Again:</strong></font>
<br><i>(You can search by Date or Property Address)</i><br>
Properties or Dates can be selected using the dropdown or calendar at the right or simply entered into search box.<br>
DATES should be entered in the following format: YYYY-MM-DD or part of a date like YYYY.<br>
<table width="933" border="0" cellpadding="0" class="insidesearch">
<tr>
<td width="362" rowspan="3" bgcolor="#FFFFCC"><form name="frmSearch" method="get" action="search.php">
<input name="txtKeyword" type="text" id="txtKeyword" size="40" value="<?=$_GET["txtKeyword"];?>">
<input type="submit" value="Search">
</td>
</form><br>
</tr>
<tr>
<td width="671" align="left"><div id="properties" style="margin:0px 0px -10px 0px;>
<?php include 'dropdown.php'; ?></div></td>
</tr>
<tr>
<td width="671" align="left"><strong><font color="#FF0000" size="2" face="Verdana, Arial, Helvetica, sans-serif"><em>.... Choose Date HERE --» </em></font></strong><input type="button" value="Calendar" onclick="displayCalendar(document.forms[0].txtKeyword,'yyyy-mm-dd',this);"> <input name="Reset" type="reset" onclick="MM_setTextOfTextfield('txtKeyword','','')" value="Clear Search Box"><br><font color="#0033CC" size="1" face="Verdana, Arial, Helvetica, sans-serif"><em><strong>You have to clear the search box before choosing dates, otherwise the calendar won't work.</font></em></strong></td>
</tr>
</table>
</td>
</tr>
</table>
<br><br>
<hr>
<table width="755" height="192" border="0" cellpadding="10" cellspacing="0">
<tr><td> </td><td> </td><td> </td></tr>
<tr>
<td width="383" height="153" valign="top">
<p>« <a href="javascript:history.go(-1)">GO
BACK </a></p>
<p></p>
<p><br />
<a href="view-paginated.php">View All Showing Requests<br>
</a><a href="searchform.php">Search Showings<br>
</a><a href="new.php">Insert New Showing Request / Check Status</a></p></td>
<td width="1" rowspan="2" bgcolor="#FF0000"> </td>
<td width="311" rowspan="2" valign="top" border="2" bordercolor="#336633" bgcolor="#FFFFCC">
<p><strong><font size="4">FOR ADMIN ONLY:</font></strong></p>
<p><a href="indexproperties.php">View, Add, Edit or Delete Properties or
Status </a></p>
<p> <a href="indexcompany.php">View, Add, Edit or Delete Companies</a></p>
<p> <a href="deleteshowingrequests.php">Delete Showing Requests</a></p></td>
</tr>
<tr>
<td height="39">
<p><a href="logout.php">LOGOUT</a></p></td>
</tr>
</table>
</div>
</td>
</tr>
</table>
<br><br><br><br><br><br><br><br>
</body>
</html>
