J!Extensions Store™
Forum
Welcome, Guest
Please Login to access forum.
[SOLVED]SQL for adding fields to custom URLs (1 viewing) 
Go to bottom
TOPIC: [SOLVED]SQL for adding fields to custom URLs
#3316
Glenn Harper
Fresh Boarder
Posts: 5
User Offline
[SOLVED]SQL for adding fields to custom URLs Karma: 0  
I have EZ Realty and Jsitemap Pro and I can't seem to get the links to display more than one EZRealty field at a time.

I seem too remember seeing something involving CONCAT_WS or something but I don't find it in the user guide.

Can somebody point me to directions for building URLs that could have fields like price, address, description, etc. strung together to make the link?
 
Logged Logged  
  The administrator has disabled public write access.
#3317
John Dagelmore
Admin
Posts: 3716
User Online Now
Re:SQL for adding fields to custom URLs Karma: 79  
Hello Glenn,

if you have configured a custom data source for EZ Realty and you want to concatenate SQL fields for the title you must use the CONCAT_WS in the raw SQL query.

For example if you have a query like this:

SELECT title FROM ....

you can concatenate fields like this:

SELECT CONCAT_WS(',','title','price','address' ) AS title FROM .....

Notice that this is used for the title not for the link and URL.

John
 
Logged Logged  
  The administrator has disabled public write access.
#3318
Glenn Harper
Fresh Boarder
Posts: 5
User Offline
Re:SQL for adding fields to custom URLs Karma: 0  
I've been trying several times to use your code, but I obviously don't know what I'm doing,

Here is what is regenerated:
SELECT
`#__ezrealty`.`smalldesc` AS `title`,
`#__ezrealty`.`id`,
`#__ezrealty`.`cid`,
`#__ezrealty`.`price`,
`#__ezrealty`.`locality`
FROM `#__ezrealty`
WHERE
`#__ezrealty`.`published` = '1'
AND (`#__ezrealty`.`language` = '*' OR `#__ezrealty`.`language` = {langtag})
ORDER BY
`#__ezrealty`.`adline` ASC

I try variations on:
SELECT CONCAT_WS(',', `#__ezrealty`.`smalldesc`, `#__ezrealty`.`locality`,`#__ezrealty`.`price`) AS title
FROM `#__ezrealty`
WHERE
`#__ezrealty`.`published` = '1'
AND (`#__ezrealty`.`language` = '*' OR `#__ezrealty`.`language` = {langtag})
ORDER BY
`#__ezrealty`.`adline` ASC

but no result.

Where is my mistake?
 
Logged Logged  
  The administrator has disabled public write access.
#3319
John Dagelmore
Admin
Posts: 3716
User Online Now
Re:SQL for adding fields to custom URLs Karma: 79  
Well you should replace only the title line with the concat but leave untouched all the other fields, this is a correct query:

SELECT CONCAT_WS(',', `#__ezrealty`.`smalldesc`, `#__ezrealty`.`locality`,`#__ezrealty`.`price`) AS title,
`#__ezrealty`.`id`,
`#__ezrealty`.`cid`
FROM `#__ezrealty`
WHERE
`#__ezrealty`.`published` = '1'
AND (`#__ezrealty`.`language` = '*' OR `#__ezrealty`.`language` = {langtag})
ORDER BY
title ASC
 
Logged Logged  
  The administrator has disabled public write access.
#3320
Glenn Harper
Fresh Boarder
Posts: 5
User Offline
Re:SQL for adding fields to custom URLs Karma: 0  
I appreciate your timely and thoughtful replies. Sorry to be a dunce, but I copied and pasted the query you posted and I got a similar error to the times I tried my own queries. I set it to debug at http://todaysproperties.com/rockport-real-estate-sitemap but I'm afraid I still don't see the error in the query.
 
Logged Logged  
  The administrator has disabled public write access.
#3321
John Dagelmore
Admin
Posts: 3716
User Online Now
Re:SQL for adding fields to custom URLs Karma: 79  
It seems that the error is a missing comma after the title at first line,
wrong:
SELECT CONCAT_WS(',', `#__ezrealty`.`smalldesc`, `#__ezrealty`.`locality`,`#__ezrealty`.`price`) AS title

correct:
SELECT CONCAT_WS(',', `#__ezrealty`.`smalldesc`, `#__ezrealty`.`locality`,`#__ezrealty`.`price`) AS title,
 
Logged Logged  
  The administrator has disabled public write access.
Go to top