Mysql query using distinct and *

Question

I want to display a list of titles but I also want to put a get variable from the query into the link. Is there a way to do this so that there aren't duplicate titles, but the links work. Right now the list is printing but the links aren't working.

$usernotes = mysql_query("select distinct title from notes where user_id='$user_id' and goam='$current_goam'");

 Print "<table border=\"1\" cellspacing=\"5\">"; 
 while($info = mysql_fetch_array( $usernotes )) 
 {          
    Print "<tr><td><a href=\"mainpage.php?goam_id=".$info['goam']."&note_id=".$info['title']."\">".$info['title'] . "</a></td></tr><td></td> "; 
 } 
 Print "</table>";

On a somewhat unrelated note, perhaps you should read through "Best way to stop SQL injection in PHP": http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — Aaron, Jan 05 '12 at 02:30

score 0 · Answer 1 · answered Jan 05 '12 at 02:27

0

You are only selecting one column. You can select multiple columns doing something like this: select distinct title, goam from notes where user_id='$user_id' and goam='$current_goam'

answered Jan 05 '12 at 02:27

Dessus

2,147
1
14
24

score 0 · Answer 2 · answered Jan 05 '12 at 02:27

0

You may try nested query.

$sql="select * from notes where title in 
     (select distinct title from notes 
       where user_id='$user_id' and goam='$current_goam')";

answered Jan 05 '12 at 02:27

KV Prajapati

93,659
19
148
186

score 0 · Answer 3 · answered Jan 05 '12 at 02:27

0

You are not selecting "goam" field from the database and so you dont have $info['goam'] set! Try this:

$usernotes = mysql_query("select distinct title, goam from notes where user_id='$user_id' and goam='$current_goam'");

answered Jan 05 '12 at 02:27

Petar Sabev

848
6
12

Luke Stevenson · Answer 4 · 2012-01-05T03:28:24.553

$user_id = mysql_real_escape_string( $user_id );
$current_goam = mysql_real_escape_string( $current_goam );

$usernotes = mysql_query( "SELECT title , goam
                           FROM notes
                           WHERE user_id='{$user_id}'
                                 AND goam='{$current_goam}'
                           GROUP BY title" );
# Personal preference here - I always user caps for the MySQL prowords,
# and I also like breaking it across lines for easy reading

print '<table border="1" cellspacing="5">'; 
while( $info = mysql_fetch_array( $usernotes ) ) 
{
  $linkURL = "mainpage.php?goam_id={$info['goam']}&note_id={$info['title']}";
  $linkTXT = $info['title'];
  # Again, personal preference...
  print "<tr><td><a href='{$linkURL}'>{$linkTXT}</a></td></tr>";
  # Removed orphan "<td></td>" from outside of table row tags 
} 
print '</table>';

OR, as the goam value is already set before you even do a query:

$user_id = mysql_real_escape_string( $user_id );
$unsanitized_goam = $current_goam;
$current_goam = mysql_real_escape_string( $current_goam );

$usernotes = mysql_query( "SELECT DISTINCT( title )
                           FROM notes
                           WHERE user_id='{$user_id}'
                                 AND goam='{$current_goam}'" );
# Personal preference here - I always user caps for the MySQL prowords,
# and I also like breaking it across lines for easy reading

print '<table border="1" cellspacing="5">'; 
while( $info = mysql_fetch_array( $usernotes ) ) 
{
  $linkURL = "mainpage.php?goam_id={$unsanitized_goam}&note_id={$info['title']}";
  $linkTXT = $info['title'];
  # Again, personal preference...
  print "<tr><td><a href='{$linkURL}'>{$linkTXT}</a></td></tr>";
  # Removed orphan "<td></td>" from outside of table row tags 
} 
print '</table>';

Mysql query using distinct and *

4 Answers4