Formatting Sort Results?

[jjfletch]

The following select statement queries two tables, returns the results from both, and sorts them so that those records with notes associated with them are displayed first.

$query = "SELECT Table1.id  

         , Table1.LastName
         , Table1.FirstName
         , Table1.Phone1
         , Table1.city1 
         , Table1.city2  

         , Table1.city3  

         , Table1.phone1 
         , Table1.phone2
         , Table1.phone3  

         , Table1.lastname 
         , COALESCE(Table2.Note, '') AS Note   

FROM Table1  

LEFT JOIN Table2 ON (Table2.id = Table1.id)
WHERE (Table1.city1 LIKE ('$cy%') OR Table1.city2 LIKE ('$cy%') OR Table1.city3 LIKE ('$cy%'))  

ORDER BY Note DESC, Table1.lastname DESC";

What I'd like to be able to do, is to print those records with notes in bold, while keeping those without notes non-bold. Is that possible? Anyone know how to do it?

[wilku]

What you'll need:
- a way to connect to database and retrieve data; should it be MySql the set of functions can be found here in php manual.
- if you'll get all the varibles from the query do something similar to

if (trim($Note)=="") {$start_tag = ""; $end_tag = "";}
else {$start_tag = "<b>"; $end_tag = "</b>";}

echo "$start_tag put all your data here $end_tag";

[jjfletch]

I can't seem to get it to work. I am using PHP/MySQL and I'll post the complete code below:

<?php

require_once ('../files/mysql_connect.php');

$cy = $_POST['city'];

   if (!empty($_POST['city'])) {
      $cy = escape_data($_POST['city']);
   } else {
      $cy = FALSE;
      echo '<p><font color="red">Please enter a City</font></p>';
   }

  if ($cy) {

 $query = "SELECT Table_1.id  
     , Table_1.LastName
     , Table_1.FirstName
     , Table_1.Phone1
     , Table_1.city1 
     , Table_1.city2  
     , Table_1.city3  
     , Table_1.phone1 
     , Table_1.phone2
     , Table_1.phone3  
     , Table_1.lastname 
     , COALESCE(Table_2.Notes, '') AS Notes   
FROM Table_1  

LEFT JOIN Table_2 ON (Table_2.id = Table_1.id)
WHERE (Table_1.city1 LIKE ('$cy%') OR Table_1.city2 LIKE ('$cy%') OR Table_1.city3 LIKE ('$cy%'))  

ORDER BY Notes DESC, Table_1.lastname DESC";
  }

 $result = mysql_query ($query);

 if (mysql_num_rows($result) == 0) {
 echo "<tr><td colspan=\"5\">Currently, we do not feature that in our database.\n";
 } else {
 while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {

 echo "
     <tr>
     <td>{$row['id']}</td>
     <td>{$row['LastName']}</td>
     <td>{$row['FirstName']}</td>
     <td>{$row['Phone1']}</td>
     </tr>
     \n";
}
}

echo '</table>';

mysql_close();
?>

Any other suggestions?

[wilku]

As far as I remember you don' t use single quotes when referring to arrays inside strings. Try

echo "
         <tr>
         <td>{$row[id]}</td>
         <td>{$row[LastName]}</td>
         <td>{$row[FirstName]}</td>
         <td>{$row[Phone1]}</td>
         </tr>"; 

If this doesn't help try var_dump($row) to see whats inside.

And one more thing - there is no opening tag for table.

[jjfletch]

Think I got it! Thanks for all the help everyone!

[wilku]

Would such a thing work?

if (trim($row['Notes'])=="") {$start_tag = ""; $end_tag = "";}
else {$start_tag = "<b>"; $end_tag = "</b>";}

echo "
         <tr>
         <td>$start_tag{$row[id]}$end_tag</td>
         <td>$start_tag{$row[LastName]}$end_tag</td>
         <td>$start_tag{$row[FirstName]}$end_tag</td>
         <td>$start_tag{$row[Phone1]}$end_tag</td>
         </tr>";