asked on

Need help formatting PHP MySql search results output

I have a MySql query that is pulling data from the same table 'ITEMS'. I have an attribute in the table called 'Featured'. Some Items will be featured some will not. Currently I have a php file that is only able to display the search results in one format(ie table color).

I would like to format the output so that the table color of the featured 'ITEMS' will be displayed in RED and the others blue. The results are based on search string that is input by user. I am thinking that 2 seperate php include files might be the best way to handle this and within those php files I can change the color of the tables. Is there a better way to handle this? I thought about trying to do something with CSS but do not know CSS well. I wasn't going to include the code but I figure it will help out.

<? session_start(); ?>
<div id="loading"> Performing Search... </div>
<?

      ### DEBUG
      $debugP = 0;
      ### END DEBUG

     #Fetch database connection
      require_once('Connections/myconn.php');
      mysql_select_db($database_myconn,$myconn);
      require_once('myfunctions.php');
      $mysql = new mysql();
      $mysql->conn = $myconn;

      #Fetch Header
      include('header.php');

      #### BUILD SEARCH SQL BASED ON SEARCH TYPE ####

      #defauts

      $maxRows_p = 10;
      $pageNum_p = 0;
      if (isset($_GET['pageNum_p'])) {
       $pageNum_p = $_GET['pageNum_p'];
      }
      $startRow_p = $pageNum_p * $maxRows_p;
      $limit = ' LIMIT '.$startRow_p.', '.$maxRows_p;

//Add state search
if(!empty($_REQUEST['state'])) {
$search[] = 'state = "'.$_REQUEST['state'].'"';
//Add zip code search
if(!empty($_REQUEST['zip']))
$search[] = 'zip = '.$_REQUEST['zip'];
//Add Price From
if(!empty($_REQUEST['pfrom']) && ($_REQUEST['pfrom'] != '-1'))
$search[] = 'price >= '.$_REQUEST['pfrom'];
//Add Max Price
if(!empty($_REQUEST['pto']) && ($_REQUEST['pto'] != '-1'))
$search[] = 'price <= '.$_REQUEST['pto'];
//Add Property ID
if(!empty($_REQUEST['id']))
$search[] = 'id = '.$_REQUEST['id'];
//Add Property Type
if(!empty($_REQUEST['cid']))
$search[] = 'cid = '.$_REQUEST['cid'];
//Add Property Style Type
if(!empty($_REQUEST['style']))
$search[] = 'style = '.$_REQUEST['styleID'];
//Add Min Bed
if(!empty($_REQUEST['bed']))
$search[] = 'bed >= "'.$_REQUEST['bed'].'"';
//Add Min Bath
if(!empty($_REQUEST['bath']))
$search[] = 'bath >= "'.$_REQUEST['bath'].'"';
//Add city
if(!empty($_REQUEST['city']))
$search[] = 'city = "'.$_REQUEST['city'].'"';
//Add min square feet
if(!empty($_REQUEST['sqft']))
$search[] = 'sqft >= '.$_REQUEST['sqft'];
//Add Garage
if(!empty($_REQUEST['garage']))
$search[] = 'garage = "'.$_REQUEST['garage'].'"';
//Add lot size
if(!empty($_REQUEST['lot_size']))
$search[] = 'lot_size >= '.$_REQUEST['lot_size'];
//Add county ID
if(!empty($_REQUEST['countyID']))
$search[] = 'countyID = '.$_REQUEST['countyID'].' ';
if(!empty($_REQUEST['subdiv']))
$search[] = 'subdiv = "'.$_REQUEST['subdiv'].'"';

//implode to search string on ' and ';
$searchStr = @implode(' and ',$search);
$str = "";
if(!empty($_REQUEST['keyword'])) {
$str = ' and MATCH(description) AGAINST ("'.$_REQUEST['keyword'].'" in BOOLEAN MODE)';
}
$searchStr .= $str;
$sql = 'select * FROM items WHERE (expires > NOW()) and active = "Yes" and sold = "No" and ';
$sql .= $searchStr;

//Add column sorting
            if($_REQUEST['sort'] != '')
                  $sort = ' order by featured ';
            else
                  $sort = $_REQUEST['sort'];

            ### DEBUG
            if($debugP) echo 'Advanced Search Sql<hr>'.$sql;

            $error['Results'] = 'No results found, please search again';

}

      ### Finished Building search sql and execting #####
      $sql .= $sort . $limit;

      //Perform search
      $searchResults = $mysql->exSql($sql);

      ### BUILD OUTPUT ####

      if (isset($_GET['totalRows_p'])) {
       $totalRows_p = $_GET['totalRows_p'];
      } else {
       $all_p = mysql_query($sql.$sort);
       $totalRows_p = @mysql_num_rows($all_p);
      }
      $totalPages_p = ceil($totalRows_p/$maxRows_p)-1;
?>

<div align="left" class="locText"><a href="index.php" class="locLink">Home</a> <span class="locArrow"> > </span> <a href="buy.php" class="locLink">Search Properties</a> <span class="locArrow"> > </span>Properties For Sale</div>
<hr size="1" color="#666666">

