Support

Home Forums Event Espresso Premium EventEspresso MySQL Sharing Repository

EventEspresso MySQL Sharing Repository

Posted: December 18, 2019 at 9:59 am


Daniel

December 18, 2019 at 9:59 am

Hi!

I’ve created a bunch of custom MySQL queries for our eventespresso installation and decided to share.

I launched it with one report that provides detail on the use of promotion codes. Like it says in the readme.md you’ll get the most out of these if you install a WordPress SQL Plugin like Exports and Reports.

https://github.com/dcdigit/eventespresso_mysql_reports

Feedback and PRs welcome.


Tony

  • Support Staff

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.


Daniel

December 20, 2019 at 6:57 am

Hi Tony!

Thanks for the feedback – I did know about the WP prefix, but that is a good suggestion to put something in the readme.

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.

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.

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.

https://stackoverflow.com/questions/4428761/mysql-field-name-from-variable

Glad you like it! I hope other Devs contribute!!


Tony

  • Support Staff

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


Daniel

December 31, 2019 at 9:55 am

I’ve posted an update to this repo. I needed a way to see which Message Template was assigned to which Event. I didn’t see any listing in native EE so I created this SQL to create a listing that provides links to both the Message Template and the Event.

Now it’s easy to find which Message Template goes with which Event.

Feedback welcome.

https://github.com/dcdigit/eventespresso_mysql_reports

The support post ‘EventEspresso MySQL Sharing Repository’ is closed to new replies.

Have a question about this support post? Create a new support post in our support forums and include a link to this existing support post so we can help you.

Support forum for Event Espresso 3 and Event Espresso 4.
Documentation for EE3 and EE4
Documentation for Event Espresso 3 Documentation for Event Espresso 4

Status: closed

Updated by  Daniel 7 months, 1 week ago ago

Topic Tags

Notifications

This topic is: not a support question
Do NOT follow this link or you will be banned from the site!