Populating a select box using an API

Posted by admin at January 4, 2017

In this tutorial, we are going to populate the options of a select box with the values retrieved from an API service. This is just a variant of a previous tutorial Populating a select box with values from a MySQl Table. The API service will be implemented with Slim. It can also be done with other API frameworks like Epiphany, Frapi, Tonic and Recess.

The SQL: The SQL for this tutorial can be downloaded here

Setting up Slim:

You can download and install the Slim framework or download zip and extract to a folder of your choice. Then we edit the index.php file accordingly.



// import the required CRUD functions
require('../inc/crud.php');

// ...

// define the url command and the function to run: get_service();
$app->get('/service/:serviceid', 'get_service');

// ...

// the function get_service();
function get_service($serviceid)
{
    
	// return the SELECT sql required for the service
	// get_read_sql() function contained in crud.php 
	$sql = get_read_sql($serviceid);
    
	// connect to db and process the sql
	try {
		// connect to database
		$db = getConnection();

		// run the query
		$stmt = $db->query($sql);
		
		// get the values
		$posts = $stmt->fetchAll(PDO::FETCH_OBJ);

		// clear out the db resource
		$db = null;
	
		// clean up collected data
		$posts = utf8($posts);
		
        // complete the json
		$json = '{"rows": ' . json_encode($posts) . '}';

	}
	catch(PDOException $e)
	{
        // the error message
		$json = '{"error":{"text":'. $e->getMessage() .'}}';
 	}

	// pad the json (jsonp)
	jsonp ($json);
    
}

Test this run the following link in the browser.


http://path/to/api/service/airport

You should have result similar to the one in the screenshot below:

inc/crud.php


function get_read_sql($tbl)
{
    switch($tbl)
    {
        case 'airport':
        case 'airports':
        case 'tbl_airport':
            $sql = 'SELECT'
                    .' *'
                . ' FROM'
                    .' `tbl_airports`'
                . ' WHERE 1'
                    . ' AND NOT ISNULL(`tbl_airports`.`airport_id`)'
                    . ' AND NOT ISNULL(`tbl_airports`.`airport_code`)'
                    . ' AND NOT ISNULL(`tbl_airports`.`airport_desc`)'
                . ' ORDER BY'
                    .' `tbl_airports`.`airport_code` ASC'
            ;
            break;
        case 'tbl_states':
        default:
            $sql = 'SELECT * FROM `'.tbl_states.'` WHERE 1';
            break;
            
    }
    return $sql;
}

The index file


// import CRUD support
require('inc/support.php');
...

<body>

...

<strong>Populate a select box with Slim API</strong><br>

<?php
    $get_act = mysql_real_escape_string($_GET['a']);
    $get_act = (strlen($get_act) > 0) ? $get_act : 'r';
?>
    
<?php if ($get_act == 'c') { ?>
    
<?php //*********************************************CREATE ?>

    <?php include ('inc/c.php') ;?>
    
<?php //*********************************************CREATE ?>
    
<?php } else if ($get_act == 'r'){ ?>
    
<?php //*********************************************READ ?>
    
    <?php include ('inc/r.php') ;?>
    
<?php //*********************************************READ ?>

<?php } else if ($get_act == 'u') { ?>
    
<?php //*********************************************UPDATE ?>

    <?php include ('inc/u.php') ;?>
    
<?php //*********************************************UPDATE ?>
    
<?php } else if ($get_act == 'd'){ ?>
    
<?php //*********************************************DELETE ?>

    <?php include ('inc/d.php') ;?>
    
<?php //*********************************************DELETE ?>


    
    © BlinkWIki
    
</body>

We focus on the sections below:


// import CRUD support
require('inc/support.php');


<?php //*********************************************READ ?>
    
    <?php include ('inc/r.php') ;?>
    
<?php //*********************************************READ ?>

inc/r.php

inc/r.php would look like this.


<?php //*********************************************READ ?>
    
    <hr>
    
    <strong>Read</strong><br><br>
    
    <select id="" name="">
         <?php echo api_sel_box('airport', 'airport_id', 'airport_code, airport_desc', 0); ?>
    </select>
    
<?php //*********************************************READ ?>

The function api_sel_box(); will be in the inc/support.php file. This is the core function that retrieves the values from the API url and create the options HTML. It functions in a similar way to the select_box(); function that does the same thing with MySQL values.


/**
*
* This function is a variant of the ordinary select_box(); select box
* populator function. Full details can be found here. 
* It simply populates a select box with values retrieved from an API link
*
* $tbl          is the table to generate values from
* $opt_value    is the name of the column to use for the option value property
* $opt_text     is the name of the column to use for the option text property
* $rid          is the record id representing the selected option
* $conn         is the database connector resource
*
*/
function api_sel_box($tbl, $opt_value, $opt_text, $rid=0)
{
    
    // the api url : the table determines the service to pull from the API
    $api_url = "http://localhost/csys/api_sel_box/api/service/".$tbl;
    
    // get the values from the api link
    {
        // allow for file_get_contes to run by
        // setting the PHP allwo_url_open value to 1
        ini_set("allow_url_fopen", 1);

        // get the contents of the url
        $json = file_get_contents($api_url);

        // decode the results
        $obj = json_decode($json);
    }
    
    
    // initialize the html
    $html = '';
    
    // determine the selected option
    $sel = ($rid == 0) ? 'selected' : '';
    
    // initiate the options html
    $html .= '<option value="0" '.$sel.'>--select--</option>';
    
    // in case the opt_text is in CSV format allowing us to display values from more than one column 
    $opt_txts = explode(',', str_replace(' ', '', $opt_text));
    
    // loop through the rows
    for ($i=0; $i<count($obj->rows); $i++)
    {
    
        // convert the object to an array
        // array allow for referencing of key names with variables
        $row = get_object_vars($obj->rows[$i]);
        
        // if the current options is the selected option then set the option's selected property
        $sel = ($row[$opt_value] == $rid) ? 'selected' : '';
        
        // append the option html
        $html .= ''
            // start the option tag
            .'<option value="'
                // append the airport id as the option value
                .$row[$opt_value]
            .'" title="'
                // append the airport desc as the option title
                .$row[$opt_text]
            .'"'
                // append the selected attribute
                .' '.$sel
            .'>'
        ;
        // display all the text values
        for ($j=0; $j<count($opt_txts); $j++)
        {
            if ($j > 0)
            {
                // the separator
                $html .= ' | ';                
            }
            $html .= ''
                // append the airport name as the option text
                .$row[$opt_txts[$j]]
            ;
        }
        $html .= ''
            // close the option tag
            . '</option>'
        ;
    }
    
    return $html;
    
}

The final screen would look like this

Putting it all together

You can download the complete application here

1 likes