[SOLVED] Please Help (Grouping)
Posted: Mon Aug 23, 2004 6:12 pm
I have hacked and hacked away and cannot figure out why it is not working (OR how to do it exactly). I have a search query which targets the same page and shows the results below the search form. The results are to be grouped according to TYPES which are also pull from a database (MySQL). I have not been able to effectively show the results in each row next to their proper TYPES (located on the left column, while the results are on the right column).
It should result like this:
COMPANYTYPE RECORD1
RECORD2
RECORD3
RECORD4
COMPANYTYPE RECORD1
RECORD2
and so on...Can anyone figure it out? Here is the entire page. I'm using DreamWeaver MX 2004 to assist me in coding:
Here is the page in action to see what I mean:
http://www.canaanloans.com/index.php
You can search for "company" to return results.
It should result like this:
COMPANYTYPE RECORD1
RECORD2
RECORD3
RECORD4
COMPANYTYPE RECORD1
RECORD2
and so on...Can anyone figure it out? Here is the entire page. I'm using DreamWeaver MX 2004 to assist me in coding:
Code: Select all
<?php
//PHP version of DWTeam Dynamic Search SQL
//for searchQuery
$tfmsqlstr = "";
if (isset($_POST["search"]) || isset($_GET["search"])){
$tfm_searchField = (isset($_POST["search"]))?$_POST["search"]:$_GET["search"];
if($tfm_searchField != "") {
$tfm_andor = "AND";
$tfm_exact = "false";
$bellChar = chr(7);
//if any words option
if (isset($_POST["anyallexact"]) || isset($_GET["anyallexact"])){
$tfm_temp = (isset($_POST["anyallexact"]))?$_POST["anyallexact"]:$_GET["anyallexact"];
if($tfm_temp == "any") $tfm_andor = "OR";
}
//if exact phrase option
if (isset($_POST["anyallexact"]) || isset($_GET["anyallexact"])){
$tfm_temp = (isset($_POST["anyallexact"]))?$_POST["anyallexact"]:$_GET["anyallexact"];
if($tfm_temp == "exact") $tfm_exact = "true";
}
$tfmsqlstr = " WHERE ((";
$tfm_databaseFields = explode(",","company,city,state,zip");
if ((strstr($tfm_searchField,'"')) || ($tfm_exact == "true")){
$tfm_searchField = str_replace('"','',$tfm_searchField);
$tfm_andor = "OR";
}else
if (stristr($tfm_searchField," or ")){
$tfm_searchField = preg_replace('/\s+or\s+/i',$bellChar,$tfm_searchField);
$tfm_andor = "OR";
}else
if (strstr($tfm_searchField,',') || strstr($tfm_searchField,' ') || stristr(strtolower($tfm_searchField),' and ')) {
$tfm_searchField = preg_replace("/\s+and\s+/i",$bellChar,$tfm_searchField);
$tfm_searchField = str_replace(",",$bellChar,$tfm_searchField);
$tfm_searchField = str_replace(" ",$bellChar,$tfm_searchField);
}
$splitField = explode($bellChar,$tfm_searchField);
for ($i = 0; $i < sizeof($splitField) ;$i++){
for ($j = 0; $j < sizeof($tfm_databaseFields); $j++){
$tfmsqlstr = $tfmsqlstr."(".$tfm_databaseFields[$j]." LIKE '%".str_replace("'","''",$splitField[$i])."%')";
if ($j < sizeof($tfm_databaseFields)-1) $tfmsqlstr = $tfmsqlstr." OR ";
}
if ($i < sizeof($splitField) -1) $tfmsqlstr = $tfmsqlstr.") ".$tfm_andor." (";
}
$tfmsqlstr = $tfmsqlstr."))";
}else{
$tfmsqlstr = " WHERE 1=0 ";
}
}else{
$tfmsqlstr = " WHERE 1=0 ";
}
?>
<?php require_once('Connections/content.php'); ?>
<?php
mysql_select_db($database_content, $content);
$query_companytypeLIST = "SELECT * FROM var_companytype WHERE active = 'Y'";
$companytypeLIST = mysql_query($query_companytypeLIST, $content) or die(mysql_error());
$row_companytypeLIST = mysql_fetch_assoc($companytypeLIST);
$totalRows_companytypeLIST = mysql_num_rows($companytypeLIST);
$tfmsqlstr_searchQuery = " WHERE 1=1 ";
if (isset($tfmsqlstr)) {
$tfmsqlstr_searchQuery = $tfmsqlstr;
}
mysql_select_db($database_content, $content);
$query_searchQuery = sprintf("SELECT * FROM con_company %s",$tfmsqlstr_searchQuery);
$searchQuery = mysql_query($query_searchQuery, $content) or die(mysql_error());
$row_searchQuery = mysql_fetch_assoc($searchQuery);
$totalRows_searchQuery = mysql_num_rows($searchQuery);
$tfmsqlstr_searchRESULTS = " WHERE 1=1 ";
if (isset($tfmsqlstr)) {
$tfmsqlstr_searchRESULTS = $tfmsqlstr;
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>CanaanLoans.com ++ Networking _____________________ v1 </title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="css/main.css" rel="stylesheet" type="text/css" title="mainCSS" media="all">
<style type="text/css">
<!--
.style1 {font-size: 10px}
-->
</style>
</head>
<body background="graphics/main/bg.gif" bgproperties="fixed">
<table width="100%" border="1" cellpadding="4" cellspacing="0" background="graphics/main/bg_table.gif" class="tableBorder">
<tr>
<td align="left" valign="top"><table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td align="left" valign="top"><h1><strong>Network Search</strong><br>
Duis aute irure dolor eu fugiat nulla pariatur. Velit esse cillum dolore sunt in culpa in reprehenderit in voluptate. Ut labore et dolore magna aliqua. Mollit anim id est laborum. Consectetur adipisicing elit, qui officia deserunt velit esse cillum dolore.<br>
<br>
Search for Companies by Name, City, State, and/or Zip by using "and", "or", or by spacing words apart. The *optional pull-down menu is for specification only. <br>
</h1> </td>
<td align="left" valign="top"><form name="tfm_search" method="get" action="index.php">
<table cellpadding=4 cellspacing=1 bgcolor="#CCCCCC">
<tr>
<td colspan="2" background="graphics/main/table_top.gif"><h1><strong>Search for Companies</strong></h1></td>
</tr>
<tr>
<td align="right" valign="top" background="graphics/main/table_mid.gif">
<div align="right">
<h1>Term: </h1>
</div></td>
<td align="left" valign="top" bgcolor="#FFFFFF">
<h1>
<input name="search" type="text" class="forms" style="width:250px" />
</h1></td>
</tr>
<tr>
<td align="right" valign="top" background="graphics/main/table_mid.gif"> <h1 class="style1">[optional]</h1></td>
<td align="left" valign="top" bgcolor="#FFFFFF">
<h1>
<select name="anyallexact" class="forms">
<option value="all">All words</option>
<option value="any">Any words</option>
<option value="exact">Exact phrase</option>
</select>
</h1></td>
</tr>
<tr>
<td align="right" valign="top" background="graphics/main/table_mid.gif"><h1> </h1></td>
<td align="left" valign="top" bgcolor="#FFFFFF"><h1>
<input name="submit" type="submit" class="forms" value="Search" />
</h1></td>
</tr>
<tr>
<td colspan="2" background="graphics/main/table_top.gif"><h1> </h1></td>
</tr>
</table>
</form></td>
</tr>
<tr><td colspan="2"><table width="100%" cellpadding=4 cellspacing=1 bgcolor="#CCCCCC">
<?php if ($totalRows_searchQuery > 0) { // Show if recordset not empty ?>
<tr>
<td colspan="2" background="graphics/main/table_top.gif"><h1><strong>Search Results </strong></h1></td>
</tr>
<?php do { ?>
<tr>
<td width="200" align="right" valign="top" background="graphics/main/table_mid.gif">
<div align="right">
<h1><?php echo $row_companytypeLIST['type']; ?></h1>
</div></td>
<td align="left" valign="top" bgcolor="#FFFFFF">
<?php do { ?>
<table width="100%" border="0" cellspacing="0" cellpadding="4">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0">
<tr valign="top" height="4">
<td align="left"><img src="../graphics/main/box_topleft.gif" width="4" height="4"></td>
<td colspan="3" align="left"></td>
<td align="right"><img src="../graphics/main/box_topright.gif" width="4" height="4"></td>
</tr>
<tr valign="top">
<td align="left"></td>
<td colspan="3" align="left"><h1><b><?php echo $row_searchQuery['company']; ?></b><br>
</h1></td>
<td align="right"> </td>
</tr>
<tr valign="bottom" height="4">
<td height="4" align="left"> </td>
<td align="left" valign="top"><h1><?php echo $row_searchQuery['address']; ?><br>
<?php echo $row_searchQuery['city']; ?>, <?php echo $row_searchQuery['state']; ?> <?php echo $row_searchQuery['zip']; ?></h1></td>
<td width="4" height="4" align="left" valign="top"><h1><img src="graphics/main/spacer.gif" width="20"></h1></td>
<td height="4" align="left" valign="top"><h1>ph: <?php echo $row_searchQuery['phone']; ?><br>
fax: <?php echo $row_searchQuery['fax']; ?><br>email: <a href="mailto:<?php echo $row_searchQuery['email']; ?>"><?php echo $row_searchQuery['email']; ?></a><br>website: <a href="http://<?php echo $row_searchQuery['url']; ?>" target="_blank"><?php echo $row_searchQuery['url']; ?></a></h1></td>
<td height="4" align="right"> </td>
</tr>
<tr valign="bottom" height="4">
<td align="left"><img src="../graphics/main/box_botleft.gif" width="4" height="4"></td>
<td colspan="3" align="left"></td>
<td align="right"><img src="../graphics/main/box_botright.gif" width="4" height="4"></td>
</tr>
</table></td>
</tr>
</table><?php } while ($row_searchQuery = mysql_fetch_assoc($searchQuery)); ?> </td>
</tr>
<?php } while ($row_companytypeLIST = mysql_fetch_assoc($companytypeLIST)); ?>
<tr>
<td colspan="2" background="graphics/main/table_top.gif"><h1> </h1></td>
</tr>
<?php } // Show if recordset not empty ?>
</table></td>
</tr>
</table></td>
</table>
</body>
</html>
<?php
mysql_free_result($companytypeLIST);
mysql_free_result($searchQuery);
?>http://www.canaanloans.com/index.php
You can search for "company" to return results.