Tag Archives for mysql

SPARK Mobile App Contest

Q1 2016 – Q4 2018

Responsibilities: HTML CSS/SASS JS/jQuery PHP

Traditional wordpress site, I built a custom theme based off llorix. SPARK for Autism needed an accompanying website for the duration of the contest to handle contest registration, announcements, and community app voting. I worked on this during my time as an internal dev at the Simons Foundation.

Notable tasks: I opted for a kanban approach to development given that feature development needed to occur around events and announcements that comprised the contest. For registration I connected a CF7 form template to a Googlesheet, such that as users registered, their data would be entered into the spreadsheet, which was shared with stakeholders so they would always be up-to-date without me spending time on plebian tasks like pulling reports. Students were asked to register themselves and their teammates by entering their emails and setting a team password. Teammates would receive an invite email, as all participants had to register in order to be eligible for prize money. To set up voting I started with a general star-voting plugin, added some authentication which hit the SPARK api to determine voting status and access privileges, and modified the JS and PHP according to aesthetic/functional specifications.

05. February 2019 by admin
Tags: , , , , , , , | Comments Off on SPARK Mobile App Contest

Simons Foundation

Q1 2016 – Q4 2018

Responsibilities: HTML CSS/SASS JS/jQuery PHP/MySQL AWS

I inherited the task of maintenance, bugfixes, and new feature implementation during my time as an internal dev at the Simons Foundation. The bugs and tech debt that the initial vendor delivered with the project was a painful reminder of why waterfall development should be avoided at all costs.

Notable tasks: Getting a team new to Agile to adopt a SCRUM process running on 2-week sprints. Diving deeper in MySQL procedures to handle migrations of the alien information schema handed off to us. Migrating an Unfuddle ticket project to our Jira account in a jungle of chunked spreadsheets, field-remapping, and consolidation scripts to preserve ticket comment history. Migrating ACF field schema out of the PHP into which it had been distilled to prevent alteration by non-developer WP admins, back into the DB. Running a strategy-mapping workshop with the team to identify strategic solutions to obstacles (I am forever grateful to my friend/colleague Rob, who taught me this method when we worked together at IBM). Writing a plugin that works with the arxiv.org api to provide a tool for helping content editors to sync arxiv’s publications authored by foundation scientists with our wordpress publications archive. Restructuring hundreds of daisy-chained SASS files into a more sane and manageable schema.

05. February 2019 by admin
Tags: , , , , , , , , | Comments Off on Simons Foundation

THINK Exchange – IBM

think-exchange.com is IBM’s members-only community, events program and content portal for C-level executives. It began with THINK Marketing for CMO’s and has expanded to include Finance and Technology.

Responsive site using PHP/MySQL, HTML/CSS/JS, WordPress, Buddypress, Media element.

23. June 2014 by admin
Tags: , , , , , , , , , , , , , , | Leave a comment

alphabetize “Friends” list by default in BuddyPress

2011
Wordpress/Buddypress 1.6.1 filter

I found a fair amount about how to alphabetize the members list by default in WordPress/Buddypress, but sorting wasn’t working on the ‘Friends’ Tab — it kept displaying the entire Members List. The Fix:

Add this to functions.php or bp-custom.php. Or you can drop it into a plugin, it’s not hard but right now I’m too busy.

add_filter( ‘bp_dtheme_ajax_querystring’, ‘sort_alpha_by_default’ );
function sort_alpha_by_default( $qs ) {
global $bp;

$args=wp_parse_args($qs);
$args[type]=’alphabetical’;
$qs=build_query($args);
return $qs;
}

27. March 2013 by admin
Tags: , , , , | Leave a comment

Crossborders – custom cms with flash front-end

2009
crossborders.tv (now rain)
website/custom cms

PHP/MySQL backend w/custom DB, XML output for interpretation by flash front-end, Brightcove for video service, though the client insisted on implementing Brightcove in a way tha bypassed the API’s and was never intended (oh if I had a nickel…).

23. June 2014 by admin
Tags: , , , , , , , , , | Leave a comment

kim dane painting portfolio

2012
www.kimdane.com
Wordpress framework, custom theme. HTML/CS/JS/PHP/MySQL

