Need unique url but require non unique information from other columns

Question

I am making a review system and I need to be able to only select the DISTINCT url from the database, but then pull the relevant page title and rating time. I have managed to get it working by using:

<?php
//opens conection
include 'conn.php';

echo $_COOKIE["search"];

$sql = "SELECT * FROM feedback WHERE url LIKE '%$_COOKIE[search]%' GROUP BY url ORDER   BY ratingtime DESC";

//runs the query
$query = mysql_query($sql);
$linecount = 1;

//populates the table with the information
echo '<table id="myTable" class="tablesorter">';
echo'<thead>
    <tr class ="null">
      <th>URL</th>
      <th>Page Title</th>
      <th>Date Last Modified</th>
      <th>Average Rating</th>
    </tr>
  </thead>';
echo '<tbody>';
echo '<tr>';

while($row = mysql_fetch_array($query))
{
  echo '<td class="overFlow"><a href="extra_info.php?url='.$row['url'].'">' .$row['url']. '</a></td>';
  echo '<td><a href="extra_info.php?url='.$row['url'].'">' . $row['pagetitle'] . '</a></td>';
  echo '<td>' . $row['ratingtime'] . '</td>';

  $check = mysql_query("SELECT `rating` FROM feedback WHERE url = '$row[url]'");
  $totalvotes = mysql_num_rows($check);
  $result = mysql_query("SELECT SUM(rating) AS 'rating_total' FROM feedback WHERE url = '$row[url]'") or die(mysql_error());
  $row = mysql_fetch_assoc($result);
  $add = $row['rating_total'];
  $average = $add / $totalvotes;

  echo '<td>' .round($average, 2). '</td>';
  echo '</tr>';

  $linecount++;
}

echo '</tbody>';
echo "</table>";
?>

I just wondered if there was a much better way to go about doing this? I am a complete noob when it come to php so if you provide any advice could you please provide evidence/documentation so that I can read up on it and learn.

Answer 1

I would do it like this

<?php
#opens conection
include 'conn.php';

echo $_COOKIE["search"];


$search = mysql_real_escape_string($_COOKIE[search]);
$sql    = "SELECT * FROM feedback WHERE url LIKE '%{$search}%' GROUP BY url ORDER BY ratingtime DESC";
#runs the query
$query = mysql_query($sql);
$linecount = 1;

#populates the table with the information
echo '<table id="myTable" class="tablesorter">';
echo '<thead>
        <tr class ="null">
          <th>URL</th>
          <th>Page Title</th>
          <th>Date Last Modified</th>
          <th>Average Rating</th>
        </tr>
      </thead>
      <tbody>';

while($row = mysql_fetch_array($query)){

        $sqlurl = mysql_real_escape_string($row['url']);

        $feedback_query = mysql_query("SELECT SUM(`rating ) / COUNT(`rating`) AS avg_rating FROM feedback WHERE url = '{$sqlurl}'");
        $feedback       = mysql_fetch_assoc($feedback_query);

        $encodedurl = urlencode($row['url']);

        echo "<tr>
                <td class'overFlow'><a href='extra_info.php?url={$encodedurl}'>{$row['url']}</a></td>
                <td><a href='extra_info.php?url={$encodedurl}'>{$row['pagetitle']}</a></td>
                <td>{$row['ratingtime']}</td>
                <td>".round($feedback['avg_rating'], 2)."</td>
              </tr>";
        $linecount++;
}

echo '</tbody>
      </table>';
?>

If improved your query to get the Avg. Rating and added mysql_real_escape_string to prevent the mysql injection vulnerability.