Friday, 15 June 2012

php - need help creating a mysql query -


Suppose I have a table called "user", which contains the following related columns:

    • Zip code

      Say I have a table called "Favorite_Shop" containing the following relevant columns:

      • shop_name
      • Username

        Assume I have an abstract called "zipData" The following are the following relevant columns:

        • Zipcode
        • Latitude
        • Loan

          Currently I have successfully Can run this code:

            $ lat = "49.886436"; // value "zipcode select zipcode = (users_zip_code)" or similar query should be obtained from zipData table $ lon = "- 97.14553"; // "Select from zipcode zipcode = (users_zip_code)" or the same question should be obtained from $ zodiac data by radius = 5; $ Query = "zipData WHERE (POW ((69.1 * lane - \" $ lon \ ") * cos ($ lat / 57.3), \" 2 \ ") + POW ((69.1 * (lat- \" $ Latitude \ "), \" 2 \ ")) & quot; ($ radius * $ radius)";   

          The above questions will display all other zip codes that are within the given radius of the supplied latitude / lamp, but I am not interested in knowing what other ZIP codes are ... I want to know what is inside the radius in the shops.

          Any help you can give will be greatly appreciated.

          ==== Because I realized that this is actually a bit more complex ====

          I need to do this ...

          • Recover the user's zipcode from the "Users" table
          • Locate the user
          • given in the "Username" column with the given user in a "Favorite_Shop" table Find all the shops within the radius P> Problems considering: In "Zipadata" There is no white space in the copy, but the zipcode in "users" and "shops" is white space for cosmetic reasons ... such as Canada's postal code in the format "A1A1A1"

            ==== Edit for posting solution ====

            The system says that I do not have permission to reply. My own question looks strange because with the help of others I already got the answer. As a work, I am editing the original post here is the solution ...

            OK, I understood it (answered with the help of people) ... this is what I did.

            This could possibly be a very simple and clean, so please feel better if you know better if you know better.

              $ query = "Select * Preferred_Shop WHERE 'Username' = '{$ _ session [' account_name ']}'"; $ Result = mysql_query ($ query); If (Mysql_errno ()) (die ( "Error". Mysql_errno ($ link). ":". Mysql_error ($ link));} $ Num_rows = mysql_num_rows ($ result); while ($ row = mysql_fetch_array ($ result) ) {// get the user's preferences and postal code $ query2 = "SELECT * FROM seekers WHERE 'username' = '{$ _ sESSION [' account_name ']}' '; $ Result2 = mysql_query ($ query2); If (mysql_errno ()) (die ( "error". Mysql_errno ($ link). ":". Mysql_error ($ link));} $ Num_rows2 = mysql_num_rows ($ result2); $ Row2 = mysql_fetch_array ($ result2); $ radius = $ Line2 ['radius']; // is not mentioned that column was in column but it does not make any difference ... it could have value from anywhere. Get the user's letters / logon // Remove white space from the postal code $ query3 = "SELECT * FROM zipData WHERE zipcode = replace ('{$ row2 [ 'postal']} ',' ',' ') "; $ Result3 = mysql_query ($ query3); If (mysql_errno ()) (die. (" error ". Mysql_errno ($ link)": ". Mysql_error ($ Link));} $ Num_rows3 = mysql_num_rows ($ result3); $ Row3 = mysql_fetch_array ($ result3); $ Axis = $ row3 ["lat"]; $ Longitude = $ row3 ["longitude"]; $ Query4 = "ZipData from the store_name, where in shops (paw ((69.1 * (loan - \" $ lon \ ") * cos ($ lat / 57.3)), \" 2 \ ") + pow ((69.1 * (* Lie - \ "$ Lieutenant \"), \ "2 \") & lt; ($ Radius * $ Radius) and Replace (stores .zipcode, '', '') = zipData.zipcode and Shops.shop_name = {$ line ['store_name']} "; $ Result4 = mysql_query ($ query4); If (mysql_errno ()) (die ( "Error". Mysql_errno ($ link). ":". Mysql_error ($ link));} $ Num_rows4 = mysql_num_rows ($ result4); $ Num_jobs = $ num_rows4; $ I 0 = ; while ($ row4 = mysql_fetch_array ($ result4)) {$ Dukanare [$ i] = $ row4 [ "shop_name"]; $ I ++;} Var_dump ($ shopArray);    

            You must be included in the table of stores

              SELECT shop_name FROM zipData, stores WHERE (POW (+ 69.1 * lon - \ "$ lon \") * cos ($ lat / 57.3)), + POW ((69.1 * \ "$ late \"), \ "2 \") & Lt; ($ Radius * $ radius) and stores. Zipcode = zipdata.zipcode    

    No comments:

    Post a Comment