<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="pageText" >Showing: <strong><?php echo ($startRow_p + 1) ?> - <?php echo min($startRow_p + $maxRows_p, $totalRows_p) ?> of <?php echo $totalRows_p ?></strong> For Sale Listings</td>
<td align="right" class="pageText"></td>
</tr>
</table></td>
</tr>
<tr>
<td height="5"><img src="images/pixel.gif" width="1" height="1" alt=""></td>
</tr>
<tr>
<td><table width="100%" border="0" cellspacing="1" cellpadding="4" class="resBorder">
<tr>
<td class="colText">Address</td>
              <?
                    if(isset($_REQUEST['fromZip']) && ($_REQUEST['fromZip'] != ''))
                        print '<td class="colText">Miles</td>';
            ?>
<td class="colText">City</td>
<td class="colText">St</td>
<td class="colText">Price</td>
<td class="colText">Beds</td>
<td class="colText">Baths</td>
<td class="colText">Sqft</td>
</tr>
      <? while($row_p = @mysql_fetch_assoc($searchResults)) { ?>
<tr valign="top">
<td class="rowText"><a href="detail.php?id=<? echo $row_p['id']; ?>" class="rowLinkBold" ><? echo $row_p['address']; ?></a></td>
              <?
                    if(isset($_REQUEST['fromZip']) && ($_REQUEST['fromZip'] != ''))
                        print '<td class="rowText">'.number_format($row_p['Miles'],2,'.',',').'</td>';
            ?>
<td class="rowText"><? echo $row_p['city']; ?></td>
<td class="rowText"><? echo $row_p['state']; ?></td>
<td class="rowText"><? echo Money($row_p['price'],1); ?></td>
<td class="rowText"><? echo $row_p['bed']; ?></td>
<td class="rowText"><? echo $row_p['bath']; ?></td>
<td class="rowText"><? echo $row_p['sqft']; ?></td>
</tr>
<tr valign="top">
<td class="descText" colspan="8"><table border="0" cellspacing="0" cellpadding="4">
<tr valign="top">
<? //fetch photo from database
      $sql = "select * from photos where ptid = ". $row_p['id']." and porder = '1' ";
      //echo $sql; //debug sql output
      $pRS = mysql_query($sql,$myconn) or die(mysql_error());
      //fetch assoc array
      $row_photo = mysql_fetch_assoc($pRS);
      // if image exist
      if ( mysql_num_rows($pRS) > 0 )
            $imageSRC = $row_photo['location'];
      else //no photo
            $imageSRC = "noimage.jpg";
      ?>
<td class="descText"><a href="detail.php?id=<? echo $row_p['id']; ?>"><img src="admin/photos/uploads/small_thumbs/tn_<? echo $imageSRC; ?>" border="0" alt="<? echo $row_photo['caption']; ?>"></a></td>
<td class="descText"><? echo substr($row_p['description'],0,150); ?>...<a href="detail.php?id=<? echo $row_p['id']; ?>" class="rowLink">more..</a><a class="rowLink" href="saveListing.php?id=<? echo $row_p['id']; ?>" title="Click to save listing"><br>
save listing</a></td>
</tr>
</table></td>
</tr>
      <? } ?>
</table></td>
</tr>
<tr>
<td height="5"><img src="images/pixel.gif" width="1" height="1" alt=""></td>
</tr>
<tr>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="pageText"> </td>
<td align="right"></td>
</tr>
</table></td>
</tr>
</table>
<p> </p>
<?
      ## if no results where found
      if(@mysql_num_rows($searchResults)<=0){
$error['Results'] = 'No results found, please search again';
            foreach($error as $name => $value)
                  print '<div align=center class="error">'.$name . ': ' . $value.'</div>';
      }
      ##Fetch Footer
      include('footer2.php');
?>

<script>
document.getElementById('loading').style.display = 'none';
</script>

Please let me know how I can proceed from here, I am having some trouble with this.

ASKER CERTIFIED SOLUTION

psimation

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mnoel76

ASKER

psimation

Thanks for the quick response. I was thinking about throwing it into a loop after I posted the question. Let me see if I can write it on my own succesfully. I will let you know. thanks

mnoel76

ASKER

