I'm tearing my hair out trying to get this to work!

I am writing a plugin to display cricket fixtures, results and match details on a website, taken from an API JSON output off another site. I have writted in 2 activation hooks, the first to create a table, the second to insert the JSON data. The table is created when I activate the plugin but the data is not inserted. I've tried dozens of different formats but cannot get it to work...

Here's a snippet of the JSON RAW output:

{"matches":[{"id":3328019,"status":"New","published":"Yes","last_updated":"23/04/2018","league_name":"North East Premier League","league_id":"409","competition_name":" Premier Divison","competition_id":"77512","competition_type":"League","match_type":"Limited Overs","game_type":"Standard","season":"2018","match_date":"21/04/2018","match_time":"11:45","ground_name":"Sacriston C.C.C.","ground_id":"21090","ground_latitude":"54.8204016","ground_longitude":"-1.6293433","home_club_name":"Sacriston CC","home_team_name":"1st XI","home_team_id":"27997","home_club_id":"5474","away_club_name":"Newcastle CC","away_team_name":"1st XI","away_team_id":"19451","away_club_id":"4563","umpire_1_name":"","umpire_1_id":"","umpire_2_name":"","umpire_2_id":"","umpire_3_name":"","umpire_3_id":"","referee_name":"","referee_id":"","scorer_1_name":"","scorer_1_id":"","scorer_2_name":"","scorer_2_id":""},{"id":3335761,"status":"New","published":"Yes","last_updated":"24/04/2018","league_name":"North East Premier League","league_id":"409","competition_name":" Division 2","competition_id":"77550","competition_type":"League","match_type":"Limited Overs","game_type":"Standard","season":"2018","match_date":"21/04/2018","match_time":"12:15","ground_name":"Cochrane Park ","ground_id":"52063","ground_latitude":"","ground_longitude":"","home_club_name":"Newcastle CC","home_team_name":"2nd XI","home_team_id":"27843","home_club_id":"4563","away_club_name":"Sacriston CC","away_team_name":"2nd XI","away_team_id":"71063","away_club_id":"5474","umpire_1_name":"","umpire_1_id":"","umpire_2_name":"","umpire_2_id":"","umpire_3_name":"","umpire_3_id":"","referee_name":"","referee_id":"","scorer_1_name":"","scorer_1_id":"","scorer_2_name":"","scorer_2_id":""}

I'm trying to write the function as such (CODE EDITED BASED ON SALLY'S COMMENT):


Plugin Name: ncccricket
Version: 1.0

global $ncccricket_db_version;
$ncccricket_db_version = '1.0';

function ncccricket_install() {
global $wpdb;
global $ncccricket_db_version;

$table_name = $wpdb->prefix . 'cricket';

$charset_collate = $wpdb->get_charset_collate();

$sql = "CREATE TABLE $table_name (
    id mediumint(9) NOT NULL,
    status TEXT NOT NULL,
    published TEXT NOT NULL,
    last_updated DATE NOT NULL,
    league_name TINYTEXT NOT NULL,
    league_id INT(6) NOT NULL,
    competition_name TINYTEXT NOT NULL,
    competition_id INT(5) NOT NULL,
    competition_type TINYTEXT NOT NULL,
    match_type TINYTEXT NOT NULL,
    game_type TINYTEXT NOT NULL,
    season INT(4) NOT NULL,
    match_date DATE NOT NULL,
    match_time TIME NOT NULL,
    ground_name TEXT NOT NULL,
    ground_id INT(5) NOT NULL,
    home_club_name TEXT NOT NULL,
    home_team_name TEXT NOT NULL,
    home_team_id INT(8) NOT NULL,
    home_club_id INT(6) NOT NULL,
    away_club_name TEXT NOT NULL,
    away_team_name TEXT NOT NULL,
    away_team_id INT(8) NOT NULL,
    away_club_id INT(6) NOT NULL,
    umpire_1_name TEXT NOT NULL,
    umpire_1_id INT(10) NOT NULL,
    umpire_2_name TEXT NOT NULL,
    umpire_2_id INT(10) NOT NULL,
    umpire_3_name TEXT NOT NULL,
    umpire_3_id TEXT NOT NULL,
    referee_name TEXT NOT NULL,
    referee_id TEXT NOT NULL,
    scorer_1_name TEXT NOT NULL,
    scorer_1_id INT(10) NOT NULL,
    scorer_2_name TEXT NOT NULL,
    scorer_2_id INT(10) NOT NULL,
    reg_date TIMESTAMP
) $charset_collate;";

require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );

add_option( 'ncccricket_db_version', $ncccricket_db_version );

function ncccricket_install_data() {

global $wpdb;

$table_name = $wpdb->prefix . 'cricket';

$url = 'http://www.JSONURL.com';
$json = file_get_contents($url);
$content = json_decode($json, true);

 foreach ($content->matches as $match) {
        $wpdb->insert( $table_name, $match);

function ncccricket_uninstall() {
global $wpdb;

$table_name = $wpdb->prefix . 'cricket';
    $sql = "DROP TABLE IF EXISTS $table_name";

register_activation_hook( __FILE__, 'ncccricket_install' );
register_activation_hook( __FILE__, 'ncccricket_install_data' );
register_uninstall_hook( __FILE__, 'ncccricket_uninstall' );

Now I know I'm probably mixing Wordpress built in functions and raw PHP but although I'm ok with PHP, I've never really had to learn the wordpress quirks till now... Anyone who can show me what I should be doing to get it to work will live forevermore in a huge karmic love bubble....

  • If your table and the JSON data (i.e. the match array/item) contain the exact same fields, there's a simple way to insert the data using the $wpdb object: $wpdb->insert( $table_name, $match ); - so those numerous variables are unnecessary.. Check the Codex for more info. – Sally CJ Jan 5 at 1:06
  • Thanks Sally... See above, I've amended the PHP now to go with your suggestion and although the table columns are created correctly now, no data is inputed from the ncccricket_install_data function?? I also get "The plugin generated 155 characters of unexpected output during activation." as a warning message (which I assume means something is returned but not correctly)?? – neo2810 Jan 6 at 18:24
  • How/when are you calling these functions? They'll need to be "routed through Wordpress" by using a filter or action in order to have access to the $wpdb global variable. I suspect that's your problem. – Tim Hallman Jan 6 at 21:25
  • I've edited in my whole plugin file so it's clearer. I've got 2 actifvation hooks which should build the table and then import the data. The first is working, the 2nd isn't. – neo2810 Jan 6 at 23:29
  • See debugging in WordPress and check the error log for any relevant details about that "unexpected output". – Sally CJ Jan 9 at 1:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.