[SOLVED] Please Help (Grouping)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

[SOLVED] Please Help (Grouping)

Post by AliasBDI »

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:

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: &nbsp;</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>&nbsp;</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>&nbsp;</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">&nbsp;</td>
  </tr>
  <tr valign="bottom" height="4">
    <td height="4" align="left">&nbsp;</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">&nbsp;</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>&nbsp;</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);
?>
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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

have you solved your problem? It seems like now it does what you want.
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Nope

Post by AliasBDI »

It is not working yet. All of the results show up in the first row. In the query, I do not see any additional query with the COMPANYTYPE. So it should not work anyways.

In other words... The search query works fine. But they results are not grouped according to the first column in the table (not the database table, but the HTML result table of COMPANYTYPE).

Go to the page and try searching for "company". The record Company4 should be under "Real Estate Agent". But they are all under the first COMPANYTYPE. Moreso, they are not even being repeated in each row.
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Anybody?

Post by AliasBDI »

I'm willing to pay if I need to in order to get this working quickly. Here some more information on it which I wrote to someone else:

My search is working perfectly. My results are not showing up the way I want them. In my MySQL table, I have two tables being used here:

var_companytype (we will call it TYPE)
con_company (we will call it COMPANY)

The COMPANY has a field in its table for the "id" of the other table TYPE. In other words, the COMPANY records have a TYPE field to associate them with the other table (their actual fiels is the same as the other table name, "var_companytype").

Once a search is executed, the query word(s) hit the COMPANY table to find all matches. The results show by COMPANY inside of each TYPE. When results come, it should gather a list of records from TYPE, put them into the first column (HTML) and then in the second column of each row, it shows the results of COMPANY.

In other words, the COMPANY results are grouped according to the TYPEs they have in their field "var_companytype".

If you go to http://www.canaanloans.com/index2.php you can see what it should look like.

THE PROBLEM with the actual page http://www.canaanloans.com/index.php. (1) It is showing all results from TYPE instead of those found in the COMPANY query search.

(2) It is only showing the COMPANY results in the first row.

(3) It is not showing the COMPANY results in their according row.

Does that make sense?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

check to see if it works:
(the part before the require is ok, so I skipped it to make my post a bit shorter. Be sure to put it into your actual php file.)

Code: Select all

// skipped query builder
<?php require_once('Connections/content.php'); ?>
<?php
$tfmsqlstr_searchQuery = " WHERE 1=1 ";
if (isset($tfmsqlstr)) {
  $tfmsqlstr_searchQuery = $tfmsqlstr;
}
mysql_select_db($database_content, $content);
$query_searchQuery = sprintf("SELECT * FROM con_company inner join var_companytype on con_company.var_companytype=var_companytype.id  %s  and active='Y' order by var_companytype.id asc",$tfmsqlstr_searchQuery);
$searchQuery = mysql_query($query_searchQuery, $content) or die(mysql_error());

$results = array();
while($row = mysql_fetch_assoc($searchQuery) ) {
    if(!isset($results[$row['id']])) $results[$row['id']] = array();
    $results[$row['id']][] = $row;
}
?>
<!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 (count($results)) { // Show if recordset not empty ?>
              <tr>
                <td colspan="2" background="graphics/main/table_top.gif"><h1><strong>Search Results </strong></h1></td>
              </tr>
              <?php foreach($results as $companies) { ?>
              <tr>
                <td width="200" align="right" valign="top" background="graphics/main/table_mid.gif">
                  <div align="right">
                    <h1><?php echo $companies[0]['type']; ?></h1>
                </div></td>
                <td align="left" valign="top" bgcolor="#FFFFFF">
                  <?php foreach($companies as $company) { ?>
              <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 $company['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 $company['address']; ?><br>
          <?php echo $company['city']; ?>, <?php echo $company['state']; ?> <?php echo $company['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 $company['phone']; ?><br>
    fax: <?php echo $company['fax']; ?><br>email: <a href="mailto:<?php echo $company['email']; ?>"><?php echo $company['email']; ?></a><br>website: <a href="http://<?php echo $company['url']; ?>" target="_blank"><?php echo $company['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 }  ?>                  </td>
              </tr>
              <?php }  ?>
              <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($searchQuery);
?>
Basically, I combined two requests into one, then transformed it to the 2d array ($results) and outputted it to fit into your template. Perhaps you'll need to alias some fields and specify exactly which fields you want to be present in query result.
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

Thanks for checking it. I did as you said, basically added your code beginning with the require and keeping everything before it on the page.

Now the page never fully loads, but it gives me this on the page:
Column: 'active' in where clause is ambiguous
What does that mean?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

need to add-in the table name/alias that 'active' is from so mysql knows which one to use
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

It is still doing the same thing. I'll take a better look at it tonight and see if I can understand what you coded.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what's your query string now?
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

The query never really changed all that much. Here is where the query code is:

Code: Select all

<?php 
$tfmsqlstr_searchQuery = " WHERE 1=1 "; 
if (isset($tfmsqlstr)) { 
  $tfmsqlstr_searchQuery = $tfmsqlstr; 
} 
mysql_select_db($database_content, $content); 
$query_searchQuery = sprintf("SELECT * FROM con_company inner join var_companytype on con_company.var_companytype=var_companytype.id  %s  and con_company.active='Y' order by var_companytype.id asc",$tfmsqlstr_searchQuery); 
$searchQuery = mysql_query($query_searchQuery, $content) or die(mysql_error()); 

$results = array(); 
while($row = mysql_fetch_assoc($searchQuery) ) { 
    if(!isset($results[$row['id']])) $results[$row['id']] = array(); 
    $results[$row['id']][] = $row; 
} 
?>
That has the fix to the ACTIVE problem spoken of above. All the rest of the code is above in other posts as well.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

and it's still saying:
Column: 'active' in where clause is ambiguous
??
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

No. It is just running the script as it did before any additional code was added. It is just like how it was in the beginning. Here is where the test page resides:

http://www.canaanloans.com/index.php

Search for "company" and see.
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Post by AliasBDI »

Weirdan, your code DID work! I must have screwed up something else before. But your code worked. SOLVED!
Post Reply