Ad:

PHP Discussion Thread
Unsolved
Views: 834

Jun 8th, 2009

don't print duplicate records

Expand Post »

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:

(Toggle Plain Text)

<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>

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

Thanks

Last edited by Tekmaven; Jun 8th, 2009 at 4:41 pm. Reason: Code Tags

Similar Threads

how to delete duplicate record in a table by using SQL query in MS SQL
Quick Search(Finding Duplicate Records) in Java
Delete duplicate records in MS ACCESS in MS Access and FileMaker Pro
How to remove duplicate records in MS SQL
Exclude duplicate records in a JOIN in MS SQL

raul66

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since May 2009

Permalink

Jun 8th, 2009

Re: don't print duplicate records

(Toggle Plain Text)

SELECT DISTINCT DATE_FORMAT

VIeditorlover

Reputation Points: 10

Solved Threads: 9

Junior Poster

Offline

137 posts
since Dec 2007

Permalink

Jun 8th, 2009

Re: don't print duplicate records

Click to Expand / Collapse Quote originally posted by VIeditorlover ...

(Toggle Plain Text)
SELECT DISTINCT DATE_FORMAT
SELECT DISTINCT DATE_FORMAT

it doesn't work...

raul66

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since May 2009

Permalink

Jun 8th, 2009

Re: don't print duplicate records

Click to Expand / Collapse Quote originally posted by raul66 ...

it doesn't work...

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

PHP Syntax (Toggle Plain Text)
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'
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'

adatapost

Moderator

Reputation Points: 2136

Solved Threads: 1228

Posting Genius

Offline

6,527 posts
since Oct 2008

Permalink

Jun 8th, 2009

Re: don't print duplicate records

Click to Expand / Collapse Quote originally posted by adatapost ...

GROUP BY uses column_list having same order and same number used with SELECT statement.
PHP Syntax (Toggle Plain Text)
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'
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?

raul66

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

10 posts
since May 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: Retrieving the size of a rotated image

Next Thread in PHP Forum Timeline: Help with php script to export mysql data and import data

DaniWeb Message

Cancel Changes

User Name
Password