Support

May 10, 2017 at 3:40 am

I have tried this query on phpMyAdmin

SELECT *,
@curRow := @curRow + 1 AS row_number
FROM (
SELECT wp_esp_attendee_meta.ATTM_ID, wp_esp_registration.REG_ID, wp_esp_registration.EVT_ID, wp_esp_registration.REG_date

FROM wp_esp_registration
INNER JOIN wp_esp_attendee_meta
ON wp_esp_attendee_meta.ATT_ID=wp_esp_registration.ATT_ID
WHERE wp_esp_registration.EVT_ID = 8904
ORDER BY wp_esp_registration.REG_date) a
JOIN (SELECT @curRow := 0) r;

How I can implement this via php based on the current user and ticket-row in ESPRESSO_MY_EVENTS or, if that would be too complicated… how can I implement this on a single-event-template in order to output the row_number which in this case would be the position…

Thanks!

May 10, 2017 at 6:58 am

Hi Josh

Sorry for the putting a fence 🙂 It should work now!

The “waitlist events” are normal EE-events but have a default status of “not confirmed”. We then move attendees from one to another.

The reason we need this query to work is we need to show the position of the ticket based on the REG_Date inside ESPRESSO_MY_EVENTS.

We already tried this in the meantime, but this only kind of works on single-event-pages… we cannot figure out a way to implement it inside the shortcode as we don’t quite understand the relations…

<?php

global $wpdb;

$sqlCount = "SELECT count(*) as cnt FROM wp_esp_registration INNER JOIN wp_esp_attendee_meta ON wp_esp_attendee_meta.ATT_ID=wp_esp_registration.ATT_ID WHERE wp_esp_registration.EVT_ID = ". get_the_ID() .";";

$results = $wpdb->get_results( $sqlCount );

if ( $results[0]->cnt )
{
	$user_id = get_current_user_id();
	$sql = "SELECT *, @curRow := @curRow + 1 AS row_number FROM ( SELECT wp_esp_attendee_meta.ATTM_ID, wp_esp_registration.REG_ID, wp_esp_registration.EVT_ID, wp_esp_registration.REG_date FROM wp_esp_registration INNER JOIN wp_esp_attendee_meta ON wp_esp_attendee_meta.ATT_ID=wp_esp_registration.ATT_ID WHERE wp_esp_registration.EVT_ID = ". get_the_ID() ." ORDER BY wp_esp_registration.REG_date) a JOIN (SELECT @curRow := 0) r;";

	$results = $wpdb->get_results( $sql );
	
	$userPositions = [];
	
	foreach ( $results as $result )
	{
		if ( $user_id == $user_id->ATTM_ID )
		{
			$userPositions[] = $result->row_number;
		}
	}
	
	echo "<p>positions for user Id: ". $user_id .'</p>';
	
        //print_r( $userPositions;)
	if ( $userPositions )
	{
		echo '<p>Position for event is '. min( $userPositions ) .'</p>';
	}
}

?>

Thanks Josh!
Cheers
Philip

May 10, 2017 at 2:28 pm

Josh, you are my hero!

Thanks, that’s exactly what we needed!

Have a great day!
Cheers
Philip