[Plugin: Participants Database] How to display the records on the page

Hi hadlingr,

Thanks, I’m leaning a lot here…

I’ve had a number of requests to provide a way to display the list on the frontend (as opposed to in the admin) and it is certainly my next enhancement. It wont be too hard to do, I just wanted to make sure the existing features were solid first. I would expect an upgrade in the next few days.

Hello Xnau,
Actually just right after I wrote the first message, I decided to create a new shortcode to display the list on the post/page.
May I put the codes here?

First, I registered a new shortcode, called “pdb_list”. I insert this code right after these lines at participants-database.php:
// define our shortcodes
add_shortcode( ‘pdb_record’, array( __CLASS__, ‘frontend_edit’) );
add_shortcode( ‘pdb_signup’, array( __CLASS__, ‘print_signup_form’ ) );

add_shortcode( 'pdb_list', array( __CLASS__, 'print_list' ) );

And then, create new public function on participants-database.php:
/**
*print list member
*/
`public function print_list( $atts ) {
global $wpdb;
// at present, there are no attributes
$vars = shortcode_atts( array(
), $atts );

?>
<style type=”text/css”><?php include ‘participants-db.css’ ?></style>
<?php
include ‘list_member.php’;
}`

Yes, right, I create a new file list_member.php, it based on your existing file, i.e list_participants.php

list_member.php

<?php
// process all the general list actions first

// add this to the query to remove the default record
$skip_default = ' <code>id</code> != '.Participants_Db::$id_base_number;

// set up the query to display the list
switch ( @$_POST['submit'] ) {

  case 'Sort':
  case 'Filter':

		$list_query = 'SELECT * FROM '.Participants_Db::$participants_table;
		$delimiter = ( false !== stripos( $_POST['operator'], 'LIKE' ) ? '%' : '' );
		if ($_POST['where_clause'] != 'none'){
		  $list_query .= ' WHERE <code>'.mysql_real_escape_string($_POST['where_clause']).'</code> '.mysql_real_escape_string($_POST['operator'])." '".$delimiter.mysql_real_escape_string($_POST['value']).$delimiter."' ";
			$list_query .= ' AND '.$skip_default;
		} else {
			$list_query .= ' WHERE '.$skip_default;
		}
		$list_query .= ' ORDER BY '.mysql_real_escape_string($_POST['sortBy']).' '.mysql_real_escape_string($_POST['ascdesc']);

		// go back to the first page
		$_GET['p'] = 1;

		break;

  case 'Clear' :
		unset( $_POST['value'], $_POST['where_clause'] );

		// go back to the first page
		$_GET['p'] = 1;

  default:
		$list_query = 'SELECT * FROM '.Participants_Db::$participants_table.' WHERE '.$skip_default.' ORDER BY last_name';

}

$options = get_option( Participants_Db::$participants_db_options );

// set the pagination object
$pagination = new Pagination( array(
																		'link'          => $_SERVER['REQUEST_URI'].'&p=%s',
																		'page'          => isset( $_GET['p'] ) ? $_GET['p'] : '1',
																		'size'          => $options['list_limit'],
																		'total_records' => Participants_Db::get_num_records(),
																		'wrap_tag'      => '',
																		'wrap_tag_close'=> '',
																		));

// get the number of records returned
$num_records = count( $wpdb->get_results( $list_query, ARRAY_A ) );

// get the records for this page, adding the pagination limit clause
$participants = $wpdb->get_results( $list_query.' '.$pagination->getLimitSql(), ARRAY_A );

?>

