rmelino Posted August 22, 2009 Share Posted August 22, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/ Share on other sites More sharing options...
dreamwest Posted August 22, 2009 Share Posted August 22, 2009 $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>"; Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-903794 Share on other sites More sharing options...
kickstart Posted August 22, 2009 Share Posted August 22, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-903878 Share on other sites More sharing options...
rmelino Posted August 22, 2009 Author Share Posted August 22, 2009 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... Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-904013 Share on other sites More sharing options...
kickstart Posted August 22, 2009 Share Posted August 22, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-904032 Share on other sites More sharing options...
rmelino Posted August 24, 2009 Author Share Posted August 24, 2009 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... Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905149 Share on other sites More sharing options...
rmelino Posted August 24, 2009 Author Share Posted August 24, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905371 Share on other sites More sharing options...
kickstart Posted August 24, 2009 Share Posted August 24, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905380 Share on other sites More sharing options...
rmelino Posted August 24, 2009 Author Share Posted August 24, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905406 Share on other sites More sharing options...
kickstart Posted August 24, 2009 Share Posted August 24, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905424 Share on other sites More sharing options...
rmelino Posted August 25, 2009 Author Share Posted August 25, 2009 Yes that works perfectly! Thank you thank you! Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905441 Share on other sites More sharing options...
rmelino Posted August 25, 2009 Author Share Posted August 25, 2009 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? Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905556 Share on other sites More sharing options...
kickstart Posted August 25, 2009 Share Posted August 25, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-905639 Share on other sites More sharing options...
rmelino Posted August 26, 2009 Author Share Posted August 26, 2009 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! Quote Link to comment https://forums.phpfreaks.com/topic/171360-solved-select-only-rows-with-distinct-values-applying-to-1-column/#findComment-906357 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.