DaniWeb Forum Index > Web Development > PHP

don't print duplicate records

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: May 2009

Posts: 10

Reputation: raul66 is an unknown quantity at this point

Solved Threads: 0

raul66

Offline

Newbie Poster

don't print duplicate records

Jun 8th, 2009

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:

Help with Code Tags

(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

•

Join Date: Dec 2007

Posts: 97

Reputation: VIeditorlover is an unknown quantity at this point

Solved Threads: 5

VIeditorlover VIeditorlover is offline

Offline

Junior Poster in Training

Re: don't print duplicate records

Jun 8th, 2009

Help with Code Tags

(Toggle Plain Text)

SELECT DISTINCT DATE_FORMAT

•

Join Date: May 2009

Posts: 10

Reputation: raul66 is an unknown quantity at this point

Solved Threads: 0

raul66

Offline

Newbie Poster

Re: don't print duplicate records

Jun 8th, 2009

•

Originally Posted by VIeditorlover

Help with Code Tags

(Toggle Plain Text)
SELECT DISTINCT DATE_FORMAT
SELECT DISTINCT DATE_FORMAT

it doesn't work...

•

Join Date: Oct 2008

Posts: 2,612

Reputation: adatapost has much to be proud of

Solved Threads: 463

adatapost

Offline

Posting Maven

Re: don't print duplicate records

Jun 8th, 2009

•

Originally Posted by raul66

it doesn't work...

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

 Help with Code Tags 
 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'

Failure is not fatal, but failure to change might be. - John Wooden

•

Join Date: May 2009

Posts: 10

Reputation: raul66 is an unknown quantity at this point

Solved Threads: 0

raul66

Offline

Newbie Poster

Re: don't print duplicate records

Jun 8th, 2009

•

Originally Posted by adatapost

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

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?