To get the main menu to break into user-determined rows, added a DB field for ‘line_break_after’ to the wp posts table, and altered the ‘My Page Order’ plugin to allow user to insert linebreaks where desired. Also a few Walker methods to the WP framework to grab this field and put in the appropriate linebreaks.

Custom JS Effect for the NextGen gallery to populate the scrollable thumbnail menu and incorporate deeplinking using swfAddress.

Social was also interesting. Buttons for FB, Twitter & Google+ pulled in w/JS. Discovered that JS content and FB Like do NOT play nice, mostly due to FB Like API ignoring JS and scraping the page for metatags when posting. So instead of telling FB to just share the page url, had to stitch together a url from the swfaddress deeplink such that it got passed in as a GET variable and the header.php could react appriopriately to return the appropriate metatags. It’s a shame that one should have to choose between smoothly swapping content around clientside and the keeping sharing simple & effective.

23. June 2014 by admin
Tags: , , , , , , , | Leave a comment

view list of unapproved WordPress/Buddypress users

Wordpress Version 3.4.2 BuddyPress Version 1.6.1 List view of unapproved users wordpress buddypress 1. FILE: wp-admin/includes/class-wp-users-list-table.php | FUNCTION: prepare_items();

line 36 – add global var $status

global $role, $usersearch, $status;

line 41 – check to see if $status param was passed in the admin request

$status = isset( $_REQUEST[‘status’] ) ? $_REQUEST[‘status’] : ”;

line 52 – add status var to args array

‘status’ => $status

…the updated function prepare_items():

function prepare_items() {

global $role, $usersearch, $status;


$usersearch = isset( $_REQUEST[‘s’] ) ? $_REQUEST[‘s’] : ;


$role = isset( $_REQUEST[‘role’] ) ? $_REQUEST[‘role’] : ;

$status = isset( $_REQUEST[‘status’] ) ? $_REQUEST[‘status’] : ;

$per_page = ( $this->is_site_users ) ? ‘site_users_network_per_page’ : ‘users_per_page’;

$users_per_page = $this->get_items_per_page( $per_page );


$paged = $this->get_pagenum();


$args = array(

‘number’ => $users_per_page,

‘offset’ => ( $paged-1 ) * $users_per_page,

‘role’ => $role,

‘search’ => $usersearch,

‘fields’ => ‘all_with_meta’,

‘status’ => $status

);


if ( !== $args[‘search’] )

$args[‘search’] = ‘*’ . $args[‘search’] . ‘*’;


if ( $this->is_site_users )

$args[‘blog_id’] = $this->site_id;


if ( isset( $_REQUEST[‘orderby’] ) )

$args[‘orderby’] = $_REQUEST[‘orderby’];


if ( isset( $_REQUEST[‘order’] ) )

$args[‘order’] = $_REQUEST[‘order’];


// Query the user IDs for this page

$wp_user_search = new WP_User_Query( $args );


$this->items = $wp_user_search->get_results();


$this->set_pagination_args( array(

‘total_items’ => $wp_user_search->get_total(),

‘per_page’ => $users_per_page,

) );

}



2. FILE: wp-admin/includes/class-wp-users-list-table.php | FUNCTION: get_views();

line 118 – add link to Users submenu

$role_links = add_unapproved_users_link($role_links);

…the updated function get_views():



function get_views() {

global $wp_roles, $role;


if ( $this->is_site_users ) {

$url = ‘site-users.php?id=’ . $this->site_id;

switch_to_blog( $this->site_id );

$users_of_blog = count_users();

restore_current_blog();

} else {

$url = ‘users.php’;

$users_of_blog = count_users();

}

$total_users = $users_of_blog[‘total_users’];

$avail_roles =& $users_of_blog[‘avail_roles’];

unset($users_of_blog);


$current_role = false;

$class = empty($role) ? ‘ class=”current”‘ : ;

$role_links = array();

$role_links[‘all’] = “<a href=’$url$class>” . sprintf( _nx( ‘All <span class=”count”>(%s)</span>’, ‘All <span class=”count”>(%s)</span>’, $total_users, ‘users’ ), number_format_i18n( $total_users ) ) . ‘</a>’;

foreach ( $wp_roles->get_names() as $this_role => $name ) {

if ( !isset($avail_roles[$this_role]) )

continue;


$class = ;


if ( $this_role == $role ) {

$current_role = $role;

$class = ‘ class=”current”‘;

}


$name = translate_user_role( $name );

/* translators: User role name with count */

$name = sprintf( __(‘%1$s <span class=”count”>(%2$s)</span>’), $name, number_format_i18n( $avail_roles[$this_role] ) );

$role_links[$this_role] = “<a href='” . esc_url( add_query_arg( ‘role’, $this_role, $url ) ) . “‘$class>$name</a>”;

}

$role_links = add_unapproved_users_link($role_links);

return $role_links;

}



