MSQL Query

FirstSocialApps · 10-31-2012, 09:45 PM

Im using the following query which returns a correct result, however it only returns one result when I know for sure there is more than one in the radius. Any advice would be appreciated.

SELECT DISTINCT zip, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM zips HAVING distance < 40

jacktackett · 10-31-2012, 10:09 PM

try removing the keyword DISTINCT and see if that helps.
--Jack

FirstSocialApps · 10-31-2012, 10:11 PM

Well Im sure that there are more distinct rows, but I did notice this, when I take the MSQL resource and change it to an array and print_r it then it shows one result, if I change it to an assoc array and iterate through and extract it shows all the results. So I got it working now but would still like to know why it only shows all the results with an assoc array and extracting.

cgimaster · 10-31-2012, 10:19 PM

Quote:

Originally Posted by FirstSocialApps

Well Im sure that there are more distinct rows, but I did notice this, when I take the MSQL resource and change it to an array and print_r it then it shows one result, if I change it to an assoc array and iterate through and extract it shows all the results. So I got it working now but would still like to know why it only shows all the results with an assoc array and extracting.

Have you tried using " GROUP BY zip " instead of DISTINCT ?

FirstSocialApps · 10-31-2012, 10:46 PM

Quote:

Originally Posted by cgimaster

Have you tried using " GROUP BY zip " instead of DISTINCT ?

No Ill give it a try though.

viescripts · 11-01-2012, 08:59 AM

undoubtedly remove DISTINCT.
DISTINCT selects only exclusive field names for all fields after the DISTINCT.

e.g. if you are searching for through same ZIP, the result will be a single line, because all the other ZIP codes after first row are repeating.

The same rule is applied on your formula, it should be DISTINCT.

If you really need to use DISTINCT, first think over which field it's applied, then perform 2 mysql queries one over another to the same DB as in example

SELECT DISTINCT 'the unique field name' FROM (SELECT zip, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM zips HAVING distance < 40) WHERE 'your condition here'

Warning: the fields in the second select should be included into first select

Hope wrote not too complex

cgimaster · 11-01-2012, 09:51 AM

Quote:

Originally Posted by viescripts

SELECT DISTINCT 'the unique field name' FROM (SELECT zip, ( 3959 * acos( cos( radians() ) * cos( radians( lat ) ) * cos( radians( lng ) - radians() ) + sin( radians() ) * sin( radians( lat ) ) ) ) AS distance FROM zips HAVING distance < 40) WHERE 'your condition here'

Would be a lot simpler by using GROUP BY then requerying everything like your sample.

With GROUP BY it will only get 1 result per unique ZIP grouping all of the same kind which seems to be the goal here I believe.

FirstSocialApps · 11-01-2012, 10:05 AM

Quote:

Originally Posted by cgimaster

With GROUP BY it will only get 1 result per unique ZIP grouping all of the same kind which seems to be the goal here I believe.

I had always thought that GROUP BY and DISTINCT were essentially equivalent. Also that doesnt explain why it only returns 1 result when extracting as an array by all the results when extracting as an assoc array.

For example

Code:

=mysql_fetch_array()
print_r()

Returns 1 result
where

Code:

while (=mysql_fetch_assoc()){
                                                           extract();
                                                           echo "<br>";
                                                         }

returns all the results.

edit:
LOL the forum pulls out the varables even when typed on a code block. Thats about a big pain in the rear in a coding forum.

wayfarer · 11-01-2012, 10:58 AM

Quote:

Originally Posted by FirstSocialApps

edit:
LOL the forum pulls out the varables even when typed on a code block. Thats about a big pain in the rear in a coding forum.

Yeah, they've never fixed that. You can prevent it yourself though. When replying, scroll down a bit (below the editor), to the section titled "Additional Options". Uncheck the parts starting with "Automatically...". One of those messes up code.

KirkMcD · 11-01-2012, 02:08 PM

Of course mysql_fetch_assoc is pulling in all the rows, based on what you posted you have it in a while loop and the mysql_fetch_array isn't in a loop.

FirstSocialApps · 11-01-2012, 06:05 PM

Quote:

Originally Posted by KirkMcD

Of course mysql_fetch_assoc is pulling in all the rows, based on what you posted you have it in a while loop and the mysql_fetch_array isn't in a loop.

Yes I didnt post correctly, sorry about that, I was on the tail end of a 19 hour session and half asleep.

cgimaster · 11-01-2012, 10:50 PM

Essentially GROUP BY lets you use aggregate operators (MAX, SUM, GROUP_CONCAT, ..., or a HAVING clause). DISTINCT just removes duplicates.