<div class="wrap">
  <h2><?php echo Participants_Db::PLUGIN_TITLE?></h2>
  <h3>List Participants: <?php echo $num_records?> records found, sorted by: <?php echo isset( $_POST['sortBy'] ) ? Participants_Db::column_title( $_POST['sortBy'] ) : Participants_Db::column_title( 'last_name' ) ?>.</h3>
  <form method="post">
    <input type="hidden" name="action" value="sort">
    <fieldset class="widefat">
    <legend>Show only records with:</legend>
    <?php
			//build the list of columns available for filtering
			$filter_columns = array( '(show all)' => 'none' );
			foreach ( Participants_db::get_column_atts() as $column ) {

				if ( in_array( $column->name, Participants_Db::$internal_columns ) ) continue;

				$filter_columns[ $column->title ] = $column->name;

			}

			$element = array(
											 'type'		=> 'dropdown',
											 'name'		=> 'where_clause',
											 'value'	=>	isset( $_POST['where_clause'] ) ? $_POST['where_clause'] : 'none',
											 'options'	=> $filter_columns,
											 );
      FormElement::print_element( $element );
			?>
       that
      <?php
       $element = array(
                        'type'     => 'dropdown',
                        'name'     => 'operator',
                        'value'    => isset( $_POST['operator'] ) ? $_POST['operator'] : 'LIKE',
                        'options'  => array(
                                          'is'               => '=',
                                          'is not'           => '!=',
                                          'contains'         => 'LIKE',
                                          "doesn't contain"  => 'NOT LIKE',
                                          ),
                        );
      FormElement::print_element( $element );
      ?>
      <input id="participant_search_term" type="text" name="value" value="<?php echo @$_POST['value'] ?>">
      <input name="submit" type="submit" value="Filter">
      <input name="submit" type="submit" value="Clear">
    </fieldset>
    <fieldset class="widefat">
    <legend>Sort by:</legend>
    	<?php

			$element = array(
											 'type'		=> 'dropdown',
											 'name'		=> 'sortBy',
											 'value'	=>	isset( $_POST['sortBy'] ) ? $_POST['sortBy'] : 'last_name',
											 'options'	=> Participants_Db::get_sortables(),
											 );
      FormElement::print_element( $element );

			$element = array(
											 'type'		=> 'radio',
											 'name'		=> 'ascdesc',
											 'value'	=>	isset( $_POST['ascdesc'] ) ? $_POST['ascdesc'] : 'asc',
											 'options'	=> array( 'Ascending' => 'asc', 'Descending' => 'desc' ),
											 );
      FormElement::print_element( $element );

			?>
      <input name="submit" type="submit" value="Sort">
    </fieldset>
  </form>
  <form id="list_form"  method="post">
    <input type="hidden" name="action" value="list_action">
    <div style="margin-top:10px">

      <span style="padding-left:20px">Show
      <?php FormElement::print_element( array( 'type'=>'text-line', 'name'=>'list_limit', 'value'=>$options['list_limit'], 'size'=>'1' ) )?>
      items per page.
      </span>
    </div>
    <table class="wp-list-table widefat fixed pages" cellspacing="0" >
      <?php
		if ( count( $participants ) > 0 ) :

		  // get the columns to display
		  $display_columns = Participants_Db::get_list_displaying_columns();

			//now output the table of participants
			$col_pattern = '<td style="padding:0 5px;white-space:nowrap;">%s</td>';
			$head_pattern = '<th style="padding:0 5px">%s</th>';

			$PID_pattern = '<td><a href="'.get_bloginfo('url').'/'.( isset( $options['registration_page'] ) ? $options['registration_page'] : '' ).'?pid=%1$s">%1$s</a></td>';

			?>
      <thead>
        <tr>

          <?php
				 foreach ( $display_columns as $column ) {
					printf ( $head_pattern, Participants_Db::column_title( $column ) );
				 } ?>
        </tr>
      </thead>
      <?php
			if ( count( $participants ) > 5 ) :
			?>
      <tfoot>
        <tr>
          <th></th>
          <?php
				 foreach ( $display_columns as $column ) {
					printf ($head_pattern, Participants_Db::column_title( $column ) );
				 } ?>
        </tr>
      </tfoot>
      <?php endif ?>
      <?php
			foreach ( $participants as $value ) {
			?>
      <tr>
        <?php foreach ( $display_columns as $column ) {

					if ( $column == 'private_id' ) printf ($PID_pattern, Participants_Db::prepare_value( $value[ $column ] ) );

					else printf ($col_pattern, Participants_Db::prepare_value( $value[ $column ] ) );

				} ?>
      </tr>
      <?php }

		else :
		?>
      <tr>
        <td>No participants found</td>
      </tr>
      <?php
		endif; // participants array
		?>
    </table>
  </form>
  <div class="pagination"><label>Page:</label>
    <?php
		$pagination->links();
		?>
  </div>

</div>

I didnt test it for a long list, I just hope the pagination would be work. And may be I should clean up unused codes on the list_member.php file.
After the codes have been set, I create a new page and simply call the shortcode [pdb_list].
Voila.. It displayed nicely on the page. Thanks for your useful plugin.
And it would be more perfect if this plugin could display the photo of the member.

Hi hadingr,

Wow, I’m just now seeing this…good getting in there and finding a way forward! I’ve had to do this with a few WP plugins, and it’s been a great way to learn more about WP.

