ROCKETTHEME IS CLOSING ON JUNE 30, 2025. As a thank-you to our community, enjoy 50% off all themes with the promo code THANKYOU before we shut down.
Read our Farewell Blog Post for more details.
A little help with a SQL Query?
-
-
- Adamck
- Elite Rocketeer
- Posts: 546
- Thanks: 2
- Web Developer, IT Assistant, Graphics design, App Developer
-
Hi Guys.
Ok so im a little stumped with this one...
Im using a plugin which allows a where clause within it.
So in order to do this correctly i can only use SQL query and no PHP/JS can be used...
Also as its just a where clause i cannot define a Variable either....
The query is similar to this...SELECT * FROM jos_users2 WHERE jos_users2.user_pcode LIKE CONCAT("%",LEFT('{inspect_new___insp_pcode}', 3),"%") AND jos_users2.user_type = 'Engineer'
This is ajax so will update after a postcode is entered in the field {inspect_new___insp_pcode}.
The code above works great for postcodes that start with 3 chars (e.g PR9 8HH) BUT it wont work for longer post codes like 'SA32 5HH' as i then require the first 4 chars 'SA32'.
What this query does is selects a relevant user from the user tabel who has that partial post code in his post_code field.
The postcode field for each user will be like
Engineer 1 = [PR9 PR8 PR7]
Engineer 2 = [SA31 SA32 SA33]
So... any help please! - Kiss my RSS
-
-
-
- Adamck
- Elite Rocketeer
- Posts: 546
- Thanks: 2
- Web Developer, IT Assistant, Graphics design, App Developer
-
I think i have sorted it now...
jos_users2.user_pcode LIKE CONCAT("%",LEFT('{inspect_new___insp_pcode}', 3),SPACE(1),"%") AND jos_users2.user_type = 'Engineer' OR jos_users2.user_pcode LIKE CONCAT("%",LEFT('{inspect_new___insp_pcode}', 4),SPACE(1),"%") AND jos_users2.user_type = 'Engineer'
A little long winded... basically this will take the postcode 'SA32 8JJ' and then strip it left to 'SA3' then it will add a space 'SA3 ' and search for that which shouldnt exist then it will do the same as above but with 4 chars making it 'SA32 ' with the space which wille exist.
Voila! Seems to work.
Anyone got any better idea's on this? - Kiss my RSS
-
Time to create page: 0.053 seconds