Rewrite MySQL query to make it more efficient/easier in PHP

I am trying to pull lat/long from a table using a share ID from another.  I got the query to work like this but it sure seem clunky...can someone give me a rewritten sample of a better way to execute this query to achieve the same result?

$query = "SELECT Residential_RESI.sMLSNumber, Residential_RESI.sLegalDescription, Residential_RESI.sCity, Residential_RESI.sState, Residential_RESI.sListPrice, Residential_RESI.sSquareFootage, Residential_RESI.sBedrooms, Residential_RESI.sTotalBaths, Residential_RESI.sAgentFirstName, Residential_RESI.sAgentLastName, Residential_RESI.sAgentPhone, Residential_RESI.sListAgentEmail, Residential_RESI.sMLSNumber, Residential_RESI.sStatus, Residential_RESI.sOfficeLong, Residential_RESI.sOfficePhone1, Residential_RESI.sOfficeAddress, Residential_RESI.sMarketingRemarks, Residential_RESI.sMasterHOAFee, Residential_RESI.sTaxes, Residential_RESI.sTaxYear, Residential_RESI.sTotalTaxBill, Residential_RESI.sAssessmentFees, Residential_RESI.sWater, Residential_RESI.sSewer, Residential_RESI.sRegionShortName, Residential_RESI.sStreetFullName, Residential_RESI.sAgentFaxPhone, Residential_RESI.sAgentPictureFile, Residential_RESI.sAgentWebSite, Residential_RESI.sDOM, Residential_RESI.sPricePerSquareFoot, property_coordinates.sLatitude, property_coordinates.sLongitude, property_coordinates.sMLSNumber FROM Residential_RESI, property_coordinates
        WHERE 
         Residential_RESI.sMLSNumber=property_coordinates.sMLSNumber
		AND sLatitude is not NULL and sLatitude != '' 
and sLongitude is not NULL and sLongitude != ''";

Open in new window

pda4meAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rfportillaCommented:
Do you really need all of those fields?  If not, cut back to just the fields that you need.

Just for readability, I would change the format to something like the following:


$query = "
SELECT rr.sMLSNumber,
      rr.sLegalDescription, rr.sCity, rr.sState, rr.sListPrice, rr.sSquareFootage,
      rr.sBedrooms, rr.sTotalBaths, rr.sAgentFirstName, rr.sAgentLastName,
      rr.sAgentPhone, rr.sListAgentEmail, rr.sMLSNumber, rr.sStatus, rr.sOfficeLong,
      rr.sOfficePhone1, rr.sOfficeAddress, rr.sMarketingRemarks, rr.sMasterHOAFee,
      rr.sTaxes, rr.sTaxYear, rr.sTotalTaxBill, rr.sAssessmentFees, rr.sWater,
      rr.sSewer, rr.sRegionShortName, rr.sStreetFullName, rr.sAgentFaxPhone,
      rr.sAgentPictureFile, rr.sAgentWebSite, rr.sDOM, rr.sPricePerSquareFoot,
      pc.sLatitude, pc.sLongitude, pc.sMLSNumber
FROM Residential_RESI rr, property_coordinates pc
WHERE rr.sMLSNumber=pc.sMLSNumber
      AND pc.sLatitude is not NULL
      AND pc.sLatitude != ''
      AND pc.sLongitude is not NULL
      AND pc.sLongitude != ''
";

In terms of efficiency, this should be pretty quick.

If you want to cut back on typing, you could just use * instead of naming fields, but I prefer to name the fields explicitly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aaron TomoskyDirector of Solutions ConsultingCommented:
Don't ever use * in production. A little typing is better than a field addition later that breaks stuff
pda4meAuthor Commented:
Thanks
rfportillaCommented:
@aarontomosky I was just going over some of my comments and caught your comment above (*).  I agree whole-heartedly, but still so many people do this.  It creates havoc when there are added fields are the order of the fields is changed.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.