[SOLVED] Select only Rows with distinct values applying to 1 column

[rmelino]

Hello,

 

I'm hoping someone can help me here.  I have a db with the following structure:

 

name |    city    | state

 

john  | denver  | CO

bill      | boise      | ID

john  |las vegas| NV

joe    |troy        | MI

 

 

I'm trying to select the rows only if they have a unique value in the 'name' column.  I am outputting these rows to a table. 

 

If i had it working correctly, i'd have my table output only having three rows outputted in this example since the name 'john' shows up twice.  is there a way to do this?

 

My outputting php code looks something like this (i've left out the $query since i can't figure out what to put here to make this give me the rows i want)

 

$query="sql query here that i can't figure out!";
$result = mysql_query($query);
while ($row = @mysql_fetch_assoc($result)){
echo '
<tr>
	<td>' . $row['name'] . '</td>
	<td>' . $row['city] . '</td>
	<td>' . $row['state'] . '</td>
</tr>';

 

 

Thanks in advance for any help!

 

[dreamwest]

$result = mysql_query("SELECT distinct * FROM table group by name");
while ($row = mysql_fetch_assoc($result)){
echo "
<tr>
      <td>{$row['name']}</td>
      <td>{$row['city]}</td>
      <td>{$row['state']}</td>
</tr>";

[kickstart]

Hi

 

How do you want it to select the other fields in the event that name is not unique?

 

Ie, on your example do you want:-

 

john  | denver  | CO

bill      | boise      | ID

joe    |troy        | MI

 

or

 

bill      | boise      | ID

john  |las vegas| NV

joe    |troy        | MI

 

All the best

 

Keith

[rmelino]

Hello,

 

Thank you for the response.  I'm getting closer to figuring out my problem.  What i was given works for the simple example i provided and in trying to apply it to what i am actually doing i'm finding it more difficult so i thought i'd provide the complete example of what i'm trying to do below. 

 

What i need from the output:

 

-Select only rows with a unique value for the 'name' column.  (ie if the same name shows up multiple times in the name column, only select 1 of those rows which has the most resent request_date)

-Select only rows within the '$radius' specified (in my example i set radius to 200

-Order the final output by request_date (newest to oldest)

 

Here is my code so far:

 

//set center point
$center_lat = $user_lat;
$center_lng = $user_lng;
$radius = 200;

$result = mysql_query("SELECT distinct * FROM lesson_requests group by student_name order by request_date asc");

$query = sprintf("SELECT name, city, state, request_date, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM table HAVING distance < '%s' ORDER BY distance LIMIT 0 , 10",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($radius));

$result = mysql_query($query);
while ($row = @mysql_fetch_assoc($result)){

echo'
<tr>
	<td>' . $row['request_date'] . '</td>
	<td>' . $row['name'] . '</td>
	<td>' . $row['city'] . '</td>
	<td>' . $row['state'] . '</td>
</tr>';

 

Sorry i didn't provide this whole thing earlier but i thought i could figure it out!  thanks for your help so far...

[kickstart]

Hi

 

I need to do some playing around with distances based on longitude / latitude. Interesting.

 

Anyway, important bit is basing it on the most recent request_date.

 

This would bring back the name and most recent request date for any within 200:-

 

SELECT name, MAX(request_date)

FROM table

WHERE ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) < 200

 

I would then use that to do a JOIN, resulting in something like:-

 

$query = "SELECT name, city, state, request_date, lat, lng, ( 3959 * acos( cos( radians('".mysql_real_escape_string($center_lat)."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".mysql_real_escape_string($center_lng)."') ) + sin( radians('".mysql_real_escape_string($center_lat)."') ) * sin( radians( lat ) ) ) ) AS distance 
FROM table a
INNER JOIN (SELECT name, MAX(request_date) AS MaxRequestDate
FROM table
WHERE ( 3959 * acos( cos( radians('".mysql_real_escape_string($center_lat)."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".mysql_real_escape_string($center_lng)."') ) + sin( radians('".mysql_real_escape_string($center_lat)."') ) * sin( radians( lat ) ) ) ) < ".mysql_real_escape_string($radius).") b
ON a.name = b.name
AND a.request_date = b.MaxRequestDate
HAVING distance < ".mysql_real_escape_string($radius)." 
ORDER BY distance 
LIMIT 0 , 10";

 

You might be able to exclude the section HAVING distance < ".mysql_real_escape_string($radius)." , but it depends on whether name and request date combined are unique. If not you will struggle.

 

Hope that gives you some ideas.

 

All the best

 

Keith

[rmelino]

Thank you very much Keith.  I'm going to try this today and I'll report back and let you know what happens!

 

Thanks again...

[rmelino]

Hi Keith,

 

For some reason, this query isn't displaying any results.  Any ideas?

 

I even tried just your first example and it wasn't displaying any results...

 

SELECT name, MAX(request_date)

FROM table

WHERE ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) < 200

 

 

[kickstart]

Hi

 

Can you post your table structure and a couple of example lines and I will have a play.

 

With the first bit you will need to substitude the correct longitude and latitude.

 

All the best

 

Keith

[rmelino]

Hello,

 

Here is the table structure:

 

CREATE TABLE IF NOT EXISTS `lesson_requests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `parent_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `skill_level` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `zip` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `message` varchar(1000) COLLATE utf8_unicode_ci NOT NULL,
  `request_date` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lat` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `lng` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=48 ;

 

Here is the php to display all records in order from newest date to oldest within a 500 mile radius. 

 

<?
//Get central lat and lng of teacher from Users table - this is the lat and lng that the 2nd sql query is based off of		
$query="SELECT * FROM `Users` WHERE username = '$username' LIMIT 1";
$result = mysql_query($query);
while ($row = mysql_fetch_object($result)) {
$teacher_lat=$row->lat;
$teacher_lng=$row->lng;
}

//distance params
$center_lat = $teacher_lat;
$center_lng = $teacher_lng;
$teachradius = 500;

  $query = sprintf("SELECT student_name, parent_name, skill_level, phone, email, city, state, zip, message, request_date, lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) ) AS distance FROM lesson_requests HAVING distance < '%s' ORDER BY request_date DESC LIMIT 0 , 100",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($teachradius));


$result = mysql_query($query);
while ($row = @mysql_fetch_assoc($result)){

echo'
<tr onmouseover="mouse_event(this, \'hlt\');" onmouseout="mouse_event(this, \'\');">
	<td>' . $row['request_date'] . '</td>
	<td>' . $row['student_name'] . '</td>
	<td>' . $row['skill_level'] . '</td>
	<td>' . $row['city'] . '</td>
	<td>' . $row['state'] . '</td>
	<td>' . $row['zip'] . '</td>

</tr>';
}

?>

 

Let me know if you need anything else from me...

 

Thanks again!

[kickstart]

Hi

 

Main thing is that I missed the GROUP BY in the subselect ( )

 

$query = "SELECT a.student_name, city, state, request_date, lat, lng, ( 3959 * acos( cos( radians('".mysql_real_escape_string($center_lat)."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".mysql_real_escape_string($center_lng)."') ) + sin( radians('".mysql_real_escape_string($center_lat)."') ) * sin( radians( lat ) ) ) ) AS distance 
FROM lesson_requests a
INNER JOIN (SELECT student_name, MAX(request_date) AS MaxRequestDate
FROM lesson_requests
WHERE ( 3959 * acos( cos( radians('".mysql_real_escape_string($center_lat)."') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('".mysql_real_escape_string($center_lng)."') ) + sin( radians('".mysql_real_escape_string($center_lat)."') ) * sin( radians( lat ) ) ) ) < ".mysql_real_escape_string($radius)." GROUP BY student_name) b
ON a.student_name = b.student_name
AND a.request_date = b.MaxRequestDate
HAVING distance < ".mysql_real_escape_string($radius)." 
ORDER BY distance 
LIMIT 0 , 10";

 

All the best

 

Keith

[rmelino]

Yes that works perfectly!

 

Thank you thank you!

[rmelino]

Actually Keith I spoke to soon!

 

Everything is working except for the radius feature.  It will only show results when i have the radius set at 8000 which i know is inaccurate.  Any ideas?

[kickstart]

Hi

 

Sounds like an issue with the calculation or the actual longitude / latitudes in use.

 

Can you give me a few examples of longitude / latitude that you are using.

 

All the best

 

Keith

[rmelino]

Ok it was my error and i figured it out.  The db i was using to feed in the lng and lat wasn't converting them properly.

 

Thanks again Keith!