Hi, I am working on result.php page, where I want to order data by date, but I want remove (don't print) duplicate records.I am using 3 tables:
1,results - short res
2,excercises - short exc
3,user - shor usr

I have this query:

[I]<div class="main">
		<table cellspacing="20">
			<tr>
			<th></th>
			<th></th>
			<th>Number of good excercises</th>
			<th>Number of bad excercises</th>
			<th>Effecient</th>
			</tr>
<?php 
$query1="SELECT DATE_FORMAT(res_time, '%D %M %Y') AS res_time, exc_excercise_name, res_result FROM res INNER JOIN exc ON res_exc_id=exc_id  WHERE res_usr_id=$_GET[id] GROUP BY res_time HAVING COUNT( res_time) = 1";
		$result1 = mysql_query($query1);
while (list($res_time,$exc_excercise_name,$res_result) = mysql_fetch_row($result1)) {
    		echo "<tr>",
			"<td class='time'>",
			$res_time,"</td>",
			"</tr>",
			"<tr>",
			"<td>",
			"</td>",
            "<td class='font'>",
            $exc_excercise_name,"</td>",
            "<td>",
            "</td>",
			"<td>",
            "</td>",
            "<td class='result'>",
            $res_result,"%","</td>",
        "</tr>";
		   
}
@mysql_close($connect);
			?>
			
		</table>
		<hr />	
</div> [/I]

But this query doesn't work and print only half of records.
Could somebody help me?

Thanks

Recommended Answers

All 4 Replies

SELECT [B]DISTINCT[/B] DATE_FORMAT
SELECT [B]DISTINCT[/B] DATE_FORMAT

it doesn't work...

it doesn't work...

GROUP BY uses column_list having same order and same number used with SELECT statement.

SELECT 
 DATE_FORMAT(res_time, '%D %M %Y') AS res_time, 
 exc_excercise_name, res_result 
          FROM res INNER JOIN exc ON res_exc_id=exc_id WHERE 
                res_usr_id='value' 
                   GROUP BY 
                         DATE_FORMAT(res_time, '%D %M %Y') AS res_time, 
 exc_excercise_name, res_result 
                HAVING COUNT
                  (DATE_FORMAT(res_time, '%D %M %Y')) = 'value'

GROUP BY uses column_list having same order and same number used with SELECT statement.

SELECT 
 DATE_FORMAT(res_time, '%D %M %Y') AS res_time, 
 exc_excercise_name, res_result 
          FROM res INNER JOIN exc ON res_exc_id=exc_id WHERE 
                res_usr_id='value' 
                   GROUP BY 
                         DATE_FORMAT(res_time, '%D %M %Y') AS res_time, 
 exc_excercise_name, res_result 
                HAVING COUNT
                  (DATE_FORMAT(res_time, '%D %M %Y')) = 'value'

what do you mean with 'value'?....
res_usr_id='value' - here I want obtain ID of user,who I click on other page, therefore was there res_usr_id=$_GET[id] and it works.
(DATE_FORMAT(res_time, '%D %M %Y')) = 'value' - here I want eliminate duplicate date records and therefore was there (DATE_FORMAT(res_time, '%D %M %Y')) = '1'.

Is it ok?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.