3. FILE: wp-includes/user.php | FUNCTION: prepare_query();

line 520 – retrieve ‘status’ var, add to query

$status = trim( $qv[‘status’] ); if (!empty($status)){ $cap_meta_query = array(); $cap_meta_query[‘key’] = ‘wp-approve-user’; if ($status == ‘unapproved’){ $value = ”; }else{ $value = ”; } $cap_meta_query[‘value’] = $value; $qv[‘meta_query’][] = $cap_meta_query; }


…the updated function prepare_query();



function prepare_query() {

global $wpdb;


$qv = &$this->query_vars;


if ( is_array( $qv[‘fields’] ) ) {

$qv[‘fields’] = array_unique( $qv[‘fields’] );


$this->query_fields = array();

foreach ( $qv[‘fields’] as $field )

$this->query_fields[] = $wpdb->users . ‘.’ . esc_sql( $field );

$this->query_fields = implode( ‘,’, $this->query_fields );

} elseif ( ‘all’ == $qv[‘fields’] ) {

$this->query_fields = $wpdb->users.*”;

} else {

$this->query_fields = $wpdb->users.ID”;

}


if ( $this->query_vars[‘count_total’] )

$this->query_fields = ‘SQL_CALC_FOUND_ROWS ‘ . $this->query_fields;


$this->query_from = “FROM $wpdb->users”;

$this->query_where = “WHERE 1=1”;


// sorting

if ( in_array( $qv[‘orderby’], array(‘nicename’, ’email’, ‘url’, ‘registered’) ) ) {

$orderby = ‘user_’ . $qv[‘orderby’];

} elseif ( in_array( $qv[‘orderby’], array(‘user_nicename’, ‘user_email’, ‘user_url’, ‘user_registered’) ) ) {

$orderby = $qv[‘orderby’];

} elseif ( ‘name’ == $qv[‘orderby’] || ‘display_name’ == $qv[‘orderby’] ) {

$orderby = ‘display_name’;

} elseif ( ‘post_count’ == $qv[‘orderby’] ) {

// todo: avoid the JOIN

$where = get_posts_by_author_sql(‘post’);

$this->query_from .= ” LEFT OUTER JOIN (

SELECT post_author, COUNT(*) as post_count

FROM $wpdb->posts

$where

GROUP BY post_author

) p ON ({$wpdb->users}.ID = p.post_author)

;

$orderby = ‘post_count’;

} elseif ( ‘ID’ == $qv[‘orderby’] || ‘id’ == $qv[‘orderby’] ) {

$orderby = ‘ID’;

} else {

$orderby = ‘user_login’;

}


$qv[‘order’] = strtoupper( $qv[‘order’] );

if ( ‘ASC’ == $qv[‘order’] )

$order = ‘ASC’;

else

$order = ‘DESC’;

$this->query_orderby = “ORDER BY $orderby $order;


// limit

if ( $qv[‘number’] ) {

if ( $qv[‘offset’] )

$this->query_limit = $wpdb->prepare(“LIMIT %d, %d”, $qv[‘offset’], $qv[‘number’]);

else

$this->query_limit = $wpdb->prepare(“LIMIT %d”, $qv[‘number’]);

}


$search = trim( $qv[‘search’] );

if ( $search ) {

$leading_wild = ( ltrim($search, ‘*’) != $search );

$trailing_wild = ( rtrim($search, ‘*’) != $search );

if ( $leading_wild && $trailing_wild )

$wild = ‘both’;

elseif ( $leading_wild )

$wild = ‘leading’;

elseif ( $trailing_wild )

$wild = ‘trailing’;

else

$wild = false;

if ( $wild )

$search = trim($search, ‘*’);


$search_columns = array();

if ( $qv[‘search_columns’] )

$search_columns = array_intersect( $qv[‘search_columns’], array( ‘ID’, ‘user_login’, ‘user_email’, ‘user_url’, ‘user_nicename’ ) );

if ( ! $search_columns ) {

if ( false !== strpos( $search, ‘@’) )

$search_columns = array(‘user_email’);

elseif ( is_numeric($search) )

$search_columns = array(‘user_login’, ‘ID’);

elseif ( preg_match(‘|^https?://|’, $search) && ! wp_is_large_network( ‘users’ ) )

$search_columns = array(‘user_url’);

else

$search_columns = array(‘user_login’, ‘user_nicename’);

}


$this->query_where .= $this->get_search_sql( $search, $search_columns, $wild );

}


$blog_id = absint( $qv[‘blog_id’] );


if ( ‘authors’ == $qv[‘who’] && $blog_id ) {

$qv[‘meta_key’] = $wpdb->get_blog_prefix( $blog_id ) . ‘user_level’;

$qv[‘meta_value’] = 0;

$qv[‘meta_compare’] = ‘!=’;

$qv[‘blog_id’] = $blog_id = 0; // Prevent extra meta query

}


$role = trim( $qv[‘role’] );


if ( $blog_id && ( $role || is_multisite() ) ) {

$cap_meta_query = array();

$cap_meta_query[‘key’] = $wpdb->get_blog_prefix( $blog_id ) . ‘capabilities’;


if ( $role ) {

$cap_meta_query[‘value’] = ‘”‘ . $role . ‘”‘;

$cap_meta_query[‘compare’] = ‘like’;

}


$qv[‘meta_query’][] = $cap_meta_query;

}

$status = trim( $qv[‘status’] );

if (!empty($status)){

$cap_meta_query = array();

$cap_meta_query[‘key’] = ‘wp-approve-user’;

if ($status == ‘unapproved’){

$value = ;

}else{

$value = ;

}

$cap_meta_query[‘value’] = $value;


$qv[‘meta_query’][] = $cap_meta_query;

}


$meta_query = new WP_Meta_Query();

$meta_query->parse_query_vars( $qv );


if ( !empty( $meta_query->queries ) ) {

$clauses = $meta_query->get_sql( ‘user’, $wpdb->users, ‘ID’, $this );

$this->query_from .= $clauses[‘join’];

$this->query_where .= $clauses[‘where’];


if ( ‘OR’ == $meta_query->relation )

$this->query_fields = ‘DISTINCT ‘ . $this->query_fields;

}


if ( !empty( $qv[‘include’] ) ) {

$ids = implode( ‘,’, wp_parse_id_list( $qv[‘include’] ) );

$this->query_where .= ” AND $wpdb->users.ID IN ($ids)”;

} elseif ( !empty($qv[‘exclude’]) ) {

$ids = implode( ‘,’, wp_parse_id_list( $qv[‘exclude’] ) );

$this->query_where .= ” AND $wpdb->users.ID NOT IN ($ids)”;

}


do_action_ref_array( ‘pre_user_query’, array( &$this ) );

}



4. FILE: wp-content/plugins/buddypress/bp-themes/your-theme/functions.php OR
wp-content/themes/your-theme/functions.php
ADD NEW FUNCTION: add_unapproved_users_link()




function add_unapproved_users_link($role_links) {

global $wpdb, $role, $user_filter


// get the number of users that aren’t yet approved

$sql = $wpdb->prepare(“SELECT *

FROM wp_users wpu

JOIN wp_usermeta wpum

ON wpu.ID = wpum.user_id

AND wpum.meta_key=’wp-approve-user’

WHERE wpum.meta_value!=1;”

);


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

$name = ‘unapproved’;

$class = ;


if ( $user_filter == $name ) {

$role = $user_filter;

$class = ‘ class=”current”‘;

}


$role_links[$user_filter] = “<a href='” . esc_url( add_query_arg( ‘status’, $name, ‘users.php’ ) );


$name = sprintf( __(‘%1$s <span class=”count”>(%2$s)</span>’), $name, number_format_i18n( count($results) ) );

$role_links[$user_filter] .= “‘$class>” . $name . “</a>”;


return $role_links;

}

27. March 2013 by admin
Tags: , , , | Leave a comment