We help IT Professionals succeed at work.

Need help formatting PHP MySql search results output

mnoel76
mnoel76 asked
on
732 Views
Last Modified: 2013-12-13
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">&nbsp;>&nbsp;</span> <a href="buy.php" class="locLink">Search Properties</a> <span class="locArrow">&nbsp;>&nbsp;</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">&nbsp;</td>
          <td align="right"></td>
        </tr>
      </table></td>
  </tr>
</table>
<p>&nbsp;</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.
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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";
               }?>

Author

Commented:
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
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.

Author

Commented:
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.  
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>";
?>

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 '..' ).
 

Author

Commented:
psimation,

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.