15 {{ upvoteCount | shortNum }}
15 {{ upvoteCount | shortNum }}

MSQL Query

by FirstSocialApps
13 replies
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
#msql #query
  • Profile picture of the author jacktackett
    try removing the keyword DISTINCT and see if that helps.
    --Jack
    Signature
    Let's get Tim the kidney he needs!HELP Tim
    Mega Monster WSO for KimW http://ow.ly/4JdHm


    {{ DiscussionBoard.errors[7271132].message }}
  • Profile picture of the author 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.
    Signature
    Create Facebook deals, sweepstakes, contests, polls, custom pages and more without a monthly fee!

    Generate thousands of leads in any niche in under 60 seconds!
    {{ DiscussionBoard.errors[7271138].message }}
    • Profile picture of the author cgimaster
      Originally Posted by FirstSocialApps View Post

      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 ?
      {{ DiscussionBoard.errors[7271162].message }}
      • Profile picture of the author FirstSocialApps
        Originally Posted by cgimaster View Post

        Have you tried using " GROUP BY zip " instead of DISTINCT ?
        No Ill give it a try though.
        Signature
        Create Facebook deals, sweepstakes, contests, polls, custom pages and more without a monthly fee!

        Generate thousands of leads in any niche in under 60 seconds!
        {{ DiscussionBoard.errors[7271259].message }}
        • Profile picture of the author viescripts
          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
          {{ DiscussionBoard.errors[7273232].message }}
          • Profile picture of the author cgimaster
            Originally Posted by viescripts View Post

            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.
            {{ DiscussionBoard.errors[7273465].message }}
            • Profile picture of the author FirstSocialApps
              Originally Posted by cgimaster View Post

              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.
              Signature
              Create Facebook deals, sweepstakes, contests, polls, custom pages and more without a monthly fee!

              Generate thousands of leads in any niche in under 60 seconds!
              {{ DiscussionBoard.errors[7273542].message }}
              • Profile picture of the author wayfarer
                Originally Posted by FirstSocialApps View Post

                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.
                Signature
                I build web things, server things. I help build the startup Veenome. | Remote Programming Jobs
                {{ DiscussionBoard.errors[7273855].message }}
              • Profile picture of the author SteveJohnson
                Originally Posted by FirstSocialApps View Post

                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.
                Signature

                The 2nd Amendment, 1789 - The Original Homeland Security.

                Gun control means never having to say, "I missed you."

                {{ DiscussionBoard.errors[7280724].message }}
  • Profile picture of the author 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.
    {{ DiscussionBoard.errors[7274802].message }}
  • Profile picture of the author cgimaster
    Essentially GROUP BY lets you use aggregate operators (MAX, SUM, GROUP_CONCAT, ..., or a HAVING clause). DISTINCT just removes duplicates.
    {{ DiscussionBoard.errors[7276897].message }}

Trending Topics