Grouping According...

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

Grouping According...

Post by AliasBDI »

I'm not sure what I did wrong here, but my php repeat column is killing the server. It times out because it does so many loops. Could someone take a look at it please? I'll put the whole page here so you see what is happening.

I have a search form which targets the same page and queries the database with the search term. The results are supposed to be listed in groups according to their "var_companytype" field. So I created a looping row for the list of "var_companytype" and the rows have 2 columns: (1) "var_companytype" result, and (2) repeating columns of 2 and infinite rows which have the "var_companytype" id in their "var_companytype" field. Remind you that I have not set that part up yet because my repeat region is not working correctly. I'll get to that part later (I do not know how to code that). So here is the entire page:

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">
<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 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">
                  <table width="100%"  border="0" cellspacing="0" cellpadding="4">
                    <tr>
					<?php
$searchQuery_endRow = 0;
$searchQuery_columns = 2; // number of columns
$searchQuery_hloopRow1 = 0; // first row flag
do {
    if($searchQuery_endRow == 0  && $searchQuery_hloopRow1++ != 0) echo "<tr>";
   ?>
                      <td><table  border="0" cellspacing="0" cellpadding="0">
  <tr valign="top" height="4">
    <td width="4" align="left"><img src="../graphics/main/box_topleft.gif" width="4" height="4"></td>
    <td align="left"></td>
    <td width="4" align="right"><img src="../graphics/main/box_topright.gif" width="4" height="4"></td>
  </tr>
  <tr valign="top">
    <td align="left"></td>
    <td align="left"><h1><b><?php echo $row_searchQuery['company']; ?></b><br><?php echo $row_searchQuery['address']; ?><br><?php echo $row_searchQuery['city']; ?>, <?php echo $row_searchQuery['state']; ?> <?php echo $row_searchQuery['zip']; ?></h1></td>
    <td 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 align="left"></td>
    <td align="right"><img src="../graphics/main/box_botright.gif" width="4" height="4"></td>
  </tr>
</table></td><?php  $searchQuery_endRow++;
if($searchQuery_endRow >= $searchQuery_columns) {
  ?>
                    </tr>
					<?php
 $searchQuery_endRow = 0;
  }
} while ($row_searchQuery = mysql_fetch_assoc($searchQuery));
if($searchQuery_endRow != 0) {
while ($searchQuery_endRow < $searchQuery_columns) {
    echo("<td>&nbsp;</td>");
    $searchRESULTS_endRow++;
}
echo("</tr>");
}?>
                  </table>                  </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);
?>
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

That's alot of icky code to pick through, but one thing does stand out.
while ($searchQuery_endRow < $searchQuery_columns) {
echo("<td> </td>");
$searchRESULTS_endRow++;
}
If $searchQuery_endRow is less than $searchQuery_columns then it will enter the while loop and never leave it as the while loop doesn't change either $searchQuery_endRow or $searchQuery_columns and therefore the while condition will always be true, therefore an infinite loop :o
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Thanks.

Post by AliasBDI »

How do I fix this? Shouldn't the endRow eventually catch up with the columns?
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Well, and this is just a wild guess, you might just need to replace the while with an if ...
if ($searchQuery_endRow < $searchQuery_columns) {
Shouldn't the endRow eventually catch up with the columns?
The problem is that once it enters the while loop it will never leave, as nothing inside the while loop effects the initial conditions. It's the same as doing while($foo=='bar'){ .... }
Unless you do something inside the loop to affect the value of $foo then it will never leave the loop as $foo will always == 'bar'
AliasBDI
Forum Contributor
Posts: 286
Joined: Fri Nov 15, 2002 10:35 am
Location: Spring, TX, USA

Next Question...

Post by AliasBDI »

How do I make the query inside of each "var_companytype" repeat rows repeat with a new query of the original results. Hope that makes sense.

I have changed the code and simply got rid of the repeat column. The 'if' instead of 'while' did the job, but the result did not look good. Forgive with all of the questions, I am still learning PHP.

Here the code now:

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">
<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 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);
?>
Post Reply