# Solved: Removing duplicates in mysql query output



## ishak (May 2, 2005)

Hi guys!

I have mysql database table which contains data that looks like below:

student_id student_name subject grade
01 John Math C
01 John Physics B
02 Mary Physics A
02 Mary Math C
03 Joseph Math B
03 Joseph Physics D

I want to run a query and get results as shown below.

student_id student_name Math Physics 
01 John C B
02 Mary A C
03 Joseph B D

Please help, how can I achieve that?

Is-hak


----------



## dukevyner (Nov 4, 2011)

> <?php mysql_connect ('localhost', 'db_user', 'user_password') ;
> mysql_select_db ('db_name');?>


 at the start of your page replacing "db_user" and "user_password" with your username and password and "db_name" with the name of your databse



> <?php
> $sql = "SELECT * FROM table_name;
> 
> $result = mysql_query($sql) or print ("Can't select database.
> ...


and so on and so forth then just use


> <?php echo $id; ?> <php echo $name;?>


 and so on and so forth
replacing "table_name" with the table that the info is in.

Hope this helps

-Luke


----------



## allnodcoms (Jun 30, 2007)

Hi Ishak,

The command you're looking for is DISTINCT. For example:

```
SELECT DISTINCT name FROM my_table
```
will return only a single instance of each name - no duplicates...

Danny


----------



## ishak (May 2, 2005)

allnodcoms said:


> Hi Ishak,
> 
> The command you're looking for is DISTINCT. For example:
> 
> ...


Thank you for the reply, bu the problem is not solved yet. The command *DISTINCT *could not work here since column *grade* does not have duplicate value. Second, what I need is to transpose this column *grade* while making column *student_id* and *student_name * distinct values. Let me repeat this example:

ID Name grade
1 John A
1 John B
2 Moses C
2 Moses B

Query Output:
ID Name grade1 grade2 
1 John A B
2 Moses C B

Thanks & Regard
Is-hak


----------



## php guy (Dec 17, 2004)

I don't believe there's a single query you could write for this as getQuery reads row by row.

May I ask why you store your data that way? Instead of having multiple rows for the same student with one grade per row, why not make a row unique to each student and update the table structure if you need more columns?

If you're stuck on pulling data in this format, you'll just have to format it with php to the results you want. You could get a list of each unique student id, then go through the list calling all the grades for whichever id you're on and printing it as you go.

If you're set on using just one query with that data format, you could query the whole table and parse the data into a 2-dimensional array. Something in the form of $grades[sid]['name'/'grades'] with sid being student id, name holding the name associated with that student id and grades being a list you push() each grade you find to the associated student id's list. Then, you simply echo your data by looping through your array and pop()ing each grade for each sid found. Note: this will only work if your student id's are linear with no gaps in numbers. If your student id numbers are all over the place, you'd need to store that data in the second dimension of the array with the names and grades (ie $grades[id]['sid'/'name'/'grades']).

I hope that made sense. Let me know if you need any help with any of these suggestions.


----------



## allnodcoms (Jun 30, 2007)

PHP Guy is right, I can't think (off the top of my head, but it's still early!) of a single query escape here. I'd use *distinct* to get a list of students, then loop over that result set to get the grades for each student.
That would definitely solve your problem.

Danny


----------



## ishak (May 2, 2005)

Let me give full scenario of the case. In fact I have three tables as shown below:

*student:*
stud_id stud_name
01 John
02 Felix
03 Moses
............................................
*subject:*
subject_id   subject_name
s1 Math
s2 Physics
s3 Geography
...........................................................
*assessment:*
subject_id student_id grade
s1 01 A
s2 01 B
s3 01 C
s1 02 D
s2 02  A
s3 02 B
s1 03 C
s2 03 A
s3 03 B

Output Required:
I want to run a query that would result in the following output:
...................................................................................................................
student_id grade1 grade2 grade3
01 A B C 
02 D A B
03 C A B

That's all I need. I'm very sorry my previous example was not obvious.
Regard,
Is-hak


----------



## php guy (Dec 17, 2004)

Again, this is something that would require multiple queries (since you have multiple tables). I'd suggest editing the structure of the tables to make the database a little more efficient. If you're set on that structure though, here's a little something I came up with to print all that data to a table like you showed

```
<?php
//your mysql connection and database here
$conn = mysql_connect("localhost","root");
$rs = @mysql_select_db("grades") or die("Error: DB"); //replace grades with whatever your db is called
//this chunk gets a list of all the subjects and stores the names and id in 2d array to use later
//$len being the final length of the array (total number of classes
$len = 0;
$rs = mysql_query("SELECT * FROM subject", $conn);
while($subject = mysql_fetch_array($rs))
{
	$sub[$len]['name'] = $subject['subname'];
	$sub[$len++]['id'] = $subject['subid'];
}
$len--;
//write top line of table for student name and all class names found in prev search
echo "<table border=\"1\">[TR][TD]Name[/TD]";
for($i=0;$i<=$len;$i++) echo "[TD]".$sub[$i]['name']."[/TD]";
echo "[/TR]";

//loop through each student and print whatever grades they got in each subject found
//will write a "-" if no grade data is found for a student for a class
$rs = mysql_query("SELECT * FROM student", $conn);
while($student = mysql_fetch_array($rs))
{
	echo "[TR][TD]".$student['sname']."[/TD]";
	//print out each grade in seperate [TD]'s
	for($i=0;$i<=$len;$i++)
	{
		$grd = mysql_fetch_array(mysql_query("SELECT * FROM assessment WHERE sid = ".$student['sid']." AND subid = '".$sub[$i]['id']."'", $conn));
		if(isset($grd['grade'])) echo "[TD]".$grd['grade']."[/TD]";
		else echo "[TD]-[/TD]";
	}
	echo "[/TR]";
}
echo "</table">;
?>
```
This just echos out a table as it finds the data, though it could be easily modified to save the data to arrays or other data structures for later use in code. I commented the main chunks of code so you can understand the logic.

Note: I took guesses at column names, but you'll have to go through and change them to match your own (ie. for student id columns, I used 'sid'

I hope this is what you were looking for. If you have any questions about this script, let me know.


----------



## ishak (May 2, 2005)

Hi php guy! Thanx so much for your help. This is what exactly I was looking for. I have run the code and the issue has now been resolved. See you then!
Is-hak


----------

