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.

• Sam Street: Other than the above mentioned code, what other positives are there to they way you have produced your code? MySQL injection isn't really an issue currently but thankyou for pointing that out to me