/    Sign up×
Community /Pin to ProfileBookmark

[RESOLVED] Problems with Complex SQL

Copy linkTweet thisAlerts:
May 12.2009

Hello,

I’m having a problem with my sql, mysql keeps throwing errors at me:

[CODE]Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:wampwwwprojectmarkerdata2.php on line 45[/CODE]

Line 45 refers to the query below:

query:

[code=php]
$query = “SELECT postcode1, postcode2, postcode, name, address1, address2, address3, address4, tel, email,
website, lat, long, type, (SQRT(POW((b.x – a.x), 2) + POW((b.y – a.y), 2))/1000) * 0.621 AS distance
FROM postcodes a, postcodes b, dealers
WHERE a.outcode LIKE ‘”.$postcode.”‘ AND b.outcode = dealers.postcode_1
HAVING (distance < ‘”.$area.”‘)
ORDER BY distance asc LIMIT 10″;
[/code]

To be honest i don’t really understand the code section with all the brackets. is there an obvious flaw? if not i can also display my database tables.

to post a comment
PHP

3 Comments(s) ↴

Copy linkTweet thisAlerts:
@copesauthorMay 12.2009 — Here is the origional sql query which works correctly on another site:

[code=php]$query = "SELECT postcode_1, postcode_2, shop_name, shop_address_1, shop_address_2, shop_address_3, shop_phone,
shop_email, (SQRT(POW((b.x - a.x), 2) + POW((b.y - a.y), 2))/1000) * 0.621 AS distance
FROM postcodes a, postcodes b, dealers
WHERE ssc_or_ssc_plus='ssc'
AND a.outcode LIKE '".$postcode."' AND b.outcode = dealers.postcode_1
HAVING (distance < '".$area."')
ORDER BY distance asc LIMIT 20";[/code]

Both sites have two tables setup in the database:

One called postcode (this is exactly the same for both sites) and one called dealers which i've changed the field names in the query to refer to the new database.
Copy linkTweet thisAlerts:
@chazzyMay 12.2009 — post a few lines above/below. off to the php forum this one shall go.
Copy linkTweet thisAlerts:
@copesauthorMay 13.2009 — Problem fixed: LONG and TYPE are both reserved mysql words. "oops" changed to lng and typ. Works fine now.
×

Success!

Help @copes spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.18,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,

tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,
)...