WordPress.org

1. CoastAMS
   Member
   Posted 1 year ago #
   

   Hi,

   I have no experience of SQL - but I'm sure this plugin will do what I need. I just don't think I have the required brain power to get it to work :)

   We're using WordPress to list vehicles for sale. I would like to be able to generate a report of the vehicles listed.

   So far I have got the individual queries working (see code below)... but I need to join them together into one query/report

   My queries:
   

   SELECT
   post_title
   FROM wp_posts
   WHERE post_type="listing"

   SELECT
   meta_value
   FROM wp_postmeta WHERE meta_key = 'regno_value'

   SELECT
   meta_value
   FROM wp_postmeta WHERE meta_key = 'price_value'

   SELECT
   meta_value
   FROM wp_postmeta WHERE meta_key = 'color_value'

   SELECT
   meta_value
   FROM wp_postmeta WHERE meta_key = 'year_value'

   If anyone can help - I would be really grateful.
   Thanks

   http://wordpress.org/extend/plugins/exports-and-reports/

2. Scott Kingsley Clark
   Member
   Plugin Author
   Posted 1 year ago #
   

   You could try this:

   SELECT
   post_title, meta_key, meta_value
   FROM wp_posts
   LEFT JOIN wp_postmeta ON post_id = wp_posts.ID AND meta_key IN ('regno_value', 'price_value', 'color_value', 'year_value')
   WHERE wp_posts.post_type = 'listing'

3. CoastAMS
   Member
   Posted 1 year ago #
   

   Thank you for getting back to me so quickly. That's ALMOST it...

   The only issue is it displays each meta key on a separate row - so each van is displayed 4 times (screenshot: http://twitpic.com/6rbdg8/full )

   is there anyway to get it so that it displays the data in the same row?
   eg:
   post_title ¦ reg_no_value ¦ price_value ¦ color_value ¦ year_value

   thanks

4. Scott Kingsley Clark
   Member
   Plugin Author
   Posted 1 year ago #
   

   SELECT
   p.post_title,
   m1.meta_value AS regno_value,
   m2.meta_value AS price_value,
   m3.meta_value AS color_value,
   m4.meta_value AS year_value,
   FROM wp_posts AS p
   LEFT JOIN wp_postmeta AS m1 ON m1.post_id = p.ID AND m1.meta_key = 'regno_value'
   LEFT JOIN wp_postmeta AS m2 ON m2.post_id = p.ID AND m2.meta_key = 'price_value'
   LEFT JOIN wp_postmeta AS m3 ON m3.post_id = p.ID AND m3.meta_key = 'color_value'
   LEFT JOIN wp_postmeta AS m4 ON m4.post_id = p.ID AND m4.meta_key = 'year_value'
   WHERE p.post_type = 'listing'

5. CoastAMS
   Member
   Posted 1 year ago #
   

   Thank you again - unfortunately that doesn't appear to work just returns a 'No items found' message

6. CoastAMS
   Member
   Posted 1 year ago #
   

   I've managed to get the query working.

   However although this works in the mySQL dashboard it doesn't work in the plugin.

   Is there anything else I need to/can do to make this work in the plugin?

   SET SESSION SQL_BIG_SELECTS=1;
   SELECT
   post_title,
   regno.meta_value as regno,
   price.meta_value as price,
   color.meta_value as color,
   year.meta_value as year,
   mileage.meta_value as mileage,
   enginesize.meta_value as enginesize,
   doors.meta_value as doors,
   trans.meta_value as trans,
   fueltype.meta_value as fueltype,
   owners.meta_value as owners,
   body_type.meta_value as body_type
   FROM wp_posts
   LEFT JOIN wp_postmeta AS regno ON regno.post_id=ID AND regno.meta_key='regno_value'
   LEFT JOIN wp_postmeta AS price ON price.post_id=ID AND price.meta_key='price_value'
   LEFT JOIN wp_postmeta AS color ON color.post_id=ID AND color.meta_key='color_value'
   LEFT JOIN wp_postmeta AS year ON year.post_id=ID AND year.meta_key='year_value'
   LEFT JOIN wp_postmeta AS mileage ON mileage.post_id=ID AND mileage.meta_key='mileage_value'
   LEFT JOIN wp_postmeta AS enginesize ON enginesize.post_id=ID AND enginesize.meta_key='enginesize_value'
   LEFT JOIN wp_postmeta AS doors ON doors.post_id=ID AND doors.meta_key='doors_value'
   LEFT JOIN wp_postmeta AS trans ON trans.post_id=ID AND trans.meta_key='trans_value'
   LEFT JOIN wp_postmeta AS fueltype ON fueltype.post_id=ID AND fueltype.meta_key='fueltype_value'
   LEFT JOIN wp_postmeta AS owners ON owners.post_id=ID AND owners.meta_key='owners_value'
   LEFT JOIN wp_postmeta AS body_type ON body_type.post_id=ID AND body_type.meta_key='body_type_value'
   WHERE wp_posts.post_type = 'listing'
   AND post_status = 'publish'
   ORDER BY
   ID

7. Scott Kingsley Clark
   Member
   Plugin Author
   Posted 1 year ago #
   

   You could configure your MySQL.ini settings to allow big selects, otherwise I'd have to make some changes to this plugin to allow for that. The way it runs it's queries is specific and it does rewriting on the query to handle pagination / filtering too, so there's a lot in the mix.

Topic Closed

This topic has been closed to new replies.