SELECT Statement

bogey2

Any help will be greatly appreciated.

I have at MYSQL table called "results" w/ the following fields:

tournaments / team / participants / fish / weight / lunker

Lake1 / team1 / participants1 / fish1 / weight1 / lunker1
Lake1 / team2 / participants1 / fish1 / weight1 / lunker1
Lake1 / team3 / / fish2 / weight2 / lunker2
Lake1 / team4 / / fish3 / weight3 / lunker2

Here is some of my code:

<?
$tournament = $_POST['tournament'];
$result = mysql_query("SELECT team, participants, fish, weight, lunker, FROM results WHERE tournament = '$tournament' ORDER BY weight DESC ") or die(mysql_error());
echo ",h1 align='center'>$tournament<br></h1>";
echo "<table bgcolor-#000099 align=center border='1' cellpadding='2' cellspacing='0' width='600'>";
echo "<tr> <th>Team</th> <th>Fish</th><th>Weight</th><th>Lunker</th></tr>";
while($row = mysql_fetch_array( $result )) {
echo "<tr>";
echo "<td align='center'>";
if ($row['participants'] == '') echo $row['team'];
else echo $row['participants'];

The problem is that the table has duplicates in the participants field so the results display are misleading show participants two times.

The results display as follows:

I would like the results to display like this:

Lake1 / team2 / participants1 / fish1 / weight1 / lunker1
Lake1 / team3 / / fish2 / weight2 / lunker2
Lake1 / team4 / / fish3 / weight3 / lunker2

Thanks in advance for your help.

Fernashes

First off, you might want to consider normalising your database. It keeps redundation down... though I'm not quite sure you need it, I can't see enough data yet.

You could try this SQL query:

SELECT [B]DISTINCT [/B] team, participants, fish, weight, lunker, 
FROM results 
WHERE tournament = '$tournament' 
ORDER BY weight DESC

The "distinct" should only turn up multiple values once... though I'm sure someone will correct me if I'm wrong. 😃

By the way... it looks like you're taking all teh fields from the table "results" - in that case, you can use "SELECT * FROM .... "

Good luck!

Lars_Berg

As distinct is applied to all columns it is not the solution in this case. Supressing repeating values is better done in the presentation layer. Keep the value of the participant from the previous row in the result and if it is the same as in the current row don't display that value.

bogey2

Thanks to all

I did try the DISTINCT but to no effect. Lars I think you are correct but I am not sure how to do this. I guess I am quite new at this. Can you recommend a source of help? Thanks again

DoctorWho

Have you tried applying GROUP BY at the end of your sql statment.

As IN

SELECT DISTINCT team, participants, fish, weight, lunker,
FROM results
WHERE tournament = '$tournament'
ORDER BY weight DESC GROUP BY tournament

Lars_Berg

The principle is very simple.

$p = "";
while($row = mysql_fetch_array( $result )) {
 if ($p != $row['participants']) {
    $p = $row['participants'];
    echo $p;
 }
// display rest of columns
}