Glenn Harper
Fresh Boarder
|
[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
|
|
The administrator has disabled public write access.
|
John Dagelmore
Admin
|
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
|
|
The administrator has disabled public write access.
|
Glenn Harper
Fresh Boarder
|
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
|
|
The administrator has disabled public write access.
|
John Dagelmore
Admin
|
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
|
|
The administrator has disabled public write access.
|
Glenn Harper
Fresh Boarder
|
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
|
|
The administrator has disabled public write access.
|
John Dagelmore
Admin
|
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
|
|
The administrator has disabled public write access.
|
|