psimation
this is the line of code that I need to modify.
<? while($row_p = @mysql_fetch_assoc($searchResults)) { ?>
<tr valign="top">
<td class="rowText"><a href="detail.php?id=<? echo $row_p['id']; ?>" class="rowLinkBold" ><? echo $row_p['address']; ?></a></td>

I followed your instructions however I am getting a unexpected T_variable . Please take a look at my new code I added and adive me what I am screwing up.

<? while($row_p = @mysql_fetch_assoc($searchResults)) {

      while($myrow = @mysql_fetch_assoc($searchResults)) {
$my_featured_var = $myrow['featured'];
if($my_featured_var == "Yes") {
$bgcolor = "red";
}
              echo '<tr valign="top" bgcolor=\"$bgcolor\">'
              $bgcolor = "blue";
              }?>

thank you

mnoel76

ASKER

woops I above code should look like this I believe but still getting T_variable error.

<? while($row_p = @mysql_fetch_assoc($searchResults)) {

      $my_featured_var = $row_p['featured'];
if($my_featured_var == "Yes") {
$bgcolor = "red";
}
              echo '<tr valign="top" bgcolor=\"$bgcolor\">'
              $bgcolor = "blue";
              }?>

mnoel76

ASKER

no more T_variable error as I forgot the ; after the echo '<tr valign="top" bgcolor=\"$bgcolor\">'
I am getting to the results screen but no color change. please help...driving me crazy

psimation

Do you have stylesheets that set row colours in the background?
Also, from your snippet above:
$my_featured_var = $row_p['featured'];
if($my_featured_var == "Yes") {
$bgcolor = "red";
}
echo '<tr valign="top" bgcolor=\"$bgcolor\">';
$bgcolor = "blue";
}?>
You are not actually achoing anything in that row.

it needs some more:
echo '<tr valign="top" bgcolor="$bgcolor"><td>'.$some_var_here.'</td></tr>';
Also note: if you use single quotes to encapsulate echo, then you done't need to escape double quotes with \, but you MUST escape out of the string with that '..' notatoin to parse variables as per my example.

mnoel76

ASKER

psimation

I do have style sheets that I believe to write the color in the background. Should I delete the "class=ResBorder" code in this table?

I have modified the code numerous times. I did figure out that I had nothing anchored therefore the reason I did not see the bgcolor color change. When I included $my_featured_var in the final echo statement However I did get those results in red above my table. However, I am not a strong php coder to properly code for all the html tags that I need to echo. I get confused all the ". '. " etc.

The only reason I used single quotes was following some of the code I had previously written. I dont truly understand the difference between the single qoutes and the double one.

If you could be kinda enough to break down the table headers and table itself so that I can properly output this code I would be very grateful. At this point am so frustrated I can;t even seem to be able to get my sort function working properly. I am tring to sort or order by featured = "yes" but am not having
any luck. please take a deeper look as I would truly appreciate some more direction.

psimation

OK, firstly, the difference between using single and double quotes in echo:
When using double quotes, the contents between the double quotes are interpreted by PHP, so you can include variables in between your text and other characters, and PHP will automagically substitute them. eg.
echo "This is something I'd like to have printed to screen along with this $variable word";
Drawback of using double quotes is that HTML tags use double quotes for form attributes as well, so you must escape them in order to keep the double quotes encapsulating the echo in tact as per this example:
echo "this is something I'd like to echo that <font color=\"red\">includes<.font> HML tags";
If you forget to escape the font tags, your echo stops at the first " of color=" and causes a parse error.

Single quotes on the other hand are treat "lteraly" by echo. It allows you to echo somethng like this:

echo 'My variables name is $variable and it's value is:'.$variable.' which is nice';
the . is used to escape the echo and "concatenate" the output of the variable so that it looks like it's all part of one long echo.

My "rule of thumb" wrt when to use it is something like this;
If you want to echo alot of HTML, then use single quotes, because there is nothing (or little) in your string that requires interpretation by PHP. So, when you do this:
echo "This is <font color="red"> alot of HTML stuff</font> with no "variables" so this example works fine without me having to escape ", however, I have to escape single quotes still otherwise I won\'t see them and it will break my echo';

OK, so after the lecture ;)

<?php
//all your mysql connection and query stuff here...
$bgcolor = "blue";
echo "<table><tr><td>This is my table heading</td></tr>";
while($myrow = @mysql_fetch_assoc($searchResults)) {
$my_featured_var = $myrow['featured'];
$my_var_to_display = $myrow['whatever'];
if($my_featured_var == "Yes") {
$bgcolor = "red";
}
echo "<tr valign=\"top\" bgcolor=\"$bgcolor\"><td>$my_var_to_display</td></tr>";
$bgcolor = "blue";
}
echo "</table>";
?>

psimation

Made 2 typos

echo "this is something I'd like to echo that <font color=\"red\">includes</font> HML tags";
echo 'My variables name is $variable and it\'s value is:'.$variable.' which is nice';

Note that you DONT have to escape single quotes if your echo uses double quotes to encapsulate, but you MUST escape single quotes if your echo uses single quotes to encapsulate.
in the second line, therefor, you must escape the ' in "it's" or else the echo will stop there and generate a parse error, the \ tells PHP to treat the quote literally and not to include it in it';s logic when trying to find the end of your echo. the first ' in the '.$variable.' ends the echo, then the . tells PHP to cancatenate, the second . tells it to expect some more stuff, and the second ' begins a new encapsulation for the last part of the echo untill it reaches the next ' and subsequent ; (or some more '..' ).

mnoel76

ASKER

psimation,

thanks for the educational lesson I greatly apreciate it. very well done. I have awarded you the points. cheers!