Click here to Skip to main content
13,794,944 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi,

I try to make a SQL request to retrieve events and images from those events. All is ok exept the pictures format, I don't know why but my request escape all quotes and stuff.

My SQL request
SELECT e.`idevents`, e.`place`, e.`description`, e.`is_online`, e.`idcategories`, e.`idusers`, e.`title`,e. `latitude`, e.`longitude`, ST_Distance_Sphere(
    POINT(latitude, longitude),
    POINT(?, ?)
) AS distance,
json_array((
    SELECT GROUP_CONCAT(
        json_object('idpictures',p.idpictures, 'url', p.url)
    )   
    FROM pictures p
    LEFT JOIN  events_pictures ep
        ON ep.idpictures = p.idpictures
    WHERE ep.idevents = e.idevents
)) as pictures            
FROM events e";


The result
[{
	"idevents": 1,
	"place": "10 route des Sablons, 44830 Bouaye",
	"description": "Apparition de la dame du lac",
	"is_online": 1,
	"idcategories": 1,
	"idusers": 1,
	"title": "Apparition dame du lac",
	"latitude": "47.134063",
	"longitude": "-1.664312",
	"distance": 95309.26587540086,
	"pictures": "[\"{\\\"url\\\": \\\"https:\/\/picsum.photos\/300\/500\\\", \\\"idpictures\\\": 1},{\\\"url\\\": \\\"https:\/\/picsum.photos\/500\/300\\\", \\\"idpictures\\\": 2}\"]"
}, {
	"idevents": 2,
	"place": "16 Route du Bois Cholet, 44860 Saint-Aignan-Grandlieu, France",
	"description": "Apparition trop cool",
	"is_online": 1,
	"idcategories": 2,
	"idusers": 1,
	"title": "Mon \u00e9v\u00e8nement super cool",
	"latitude": "47.146919",
	"longitude": "-1.640709",
	"distance": 97945.99220240247,
	"pictures": "[null]"
}]

Here you can see a lots of "\" added.

What I want
[{
		"idevents": 1,
		"place": "10 route des Sablons, 44830 Bouaye",
		"description": "Apparition de la dame du lac",
		"is_online": 1,
		"idcategories": 1,
		"idusers": 1,
		"title": "Apparition dame du lac",
		"latitude": "47.134063",
		"longitude": "-1.664312",
		"distance": 95309.26587540086,
		"pictures": [{
				"url": "https://picsum.photos/300/500",
				"idpictures": 1
			},
			{
				"url": "https://picsum.photos/500/300",
				"idpictures": 2
			}
		]
	},
	{
		"idevents": 2,
		"place": "16 Route du Bois Cholet, 44860 Saint-Aignan-Grandlieu, France",
		"description": "Apparition trop cool",
		"is_online": 1,
		"idcategories": 2,
		"idusers": 1,
		"title": "Mon \u00e9v\u00e8nement super cool",
		"latitude": "47.146919",
		"longitude": "-1.640709",
		"distance": 97945.99220240247,
		"pictures": [null]
	}
]

Thanks
A.D

What I have tried:

I looked on internetto see if functions
JSON_OBJECT()
or
GROUP_CONCAT()
escaped characters but it seems not...
Posted 13-Oct-18 13:05pm
Comments
Mehdi Gholam 14-Oct-18 5:09am
   
Your "pictures" property is probably being stored as a string.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.181207.3 | Last Updated 13 Oct 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100