SteveJohnson · 11-02-2012, 02:58 PM

Quote:

Originally Posted by FirstSocialApps

Also that doesnt explain why it only returns 1 result when extracting as an array by all the results when extracting as an assoc array.

mysql_fetch_array() and mysql_fetch_assoc() are both functions that operate on a mysql resource - the results of the query. Each call to the function returns a row, then advances the internal pointer of the resource to the next row if there is one.

Code:

$row[] = mysql_fetch_assoc( $result );
$row[] = mysql_fetch_assoc( $result );
$row[] = mysql_fetch_assoc( $result );
$row[] = mysql_fetch_assoc( $result );
$row[] = mysql_fetch_assoc( $result );
$row[] = mysql_fetch_assoc( $result );
$row[] = mysql_fetch_assoc( $result );
$row[] = mysql_fetch_assoc( $result );

gives essentially the same result as

Code:

while ( $r = mysql_fetch_assoc( $result ) ) {
  $row[] = $r;
}

except with the first, you wouldn't know where to stop as both functions return false when the pointer reaches the end of the resource.

FirstSocialApps · 11-02-2012, 11:17 PM

Quote:

Originally Posted by FirstSocialApps

I was on the tail end of a 19 hour session and half asleep.

LOL I know, thanks for the tip though, have a look at my last post. This entire thread was the result of a 19 hour coding session and me being brain fried and half out of it. Id delete it if I knew how.

10-31-2012, 09:45 PM	#1
FirstSocialApps Digital Carpenter War Room Member Join Date: Aug 2012 Location: Pennsylvania Posts: 855 Thanks: 170 Thanked 209 Times in 174 Posts Social Networking	MSQL Query Im using the following query which returns a correct result, however it only returns one result when I know for sure there is more than one in the radius. Any advice would be appreciated. SELECT DISTINCT zip, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM zips HAVING distance < 40
	Viral Facebook Apps

10-31-2012, 10:09 PM	#2
jacktackett Socrates: I drank What? War Room Member Join Date: May 2007 Location: Cary,NC , USA. Posts: 1,529 Blog Entries: 1 Thanks: 1,335 Thanked 672 Times in 362 Posts Social Networking Contact Info	Re: MSQL Query try removing the keyword DISTINCT and see if that helps. --Jack
	Mega Monster WSO for KimW http://ow.ly/4JdHm

10-31-2012, 10:11 PM	#3
FirstSocialApps Digital Carpenter War Room Member Join Date: Aug 2012 Location: Pennsylvania Posts: 855 Thanks: 170 Thanked 209 Times in 174 Posts Social Networking	Re: MSQL Query Well Im sure that there are more distinct rows, but I did notice this, when I take the MSQL resource and change it to an array and print_r it then it shows one result, if I change it to an assoc array and iterate through and extract it shows all the results. So I got it working now but would still like to know why it only shows all the results with an assoc array and extracting.
	Viral Facebook Apps

11-01-2012, 08:59 AM	#6
viescripts Active Warrior Join Date: Oct 2012 Posts: 94 Thanks: 13 Thanked 12 Times in 11 Posts	Re: MSQL Query undoubtedly remove DISTINCT. DISTINCT selects only exclusive field names for all fields after the DISTINCT. e.g. if you are searching for through same ZIP, the result will be a single line, because all the other ZIP codes after first row are repeating. The same rule is applied on your formula, it should be DISTINCT. If you really need to use DISTINCT, first think over which field it's applied, then perform 2 mysql queries one over another to the same DB as in example SELECT DISTINCT 'the unique field name' FROM (SELECT zip, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM zips HAVING distance < 40) WHERE 'your condition here' Warning: the fields in the second select should be included into first select Hope wrote not too complex

11-01-2012, 02:08 PM	#10
KirkMcD Senior Warrior Member War Room Member Join Date: Apr 2006 Location: , , USA. Posts: 2,866 Thanks: 55 Thanked 225 Times in 208 Posts	Re: MSQL Query Of course mysql_fetch_assoc is pulling in all the rows, based on what you posted you have it in a while loop and the mysql_fetch_array isn't in a loop.

11-01-2012, 10:50 PM	#12
cgimaster HyperActive Warrior War Room Member Join Date: Jun 2012 Posts: 138 Thanks: 15 Thanked 26 Times in 25 Posts	Re: MSQL Query Essentially GROUP BY lets you use aggregate operators (MAX, SUM, GROUP_CONCAT, ..., or a HAVING clause). DISTINCT just removes duplicates.