Support

December 20, 2019 at 3:15 am

Hi Daniel,

That’s a nice workaround for creating reports that we don’t yet have 🙂

The only comment I have for the above is wp_ can and often has been changed, it’s the $table_prefix value from wp-config.php, the default value is wp_ but many people change it. A while back people thought that doing so improved security on your site (it does not) so started changing the default value meaning your above queries won’t work there.

I’m guessing you already know this and you’re not expecting them to work everywhere out of the box anyway but through it worth noting in the thread for any future readers, it might be a good idea to include it in your readme.

December 23, 2019 at 3:37 am

I’d love to know if you EE guys have any MySQL like this just hanging around that you might want to share? Snippets to do some quick reports.

Personally I don’t have any raw SQL sitting around and I doubt any other member of the team will do as we would use the EE model system for almost any code we write for EE. It’s more portable than the above as it works regardless of the table prefix and takes care of any joins for you although will take a little more work to output the details in tables etc.

For example, to call all events with a specific meta key, you can use:

$events = EEM_Event::instance()->get_all(
	array(
		array(
			'Post_Meta.meta_key' => 'test'
		)
	)
);

Your query specifically calls for events that have that meta key and is not empty, so you can do something like:

$events = EEM_Event::instance()->get_all(
	array(
		array(
			'Post_Meta.meta_key' => 'test',
			'Post_Meta.meta_value' => array('!=', '')
		)
	)
);

Now you’ll have an array of EE_Event objects which you can output whatever details you need from that using PHP. You could do something similar for the Promotions report you made although that’s not as easy as the above example.

I’ve just used this technique last night to help migrate some ACF data on a bunch of events. I wanted to find all the Events that had a certain meta_key because we were migrating from a text ACF field to a date ACF field. I created a SQL statement to show me the listing and provide links directly to the Events of interest. So, not just for reporting! With URL concatenation, it’s a nice Event management utility too.

Sure, if your writing raw SQL you can do pretty much whatever you need to do with it. However, the majority of users we have here are not likely to understand it and will have no idea how to make any changes to it so it’s not something we (EE Support) would create/use regularly.

We don’t get all that many developers on here writing their own statements (although there are some), mainly end users of EE or designers asking.

I could probably make it more portable by creating a SQL variable, and then concatenating the name in a prepared statement but then the SQL would become more cluttered.

Yep, there’s pros and cons to either method and I’m glad this is working for you.

—

Note that I’m not trying to dissuade you from using the above or sharing what you are doing with it, it’s a great way to add additional functionality you may need if you understand it enough to do it