I went totally down the rabbit hole on this and re-wrote the list display as a PHP class…because I needed to be able to better control which parts of the listing would display. Also needed to give users a way to define which columns would be included.

So now I have both the listing shortcode (I used the same one as you [pdb_list]) and also the ability to add an image to the record. Check out the latest upgrade.

–Roland

Hello Roland,

First I would like to thank you for the work you performed with this plugin, I made long research other the pugins, tested dozens of them to finally converge to yours which fits the best my need.

I am using it to distribute sub-lists of the participants database to some identified focal points which are users of my wordpress blog, it is purely internal and with no problem of security as I know all the users personnaly. I use the pdb_list with a filter on a “focal point” column inserted in a private post that only this focal point is seeing. I allows those focal point to see only the part of the database which is interesting them. Very Good.

Now I would like to offer them the possibility to edit the entries directly from the frontend. My first idea was to add a column in the database which contains the famous private link which allow the edition. But this force me to do a manual copy/paste to fill this column individually.
Maybe a better idea would be to develop the short code to have an option allowing to display the private link under an “Edit” hyperlink in front of each row.
Could you help me in changing the shortcode ?

On a more general/long term improvement my need would be to manage individually each field of the database to be able to set for each of them which group of users can read it and which group of users can modify it. It is a kind of fully customize possibility versus your 2 current forms accessible via pdb_signup and pdb_record.
As you can see my goal is to 100% use the frontend to manage the participant database, I don’t want the users to go on the backend as they judge it is too complicated. For this last chapter, I recognize that it is huge work and that if the request comes only from me you should not invest your time on it.

Thanks
Séverin

Hi Sev,

I hadn’t thought to make this possible, but you could modify the main script to print the private id on the frontend.

In participants_db.php on line 565 find:

if ( $set == 'admin_column' && self::$plugin_settings->get_option( 'show_pid' ) ) $column_set[0] = 'private_id';

change this (to disable the backend check) to this:

/*if ( $set == 'admin_column' && self::$plugin_settings->get_option( 'show_pid' ) ) */ $column_set[0] = 'private_id';

Now [pdb_list] shortcode will publish links to the record edit page for each record listed.

For the second part…well perhaps it would be easier to create a special user role for these users and give them access to just the list and record edit pages in the admin. I know people get confused by the unfamiliar interface in the admin, but you can cut it down to the bare minimum. I’m not likely to build something like what you’re asking for into the plugin.

–have fun! Roland

Thanks a lot Roland, your fix works perfectly.
And double thanks for the fast answer !

I will now analyse in depth your code to see if I can manage another shortcode that will suit me need.

Hello Roland,

Very nice update with version 1.3 and now with the ability to select the fields you want to display, it is answering to my need.
I upgraded to this version and applied the patch you gave me to see private_id (now it is line 634 which must be modified.
Only problem is that this fix is not compatible with the field selection that 1.3 brings, even when I select “private_id” field. Could you tell me which line/block should be modified to allow field selection + private ID display ?

Thanks for your work anyway on 1.3, it avoided me to go in your code ??

Sev

OK, here’s the mod you need for getting it to work with the fields variable in the shortcode:

in participants-database.php on line 667:

$sql = "SELECT f.name
		        FROM ".self::$fields_table." f
						WHERE f.group != 'internal'";

Change this to:

$sql = "SELECT f.name
		        FROM ".self::$fields_table." f ";

That’ll do it.

Hello, the mod permits to see the “Record ID” field which is not clickable. it does not show the “Private ID” which is clickable thanks to the previous mod and allows to go directly to the edit page.

And in fact what I was hoping was that the edit page will contain only the fields selected in the pdb_list but I guess you did not changed the code of the edit page ?!

Please forget my first post, I put id instead of private_id in the fields selection. After this correction it works well.

So now it remains only this problem : when clicking on the private_id, it goes to the edit page which does not have the fields selection ability.

If I want to display the all the records on my site, what sort of short code would I use? We’re trying to create a swimmer database for potential colleges to look at –

@fsn,

First, I just want to mention that if you have a new question, you should start a new topic with it. It’s much more likely to be seen that way, and it helps others who might have similar questions.

Showing the list of records on your website is done with the [pdb_list] shortcode.

I suggest you read the docs, they will explain all the options for this shortcode, and a lot of the other details you’ll need to know to get the plugin working the way you want. Let me know if you still have questions…

https://www.ads-software.com/extend/plugins/participants-database/other_notes/