Results 1 to 1 of 1
September 6th, 2007, 07:12 PM #1GeoNewcomer License Holder
- Join Date
- Mar 2006
- Sydney, Australia
MYSQL query assistance to extract data and images for Google
I have been attempting to put my meagre MySQL query knowledge to use to create a query to extract descrition data and multiple associated image url paths for a Googlebase text bulk upload, from GeoClassAuctions 3.0.2.
The items are cars, the multiple image file names for each car following the covention; ABC12345_1.jpg, ABC12345_2.jpg, ABC12345_3.jpg etc (up to about 6 images, but not always. Some have from zero to 6 images)
So far I have succeeded in joining the geodesic_classifieds, geodesic_classifieds_images_urls, and geodesic_userdata tables and renaming to to suit the required standard and custom attribute names to suit Googlebase, for the url link, vehicle description, location etc.
My problem is that when I run the query, it works as expectd, BUT I get multiple rows or each item. One row for each instance of an image url.
Googlebase requires the use of a single field (attribute) called 'image_link' to contain a series of full image url paths, seperated by commas.
I have tried concatenating the full path to the image to achieve this.
As per the query shown here, I thought I could use the TRIM function 'within' the CONCAT funtion to strip off _1.jpg; _2jpg; etc, then add it back on to arrive at:
'http:carsglobal.com/sales/images/user_images/ABC12345_1.jpg, http:carsglobal.com/sales/images/user_images/ABC12345_2.jpg, http:carsglobal.com/sales/images/user_images/ABC12345_3.jpg'
You'll see what I mean, but the syntaxt is clearly wrong.
I have searched MySQL resources and can't find a solution to what must be a fairly common requirement.
Are there any MySQL gurus that can provide a simple solution?
The Googlebase custom attribute naming convention is c:nameofcustomattribute:datatype ie. 'c:enginesize:integer'
SELECT CONCAT('http://www.carsglobal.com/sales/index.php?a=2&b=',C.id) as 'link', CONCAT('http://www.carsglobal.com/sales/images/user_images/',TRIM(TRAILING '_1.jpg' FROM I.full_filename),'_1.jpg', ", ",'http://www.carsglobal.com/sales/images/user_images/',TRIM(TRAILING '_2.jpg' FROM I.full_filename),'_2.jpg', ", ",'http://www.carsglobal.com/sales/images/user_images/',TRIM(TRAILING '_3.jpg' FROM I.full_filename),'_3.jpg') as 'image_link', C.title as 'title', C.price as 'price', C.optional_field_1 as 'make', C.optional_field_2 as 'model', C.optional_field_3 as 'c:badge:string', C.optional_field_4 as 'c:series:string', C.optional_field_5 as 'c:body_type', C.optional_field_6 as 'c:transmission:string', C.optional_field_7 as'c:drive_type:string', C.optional_field_8 as 'year', C.optional_field_9 as 'c:engine_displacement:decimal', C.optional_field_10 as'c:cylinders:string', C.optional_field_11 as 'c:induction:string', ('USED') as 'condition', ('CAR') as 'vehicle_type', C.optional_field_13 as 'mileage', C.optional_field_14 as 'color', C.optional_field_16 as 'vin', C.optional_field_20 as 'c:features:string', C.id as 'id', C.description as 'description', U.company_name as 'c:dealer:string', CONCAT(U.address,", ",U.city," ",U.state,", ",U.zip," ",U.country) as'location'
FROM geodesic_classifieds as C
JOIN geodesic_classifieds_images_urls as I on I.classified_id = C.id
JOIN geodesic_userdata as U on C.seller = U.id
WHERE C.live = 1
Any feedback would be appreciated.
By carsglobal in forum Commercial DevelopmentReplies: 2Last Post: June 14th, 2006, 09:59 AM